# 多列统计信息 #### 概念 多列统计信息是把一张表中的多个列看作一个整体，计算并获取其在整张表中的数据分布信息，统计信息种类主要包括NDV（distinct值数目）、MCV（most common value）、空值比例（nullfrac）、空值高频值（NULL_MCV）等。 #### 作用 如果一条sql引用了多个列的约束条件，这些列之间相互独立，用单列统计信息计算时，会把这些约束条件拆分成单个子约束条件。但在实际应用中无法保证这些约束条件是相互独立的，因此可能导致估算的误差较大，于是衍生出多列统计信息来计算各个列之间的依赖度以及它们的数据分布。例如，在一个体育课的学生信息表中，课程为篮球的学生性别大多是男生，课程为舞蹈的学生性别大多是女生，当查询课程是篮球，性别是男生时，使用单列统计信息容易导致误差较大，这时就需要使用多列统计信息。 示例： ``` gaussdb=# CREATE TABLE class (studentid int, course_name text, gender text); CREATE TABLE gaussdb=# INSERT INTO class VALUES(generate_series(1, 500),'篮球','男'); INSERT 0 500 gaussdb=# INSERT INTO class VALUES(generate_series(501, 1000),'舞蹈','女'); INSERT 0 500 gaussdb=# ANALYZE class; ANALYZE gaussdb=# SET enable_fast_query_shipping = off; SET gaussdb=# EXPLAIN SELECT * FROM class WHERE course_name = '篮球'; QUERY PLAN ---------------------------------------------------------------- Streaming (type: GATHER) (cost=4.00..33.69 rows=500 width=15) Node/s: All datanodes -> Seq Scan on class (cost=0.00..10.25 rows=500 width=15) Filter: (course_name = '篮球'::text) (4 rows) gaussdb=# EXPLAIN SELECT * FROM class WHERE course_name = '篮球' AND gender = '男'; QUERY PLAN -------------------------------------------------------------------------- Streaming (type: GATHER) (cost=4.00..23.22 rows=250 width=15) Node/s: All datanodes -> Seq Scan on class (cost=0.00..11.50 rows=250 width=15) Filter: ((course_name = '篮球'::text) AND (gender = '男'::text)) (4 rows) --查看单列统计信息 gaussdb=# \x Expanded display is on. gaussdb=# SELECT * FROM pg_stats WHERE tablename = 'class'; -[ RECORD 1 ]----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ schemaname | public tablename | class attname | studentid inherited | f null_frac | 0 avg_width | 4 n_distinct | -1 n_dndistinct | -1 most_common_vals | most_common_freqs | histogram_bounds | {1,10,20,30,40,50,60,70,80,90,100,110,120,130,140,150,160,170,180,190,200,210,220,230,240,250,260,270,280,290,300,310,320,330,340,350,360,370,380,390,400,410,420,430,440,450,460,470,480,490,500,510,520,530,540,550,560,570,580,590,600,610,620,630,640,650,660,670,680,690,700,710,720,730,740,750,760,770,780,790,800,810,820,830,840,850,860,870,880,890,900,910,920,930,940,950,960,970,980,990,1000} correlation | .91529 most_common_elems | most_common_elem_freqs | elem_count_histogram | partitionname | subpartitionname | -[ RECORD 2 ]----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ schemaname | public tablename | class attname | course_name inherited | f null_frac | 0 avg_width | 7 n_distinct | 2 n_dndistinct | 2 most_common_vals | {篮球,舞蹈} most_common_freqs | {.5,.5} histogram_bounds | correlation | .989651 most_common_elems | most_common_elem_freqs | elem_count_histogram | partitionname | subpartitionname | -[ RECORD 3 ]----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ schemaname | public tablename | class attname | gender inherited | f null_frac | 0 avg_width | 4 n_distinct | 2 n_dndistinct | 2 most_common_vals | {女,男} most_common_freqs | {.5,.5} histogram_bounds | correlation | -.415406 most_common_elems | most_common_elem_freqs | elem_count_histogram | partitionname | subpartitionname | --查看多列统计信息 gaussdb=# \x Expanded display is off. gaussdb=# SELECT * FROM pg_ext_stats WHERE tablename = 'class'; schemaname | tablename | attname | inherited | null_frac | avg_width | n_distinct | n_dndistinct | most_common_vals | most_common_freqs | most_common_vals_null | most_common_freqs_null | histogr am_bounds | partitionname | subpartitionname ------------+-----------+---------+-----------+-----------+-----------+------------+--------------+------------------+-------------------+-----------------------+------------------------+-------- ----------+---------------+------------------ (0 rows) --收集多列统计信息 gaussdb=# ANALYZE class ((course_name,gender)); ANALYZE gaussdb=# EXPLAIN SELECT * FROM class WHERE course_name = '篮球' AND gender = '男'; QUERY PLAN -------------------------------------------------------------------------- Streaming (type: GATHER) (cost=4.00..34.94 rows=500 width=15) Node/s: All datanodes -> Seq Scan on class (cost=0.00..11.50 rows=500 width=15) Filter: ((course_name = '篮球'::text) AND (gender = '男'::text)) (4 rows) --查看多列统计信息 gaussdb=# \x Expanded display is on. gaussdb=# SELECT * FROM pg_ext_stats WHERE tablename = 'class'; -[ RECORD 1 ]----------+-------------------------- schemaname | public tablename | class attname | 2 3 inherited | f null_frac | 0 avg_width | 11 n_distinct | 2 n_dndistinct | 2 most_common_vals | {"{篮球,舞蹈}","{男,女}"} most_common_freqs | {.5,.5} most_common_vals_null | most_common_freqs_null | histogram_bounds | partitionname | subpartitionname | gaussdb=# DROP TABLE class; DROP TABLE ``` 上述例子中，从插入的数据可以看出，知道课程名字course_name就可以确定性别gender，如果没有多列统计信息，单独查询课程名字等于篮球时（course_name = '篮球'），预估行数为1000（总行数） \* 0.5（课程为篮球的MCV比例） = 500，符合真实数据。但是同时查询课程名字course_name和性别gender时（course_name = '篮球' and gender = '男'），预估行数为1000（总行数） \* 0.5（课程为篮球的MCV比例） \* 0.5（性别为男的MCV比例） = 250，实际行数为500，不符合真实数据。 当收集完多列统计信息后，可以看到同时查询课程名字course_name和性别gender时（course_name = '篮球' and gender = '男'），预估行数为1000（总行数）\* 0.5（课程为篮球和性别为男的MCV比例） = 500，实际行数为500，符合真实数据。 #### 计算方式 通过下述示例数据，说明统计信息的具体计算过程。 示例： ``` gaussdb=# CREATE TABLE t1(a INT, b INT, c INT, d INT); CREATE TABLE gaussdb=# INSERT INTO t1 VALUES (1 , 1 , 1 , 1); INSERT 0 1 gaussdb=# INSERT INTO t1 VALUES (1 , 1 , 1 , 1); INSERT 0 1 gaussdb=# INSERT INTO t1 VALUES (1 , 1 , 1 , 1); INSERT 0 1 gaussdb=# INSERT INTO t1 VALUES (1 , 1 , 1 , 1); INSERT 0 1 gaussdb=# INSERT INTO t1 VALUES (1 , 2 , 3 , 4); INSERT 0 1 gaussdb=# INSERT INTO t1 VALUES (1 , 2 , 3 , 4); INSERT 0 1 gaussdb=# INSERT INTO t1 VALUES (1 , 2 , 3 , 4); INSERT 0 1 gaussdb=# INSERT INTO t1 VALUES (1 , 3 , 3 , 4); INSERT 0 1 gaussdb=# INSERT INTO t1 VALUES (1 , 3 , 3 , 4); INSERT 0 1 gaussdb=# INSERT INTO t1 VALUES (1 , 3 , 3 , 4); INSERT 0 1 gaussdb=# INSERT INTO t1 VALUES (NULL, 1 , 1 , 1); INSERT 0 1 gaussdb=# INSERT INTO t1 VALUES (NULL, 1 , 1 , 1); INSERT 0 1 gaussdb=# INSERT INTO t1 VALUES (NULL, 1 , 1 , 1); INSERT 0 1 gaussdb=# INSERT INTO t1 VALUES (NULL, 1 , 1 , 1); INSERT 0 1 gaussdb=# INSERT INTO t1 VALUES (NULL, NULL, 3 , 4); INSERT 0 1 gaussdb=# INSERT INTO t1 VALUES (NULL, NULL, 3 , 4); INSERT 0 1 gaussdb=# INSERT INTO t1 VALUES (NULL, NULL, 3 , 4); INSERT 0 1 gaussdb=# SELECT * FROM t1; a | b | c | d ---+---+---+--- 1 | 1 | 1 | 1 1 | 1 | 1 | 1 1 | 1 | 1 | 1 1 | 1 | 1 | 1 1 | 2 | 3 | 4 1 | 2 | 3 | 4 1 | 2 | 3 | 4 1 | 3 | 3 | 4 1 | 3 | 3 | 4 1 | 3 | 3 | 4 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | | 3 | 4 | | 3 | 4 | | 3 | 4 (17 rows) gaussdb=# ANALYZE t1((a,b)); ANALYZE gaussdb=# \x Expanded display is on. gaussdb=# SELECT * FROM pg_ext_stats WHERE tablename = 't1'; -[ RECORD 1 ]----------+-------------------------- schemaname | public tablename | t1 attname | 1 2 inherited | f null_frac | .176471 avg_width | 8 n_distinct | -.235294 n_dndistinct | -.333333 most_common_vals | {"{1,1,1}","{1,2,3}"} most_common_freqs | {.235294,.176471,.176471} most_common_vals_null | {"{NULL}","{1}"} most_common_freqs_null | {.235294} histogram_bounds | partitionname | subpartitionname | gaussdb=# DROP TABLE t1; DROP TABLE ``` - null_frac ：NULL值比例。由于存在三行\[null,null\], 所以3/17（总行数） = 0.176471。 - n_distinct：distinct值数目。去掉空值后，存在四种取值\[1,1\],\[1,2\],\[1,3\],\[null,1\], 4/17(总行数) = 0.235294。 - n_dndistinct：DN平均的distinct值数目。由于需要除以DN数，舍弃了一些精度，导致该值无法精确计算。 - most_common_vals：MCV（高频值），本列保存的多列常用数值均不为NULL。每个{}里同位置为一组，比如在该示例中，MCV依次为\[1,1\],\[1,2\],\[1,3\]。 - most_common_freqs：MCV值占总行数的比例。\[1,1\]有4行，4 / 17 = 0.23529，\[1,2\]有3行，3 / 17 = 0.176471，\[1,3\]有3行，3 / 17 = 0.176471。 - most_common_vals_null：包含NULL值的MCV(高频值）。本列保存的多列常用数值中至少有一个值为NULL但不全为NULL。包含NULL值且不全为NULL的值只有\[null,1\]。 - most_common_freqs_null：包含NULL值的MCV值占总行数的比例。包含NULL值且不全为NULL的值只有\[null,1\]，并且有4行，4 / 17 = 0.23529。 - histogram_bounds：直方图信息，多列统计信息无直方图信息。 #### 收集方法 在GaussDB中，默认情况下，ANALYZE只收集表中单列的统计信息，不会收集多个相关列的统计信息。可以通过pg_stats视图查看单列统计信息，通过pg_ext_stats视图查看多列统计信息。收集多列统计信息的方法有以下三种： - 手动指定列名收集。 例：ANALYZE t1((a,b)); ``` gaussdb=# CREATE TABLE t1 (a int, b int); CREATE TABLE gaussdb=# INSERT INTO t1 VALUES(generate_series(1, 10), generate_series(1, 2)); INSERT 0 10 gaussdb=# INSERT INTO t1 VALUES(generate_series(1, 10), generate_series(1, 2)); INSERT 0 10 gaussdb=# ANALYZE t1; ANALYZE --查看单列统计信息 gaussdb=# SELECT * FROM pg_stats WHERE tablename = 't1'; schemaname | tablename | attname | inherited | null_frac | avg_width | n_distinct | n_dndistinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | mos t_common_elems | most_common_elem_freqs | elem_count_histogram | partitionname | subpartitionname ------------+-----------+---------+-----------+-----------+-----------+------------+--------------+------------------------+---------------------------------+------------------+-------------+---- ---------------+------------------------+----------------------+---------------+------------------ public | t1 | a | f | 0 | 4 | -.5 | -.5 | {1,2,3,4,5,6,7,8,9,10} | {.1,.1,.1,.1,.1,.1,.1,.1,.1,.1} | | .409023 | | | | | public | t1 | b | f | 0 | 4 | 2 | -.166667 | {1,2} | {.5,.5} | | .541353 | | | | | (2 rows) --查看多列统计信息 gaussdb=# SELECT * FROM pg_ext_stats WHERE tablename = 't1'; schemaname | tablename | attname | inherited | null_frac | avg_width | n_distinct | n_dndistinct | most_common_vals | most_common_freqs | most_common_vals_null | most_common_freqs_null | histogr am_bounds | partitionname | subpartitionname ------------+-----------+---------+-----------+-----------+-----------+------------+--------------+------------------+-------------------+-----------------------+------------------------+-------- ----------+---------------+------------------ (0 rows) --收集多列统计信息 gaussdb=# ANALYZE t1((a,b)); ANALYZE --查看多列统计信息 gaussdb=# SELECT * FROM pg_ext_stats WHERE tablename = 't1'; schemaname | tablename | attname | inherited | null_frac | avg_width | n_distinct | n_dndistinct | most_common_vals | most_common_freqs | most_com mon_vals_null | most_common_freqs_null | histogram_bounds | partitionname | subpartitionname ------------+-----------+---------+-----------+-----------+-----------+------------+--------------+----------------------------------------------------+---------------------------------+--------- --------------+------------------------+------------------+---------------+------------------ public | t1 | 1 2 | f | 0 | 8 | -.5 | -.5 | {"{1,2,3,4,5,6,7,8,9,10}","{1,2,1,2,1,2,1,2,1,2}"} | {.1,.1,.1,.1,.1,.1,.1,.1,.1,.1} | | | | | (1 row) gaussdb=# DROP TABLE t1; DROP TABLE ``` - 通过ALTER TABLE语句新增多列。 例：ALTER TABLE t1 ADD statistics ((a,b)); 随后ANALYZE t1; 即可收集多列的统计信息。ALTER TABLE只是为这个多列占了一个槽位，并不真正收集统计信息。删除多列统计信息：ALTER TABLE t1 DELETE statistics((a,b)); ``` gaussdb=# CREATE TABLE t1 (a int, b int); CREATE TABLE gaussdb=# INSERT INTO t1 VALUES(generate_series(1, 10), generate_series(1, 2)); INSERT 0 10 gaussdb=# INSERT INTO t1 VALUES(generate_series(1, 10), generate_series(1, 2)); INSERT 0 10 gaussdb=# ANALYZE t1; ANALYZE --查看单列统计信息 gaussdb=# SELECT * FROM pg_stats WHERE tablename = 't1'; schemaname | tablename | attname | inherited | null_frac | avg_width | n_distinct | n_dndistinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | mos t_common_elems | most_common_elem_freqs | elem_count_histogram | partitionname | subpartitionname ------------+-----------+---------+-----------+-----------+-----------+------------+--------------+------------------------+---------------------------------+------------------+-------------+---- ---------------+------------------------+----------------------+---------------+------------------ public | t1 | a | f | 0 | 4 | -.5 | -.5 | {1,2,3,4,5,6,7,8,9,10} | {.1,.1,.1,.1,.1,.1,.1,.1,.1,.1} | | .409023 | | | | | public | t1 | b | f | 0 | 4 | 2 | -.166667 | {1,2} | {.5,.5} | | .541353 | | | | | (2 rows) --查看多列统计信息 gaussdb=# SELECT * FROM pg_ext_stats WHERE tablename = 't1'; schemaname | tablename | attname | inherited | null_frac | avg_width | n_distinct | n_dndistinct | most_common_vals | most_common_freqs | most_common_vals_null | most_common_freqs_null | histogr am_bounds | partitionname | subpartitionname ------------+-----------+---------+-----------+-----------+-----------+------------+--------------+------------------+-------------------+-----------------------+------------------------+-------- ----------+---------------+------------------ (0 rows) --设置多列统计信息 gaussdb=# ALTER TABLE t1 ADD statistics ((a,b)); ALTER TABLE gaussdb=# ANALYZE t1; ANALYZE --查看单列统计信息 gaussdb=# SELECT * FROM pg_stats WHERE tablename = 't1'; schemaname | tablename | attname | inherited | null_frac | avg_width | n_distinct | n_dndistinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | mos t_common_elems | most_common_elem_freqs | elem_count_histogram | partitionname | subpartitionname ------------+-----------+---------+-----------+-----------+-----------+------------+--------------+------------------------+---------------------------------+------------------+-------------+---- ---------------+------------------------+----------------------+---------------+------------------ public | t1 | a | f | 0 | 4 | -.5 | -.5 | {1,2,3,4,5,6,7,8,9,10} | {.1,.1,.1,.1,.1,.1,.1,.1,.1,.1} | | .409023 | | | | | public | t1 | b | f | 0 | 4 | 2 | -.166667 | {1,2} | {.5,.5} | | .541353 | | | | | (2 rows) --查看多列统计信息 gaussdb=# SELECT * FROM pg_ext_stats WHERE tablename = 't1'; schemaname | tablename | attname | inherited | null_frac | avg_width | n_distinct | n_dndistinct | most_common_vals | most_common_freqs | most_com mon_vals_null | most_common_freqs_null | histogram_bounds | partitionname | subpartitionname ------------+-----------+---------+-----------+-----------+-----------+------------+--------------+----------------------------------------------------+---------------------------------+--------- --------------+------------------------+------------------+---------------+------------------ public | t1 | 1 2 | f | 0 | 8 | -.5 | -.5 | {"{1,2,3,4,5,6,7,8,9,10}","{1,2,1,2,1,2,1,2,1,2}"} | {.1,.1,.1,.1,.1,.1,.1,.1,.1,.1} | | | | | (1 row) --删除多列统计信息 gaussdb=# ALTER TABLE t1 DELETE statistics((a,b)); ALTER TABLE --查看多列统计信息 gaussdb=# SELECT * FROM pg_ext_stats WHERE tablename = 't1'; schemaname | tablename | attname | inherited | null_frac | avg_width | n_distinct | n_dndistinct | most_common_vals | most_common_freqs | most_common_vals_null | most_common_freqs_null | histogr am_bounds | partitionname | subpartitionname ------------+-----------+---------+-----------+-----------+-----------+------------+--------------+------------------+-------------------+-----------------------+------------------------+-------- ----------+---------------+------------------ (0 rows) gaussdb=# DROP TABLE t1; DROP TABLE ``` - 根据索引前缀自动创建多列统计信息。 1. 设置参数auto_statistic_ext_columns，取值范围\[1,4\]。 2. 为表创建索引，例如为t1表创建一个索引"CREATE INDEX idx1 ON t1(a,b,c,d);"。 3. 收集统计信息。 ``` gaussdb=# CREATE TABLE t1 (a int, b int); CREATE TABLE gaussdb=# INSERT INTO t1 VALUES(generate_series(1, 10), generate_series(1, 2)); INSERT 0 10 gaussdb=# INSERT INTO t1 VALUES(generate_series(1, 10), generate_series(1, 2)); INSERT 0 10 gaussdb=# ANALYZE t1; ANALYZE --查看单列统计信息 gaussdb=# SELECT * FROM pg_stats WHERE tablename = 't1'; schemaname | tablename | attname | inherited | null_frac | avg_width | n_distinct | n_dndistinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | mos t_common_elems | most_common_elem_freqs | elem_count_histogram | partitionname | subpartitionname ------------+-----------+---------+-----------+-----------+-----------+------------+--------------+------------------------+---------------------------------+------------------+-------------+---- ---------------+------------------------+----------------------+---------------+------------------ public | t1 | a | f | 0 | 4 | -.5 | -.5 | {1,2,3,4,5,6,7,8,9,10} | {.1,.1,.1,.1,.1,.1,.1,.1,.1,.1} | | .409023 | | | | | public | t1 | b | f | 0 | 4 | 2 | -.166667 | {1,2} | {.5,.5} | | .541353 | | | | | (2 rows) --查看多列统计信息 gaussdb=# SELECT * FROM pg_ext_stats WHERE tablename = 't1'; schemaname | tablename | attname | inherited | null_frac | avg_width | n_distinct | n_dndistinct | most_common_vals | most_common_freqs | most_common_vals_null | most_common_freqs_null | histogr am_bounds | partitionname | subpartitionname ------------+-----------+---------+-----------+-----------+-----------+------------+--------------+------------------+-------------------+-----------------------+------------------------+-------- ----------+---------------+------------------ (0 rows) --查看参数 gaussdb=# SHOW auto_statistic_ext_columns; auto_statistic_ext_columns ---------------------------- 4 (1 row) --设置参数 gaussdb=# SET auto_statistic_ext_columns = 2; SET --创建前缀索引 gaussdb=# CREATE INDEX idx1 ON t1(a,b); CREATE INDEX gaussdb=# ANALYZE t1; ANALYZE --查看单列统计信息 gaussdb=# SELECT * FROM pg_stats WHERE tablename = 't1'; schemaname | tablename | attname | inherited | null_frac | avg_width | n_distinct | n_dndistinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | mos t_common_elems | most_common_elem_freqs | elem_count_histogram | partitionname | subpartitionname ------------+-----------+---------+-----------+-----------+-----------+------------+--------------+------------------------+---------------------------------+------------------+-------------+---- ---------------+------------------------+----------------------+---------------+------------------ public | t1 | a | f | 0 | 4 | -.5 | -.5 | {1,2,3,4,5,6,7,8,9,10} | {.1,.1,.1,.1,.1,.1,.1,.1,.1,.1} | | .168421 | | | | | public | t1 | b | f | 0 | 4 | 2 | -.166667 | {1,2} | {.5,.5} | | .541353 | | | | | (2 rows) --查看多列统计信息 gaussdb=# SELECT * FROM pg_ext_stats WHERE tablename = 't1'; schemaname | tablename | attname | inherited | null_frac | avg_width | n_distinct | n_dndistinct | most_common_vals | most_common_freqs | most_com mon_vals_null | most_common_freqs_null | histogram_bounds | partitionname | subpartitionname ------------+-----------+---------+-----------+-----------+-----------+------------+--------------+----------------------------------------------------+---------------------------------+--------- --------------+------------------------+------------------+---------------+------------------ public | t1 | 1 2 | f | 0 | 8 | -.5 | -.5 | {"{1,2,3,4,5,6,7,8,9,10}","{1,2,1,2,1,2,1,2,1,2}"} | {.1,.1,.1,.1,.1,.1,.1,.1,.1,.1} | | | | | (1 row) gaussdb=# DROP TABLE t1; DROP TABLE ```