更新时间：2026-07-28 GMT+08:00
操作指导
前提条件
- 软件版本：内核版本≥GaussDB Kernel 503.1.0.B038。
- 数据库运行正常。
- GUC参数enable_ai_stats设置为on。
- multi_stats_type设置为'BAYESNET'或者'ALL'。
- GUC参数enable_fast_query_shipping设置为off
操作步骤
- 设置采样方式为按照采样率采样，即设置GUC参数default_statistics_target为[-100, -1]之间的整数，表示采样百分比。
- 使用ANALYZE table_name((column1_name,column2_name[, ...])) 进行数据统计和模型创建。
- 输入查询，如果查询涉及到的等值查询列上有统计模型创建，会自动使用统计模型进行选择率估计。
- 不再需要智能统计模型时，使用ALTER TABLE table_name DELETE STATISTICS ((column1_name,column2_name[, ...]))进行统计信息以及模型删除。
其他使用的方法详见《参考》中“SQL参考 > SQL语法 > A > ALTER TABLE”和“SQL参考 > SQL语法 > A > ANALYZE | ANALYSE”章节。
示例
受环境、版本及部署形态差异影响，示例中的执行计划可能与实际不同。本示例仅作为参考，具体优化效果请以实际执行计划为准。
-- 建表并插入数据
gaussdb=# DROP TABLE IF EXISTS t1;
DROP TABLE
gaussdb=# CREATE TABLE t1(a INT , b INT, c INT);
NOTICE: The 'DISTRIBUTE BY' clause is not specified. Using 'a' as the distribution column by default.
HINT: Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
CREATE TABLE
gaussdb=# INSERT INTO t1 VALUES(GENERATE_SERIES(1, 1000),1,1);
INSERT 0 1000
-- 检查GUC参数
gaussdb=# SHOW enable_ai_stats;
enable_ai_stats
-----------------
on
(1 row)
gaussdb=# SHOW multi_stats_type;
multi_stats_type
------------------
bayesnet
(1 row)
gaussdb=# SHOW enable_fast_query_shipping;
enable_fast_query_shipping
---------------------------
off
(1 row)
gaussdb=# SHOW explain_perf_mode;
explain_perf_mode
-------------------
normal
(1 row)
-- 创建统计模型
gaussdb=# ANALYZE t1((a,b));
ANALYZE
-- 查询统计模型
gaussdb=# \x
Expanded display is on.
gaussdb=# SELECT gs_ai_stats_explain('t1', ARRAY['a', 'b']);
-[ RECORD 1 ]-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
gs_ai_stats_explain | Name: BAYESNET_MODEL_STATS_xxx_xxxxx
| Algorithm: bayes_net_internal
| Query: DIRECT ML API
| Return type: Float64
| Pre-processing time: 0.000000
| Execution time: 0.003121
| Processed tuples: 1000
| Discarded tuples: 0
| edges_of_network: 0,1
| num_edges: 1
| num_nodes: 2
| num_bins: 500
| num_mcvs: 500
| num_sample_rows: 1000
| num_total_rows: 1000
| Number of nodes: 2
| Bucket Type: Range Bin
| Column #0: {1,3,5,7,9,11,13,15,17,19,21,23,25,27,29,31,33,35,37,39,41,43,45,47,49,51,53,55,57,59,61,63,65,67,69,71,73,75,77,79,81,83,85,87,89,91,93,95,97,99,101,103,105,107,109,111,113,115,117,119,121,123,125,127,129,131,133,135,137,139,141,143,145,147,149,151,153,155,157,159,161,163,165,167,169,171,173,175,177,179,181,183,185,187,189,191,193,195,197,199,201,203,205,207,209,211,213,215,217,219,221,223,225,227,229,231,233,235,237,239,241,243,245,247,249,251,253,255,257,259,261,263,265,267,269,271,273,275,277,279,281,283,285,287,289,291,293,295,297,299,301,303,305,307,309,311,313,315,317,319,321,323,325,327,329,331,333,335,337,339,341,343,345,347,349,351,353,355,357,359,361,363,365,367,369,371,373,375,377,379,381,383,385,387,389,391,393,395,397,399,401,403,405,407,409,411,413,415,417,419,421,423,425,427,429,431,433,435,437,439,441,443,445,447,449,451,453,455,457,459,461,463,465,467,469,471,473,475,477,479,481,483,485,487,489,491,493,495,497,499,501,503,505,507,509,511,513,515,517,519,521,523,525,527,529,531,533,535,537,539,541,543,545,547,549,551,553,555,557,559,561,563,565,567,569,571,573,575,577,579,581,583,585,587,589,591,593,595,597,599,601,603,605,607,609,611,613,615,617,619,621,623,625,627,629,631,633,635,637,639,641,643,645,647,649,651,653,655,657,659,661,663,665,667,669,671,673,675,677,679,681,683,685,687,689,691,693,695,697,699,701,703,705,707,709,711,713,715,717,719,721,723,725,727,729,731,733,735,737,739,741,743,745,747,749,751,753,755,757,759,761,763,765,767,769,771,773,775,777,779,781,783,785,787,789,791,793,795,797,799,801,803,805,807,809,811,813,815,817,819,821,823,825,827,829,831,833,835,837,839,841,843,845,847,849,851,853,855,857,859,861,863,865,867,869,871,873,875,877,879,881,883,885,887,889,891,893,895,897,899,901,903,905,907,909,911,913,915,917,919,921,923,925,927,929,931,933,935,937,939,941,943,945,947,949,951,953,955,957,959,961,963,965,967,969,971,973,975,977,979,981,983,985,987,989,991,993,995,997,999,1000}
| Column Probabilities: {.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,.001,0}
| Bucket Type: All Values
| Column #1: {1}
| Column Probabilities: {1}
|
gaussdb=# \x
Expanded display is off.
-- 执行查询成功
gaussdb=# EXPLAIN ANALYZE SELECT * FROM t1 WHERE a=1 AND b=1;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
Streaming (type: GATHER) (cost=0.06..12.54 rows=1 width=12) (actual time=4.944..5.275 rows=1 loops=1)
Node/s: datanode1
-> Seq Scan on t1 (cost=0.00..12.45 rows=1 width=12) (actual time=[0.226,0.576]..[0.226,0.576], rows=1)
Filter: ((a = 1) AND (b = 1))
Rows Removed by Filter: 491
Total runtime: 5.785 ms
(6 rows)
-- 若无需智能统计模型可删除
gaussdb=# ALTER TABLE t1 DELETE STATISTICS((a,b));
ALTER TABLE
-- 删除表
gaussdb=# DROP TABLE t1;
DROP TABLE 父主题： 智能基数估计
