PG中的MCV统计信息
MCV
在pg12中可以建立mcv统计信息
MCV(Multi-Column-Values）用于追踪出现频率最高的值的组合。
相比函数依赖，MCV的两个优势：
第一：mcv存储了实际值，因此，可以更为精确的判断where条件的组合的预计返回记录数。
第二：mcv可以处理非等值的where条件。
建立mcv和不建立mcv情况下的等值查询
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 | [pg124@lhrdb ~]$ psql -d postgres psql (12.4) Type 'help' for help. postgres=# CREATE TABLE t_mcv (a INT, b INT); CREATE TABLE postgres=# INSERT INTO t_mcv SELECT i % 100, i % 100 FROM generate_series(1, 10000) s(i); INSERT 0 10000 postgres=# CREATE STATISTICS stts2 (mcv) ON a, b FROM t_mcv; CREATE STATISTICS postgres=# ANALYZE t_mcv; ANALYZE postgres=# EXPLAIN (ANALYZE, TIMING OFF) SELECT * FROM t_mcv WHERE a = 1 AND b = 1; QUERY PLAN ----------------------------------------------------------------------------------- Seq Scan on t_mcv (cost=0.00..195.00 rows=100 width=8) (actual rows=100 loops=1) Filter: ((a = 1) AND (b = 1)) Rows Removed by Filter: 9900 Planning Time: 0.272 ms Execution Time: 0.900 ms (5 rows) postgres=# SELECT m.* FROM pg_statistic_ext join pg_statistic_ext_data on (oid = stxoid), postgres-# pg_mcv_list_items(stxdmcv) m WHERE stxname = 'stts2'; index | values | nulls | frequency | base_frequency -------+---------+-------+-----------+---------------- 0 | {0,0} | {f,f} | 0.01 | 0.0001 1 | {1,1} | {f,f} | 0.01 | 0.0001 2 | {2,2} | {f,f} | 0.01 | 0.0001 ...................................... 97 | {97,97} | {f,f} | 0.01 | 0.0001 98 | {98,98} | {f,f} | 0.01 | 0.0001 99 | {99,99} | {f,f} | 0.01 | 0.0001 (100 rows) |
如下是在testdb中，不建立mcv的情况:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | testdb=# EXPLAIN (ANALYZE, TIMING OFF) SELECT * FROM t_mcv WHERE a = 1 AND b = 1; QUERY PLAN --------------------------------------------------------------------------------- Seq Scan on t_mcv (cost=0.00..195.00 rows=1 width=8) (actual rows=100 loops=1) Filter: ((a = 1) AND (b = 1)) Rows Removed by Filter: 9900 Planning Time: 0.076 ms Execution Time: 0.955 ms (5 rows) testdb=# SELECT count(*) FROM t_mcv WHERE a = 1 AND b = 1; count ------- 100 (1 row) testdb=#--->>，见下： testdb=# |
可以看到在不建立mcv时，优化器预估rows=1，在建立mcv时，优化器预估rows=100，其实，符合该条件的记录是100条。
从上面的分析可以看出，建立mcv有助于优化器更加精确的评估SQL的成本。
建立mcv和不建立mcv情况下的不等值查询
首先来看建立mcv情况下的不等值查询：
1 2 3 4 5 6 7 8 9 10 11 12 13 | postgres=# EXPLAIN (ANALYZE) SELECT * FROM t_mcv WHERE a <= 49 AND b > 49; QUERY PLAN ------------------------------------------------------------------------------------------------- Seq Scan on t_mcv (cost=0.00..195.00 rows=1 width=8) (actual time=0.751..0.752 rows=0 loops=1) Filter: ((a <= 49) AND (b > 49)) Rows Removed by Filter: 10000 Planning Time: 0.097 ms Execution Time: 0.768 ms (5 rows) postgres=# |
再来看不建立mcv情况下的不等值查询：
1 2 3 4 5 6 7 8 9 10 11 12 | testdb=# EXPLAIN (ANALYZE) SELECT * FROM t_mcv WHERE a <= 49 AND b > 49; QUERY PLAN ---------------------------------------------------------------------------------------------------- Seq Scan on t_mcv (cost=0.00..195.00 rows=2500 width=8) (actual time=0.899..0.900 rows=0 loops=1) Filter: ((a <= 49) AND (b > 49)) Rows Removed by Filter: 10000 Planning Time: 0.065 ms Execution Time: 0.915 ms (5 rows) testdb=# |
可以看出来，从预计rows和Execution Time角度看，都是建立mcv要比不建立mcv要好。
CREATE、DROP和ALTER STATISTICS
CREATE STATISTICS
CREATE STATISTICS — 定义扩展统计
大纲
1 2 3 4 | CREATE STATISTICS [ IF NOT EXISTS ] statistics_name [ ( statistics_kind [, ... ] ) ] ON column_name, column_name [, ...] FROM table_name |
描述
CREATE STATISTICS将创建一个新的扩展统计对象， 追踪指定表、外部表或物化视图的数据。该统计对象将在当前数据库中创建， 被发出该命令的用户所有。
如果给定了模式名（比如，CREATE STATISTICS myschema.mystat ...）， 那么在给定的模式中创建统计对象。否则在当前模式中创建。 统计对象的名称必须与相同模式中的任何其他统计对象不同。
参数
IF NOT EXISTS如果具有相同名称的统计对象已经存在，不会抛出一个错误，只会发出一个提示。 请注意，这里只考虑统计对象的名称，不考虑其定义细节。
statistics_name要创建的统计对象的名称（可以有模式限定）。
statistics_kind在此统计对象中计算的统计种类。目前支持的种类是启用n-distinct统计的
ndistinct，启用功能依赖性统计的dependencies，以及启用最常见的值列表的mcv。 如果省略该子句，则统计对象中将包含所有支持的统计类型。 有关更多信息，请参阅第 14.2.2 节和 第 70.2 节。column_name被计算的统计信息包含的表格列的名称。至少必须给出两个列名，列名的顺序可以忽略。
table_name包含计算统计信息的列的表的名称（可以是模式限定的）。
注意
你必须是表的所有者才能创建读取它的统计对象。不过，一旦创建， 统计对象的所有权与基础表无关。
示例
用两个功能相关的列创建表t1， 即第一列中的值的信息足以确定另一列中的值。然后， 在这些列上构建函数依赖关系统计信息：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | CREATE TABLE t1 ( a int, b int ); INSERT INTO t1 SELECT i/100, i/500 FROM generate_series(1,1000000) s(i); ANALYZE t1; -- 匹配行的数量将被大大低估： EXPLAIN ANALYZE SELECT * FROM t1 WHERE (a = 1) AND (b = 0); CREATE STATISTICS s1 (dependencies) ON a, b FROM t1; ANALYZE t1; -- 现在行计数估计会更准确： EXPLAIN ANALYZE SELECT * FROM t1 WHERE (a = 1) AND (b = 0); |
如果没有函数依赖性统计，规划器会认为两个WHERE条件是独立的， 并且会将它们的选择性乘以一起，以致得到太小的行数估计。 通过这样的统计，规划器认识到WHERE条件是多余的，并且不会低估行数。
创建表t2与两个完全相关的列(包含相同的数据)，并且在这些列上创建一个MCV列表:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | CREATE TABLE t2 ( a int, b int ); INSERT INTO t2 SELECT mod(i,100), mod(i,100) FROM generate_series(1,1000000) s(i); CREATE STATISTICS s2 (mcv) ON a, b FROM t2; ANALYZE t2; -- valid combination (found in MCV) EXPLAIN ANALYZE SELECT * FROM t2 WHERE (a = 1) AND (b = 1); -- invalid combination (not found in MCV) EXPLAIN ANALYZE SELECT * FROM t2 WHERE (a = 1) AND (b = 2); |
MCV列表为计划器提供了关于表中普遍出现的特定值的更详细的信息，以及表中未显示的值组合的选择性上限，允许它在这两种情况下产生更好的估计值。
兼容性
SQL标准中没有CREATE STATISTICS命令。
ALTER STATISTICS
ALTER STATISTICS — 更改扩展统计对象的定义
大纲
1 2 3 4 | ALTER STATISTICS name OWNER TO { new_owner | CURRENT_USER | SESSION_USER } ALTER STATISTICS name RENAME TO new_name ALTER STATISTICS name SET SCHEMA new_schema ALTER STATISTICS name SET STATISTICS new_target |
描述
ALTER STATISTICS更改现有扩展统计对象的参数。 任何在ALTER STATISTICS命令中没有明确设定的参数保持它们之前的设置。
您必须拥有统计对象才能使用ALTER STATISTICS。 要更改统计对象的模式，还必须在新模式上具有CREATE权限。 要更改所有者，还必须是新所有者角色的直接或间接成员， 且该角色在统计对象的模式上必须具有CREATE权限。 （这些限制强制了通过删除和重新创建统计对象来改变所有者不会做任何你不能做的事情， 但是超级用户可以改变任何统计对象的所有权。）
参数
name要修改的统计对象的名称（可能有模式修饰）。
new_owner统计对象的新所有者的用户名。
new_name统计对象的新名称。
new_schema统计对象的新模式。
new_target此统计信息对象的统计信息收集目标，用于随后的 ANALYZE 操作。 可以在0到10000范围内设置目标。 或者，将其设置为-1以恢复为使用所引用列的统计目标的 最大值（如果已设置）或系统默认统计目标 (default_statistics_target)。 有关 PostgreSQL 查询计划使用统计信息的更多信息，请参考 第 14.2 节.
兼容性
SQL标准中没有ALTER STATISTICS命令。
DROP STATISTICS
DROP STATISTICS — 删除扩展统计
大纲
1 | DROP STATISTICS [ IF EXISTS ] name [, ...] |
描述
DROP STATISTICS删除数据库中的统计对象。 只有统计对象的所有者、模式的所有者或超级用户可以删除统计对象。
参数
IF EXISTSname要删除的统计对象的名称（可以有模式修饰）。
示例
删除不同模式中的两个统计对象，如果不存在时不会失败：
1 2 3 | DROP STATISTICS IF EXISTS accounting.users_uid_creation, public.grants_user_role; |
兼容性
SQL标准中没有DROP STATISTICS命令。
