如果你用Postgres做了一些性能调优，你可能用过EXPLAIN。EXPLAIN向你展示了PostgreSQL计划器为所提供的语句生成的执行计划，它显示了语句所引用的表如何被扫描（使用顺序扫描、索引扫描等）。它显示了语句所引用的表将如何被扫描（使用顺序扫描，索引扫描等），以及如果使用多个表，将使用什么连接算法。但是，Postgres是如何提出这些计划的呢？
决定使用哪种计划的一个非常重要的输入是计划员收集的统计数据。这些统计数据让计划员能够估计在执行计划的某一部分后会返回多少行，然后影响将使用的扫描或连接算法的种类。它们主要是通过运行ANALYZE或VACUUM（以及一些DDL命令，如CREATE INDEX）来收集/更新的。
这些统计数据被规划者存储在pg_class和pg_statistics中。Pg_class基本上存储了每个表和索引的总条目数，以及它们占用的磁盘块数。Pg_statistic存储的是每一列的统计数据，比如该列有多少%的值是空的，最常见的值是什么，直方图界限等。在下面的表格中，你可以看到Postgres为col1收集到的统计数据的例子。下面的查询输出显示，planner（正确）估计表中col1列有1000个不同的值，还对最常见的值、频率等进行了其他估计。
请注意，我们已经查询了pg_stats（一个持有更可读的列统计版本的视图）。
CREATE TABLE tbl (
col1 int,
col2 int
);
INSERT INTO tbl SELECT i/10000, i/100000
FROM generate_series (1,10000000) s(i);
ANALYZE tbl;
select * from pg_stats where tablename = 'tbl' and attname = 'col1';
-[ RECORD 1 ]----------+---------------------------------------------------------------------------------
schemaname | public
tablename | tbl
attname | col1
inherited | f
null_frac | 0
avg_width | 4
n_distinct | 1000
most_common_vals | {318,564,596,...}
most_common_freqs | {0.00173333,0.0017,0.00166667,0.00156667,...}
histogram_bounds | {0,8,20,30,39,...}
correlation | 1
most_common_elems |
most_common_elem_freqs |
elem_count_histogram |
当单列统计不够用的时候
这些单列统计有助于planner估计条件的选择性（这就是planner用来估计索引扫描将选择多少行的原因）。当在查询中提供了多个条件时，planner会假设这些列（或where子句条件）是相互独立的。当列之间相互关联或相互依赖时，这就不成立了，这将导致规划者低估或高估这些条件所返回的行数。
下面我们来看几个例子。为了使计划简单易读，我们通过设置max_parallel_workers_per_gather为0来关闭每个查询的并行性。
EXPLAIN ANALYZE SELECT * FROM tbl where col1 = 1;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------
Seq Scan on tbl (cost=0.00..169247.80 rows=9584 width=8) (actual time=0.641..622.851 rows=10000 loops=1)
Filter: (col1 = 1)
Rows Removed by Filter: 9990000
Planning time: 0.051 ms
Execution time: 623.185 ms
(5 rows)
正如你在这里看到的，planner估计col1的值为1的行数为9584，而查询返回的实际行数为10000。所以，非常准确。
但是，当你在第1列和第2列上都包含过滤器时，会发生什么呢？
EXPLAIN ANALYZE SELECT * FROM tbl where col1 = 1 and col2 = 0;
QUERY PLAN
----------------------------------------------------------------------------------------------------------
Seq Scan on tbl (cost=0.00..194248.69 rows=100 width=8) (actual time=0.640..630.130 rows=10000 loops=1)
Filter: ((col1 = 1) AND (col2 = 0))
Rows Removed by Filter: 9990000
Planning time: 0.072 ms
Execution time: 630.467 ms
(5 rows)
planner的估算已经偏离了100倍! 让我们试着了解一下为什么会出现这种情况。
第一列的选择性大约是0.001（1/1000），第二列的选择性是0.01（1/100）。为了计算被这2个 "独立 "条件过滤的行数，planner将它们的选择性相乘。所以，我们得到
选择性 = 0. 001 * 0. 01 = 0. 00001.
当这个乘以我们在表中的行数即10000000时，我们得到100。这就是planner估计的100的由来。但是，这几列不是独立的，我们怎么告诉planner呢？
在PostgreSQL中CREATE STATISTICS
在Postgres 10之前，并没有一个简单的方法来告诉计划员收集统计数据，从而捕捉到列之间的这种关系。但是，在Postgres 10中，有一个新的功能正是为了解决这个问题而建立的。CREATE STATISTICS可以用来创建扩展的统计对象，它可以告诉服务器收集关于这些有趣的相关列的额外统计。
功能依赖性统计
回到我们之前的估算问题，问题是col2的值其实不过是col 1 / 10。在数据库术语中，我们会说col2在功能上依赖于col1。这意味着col1的值足以决定col2的值，不存在两行col1的值相同而col2的值不同的情况。因此，col2上的第2个过滤器实际上并没有删除任何行！但是，planner捕捉到了足够的统计数据。但是，规划者捕捉到了足够的统计数据来知道这一点。
让我们创建一个统计对象来捕获关于这些列的功能依赖统计，并运行ANALYZE。
CREATE STATISTICS s1 (dependencies) on col1, col2 from tbl;
ANALYZE tbl;
让我们看看planner现在拿出了什么。
EXPLAIN ANALYZE SELECT * FROM tbl where col1 = 1 and col2 = 0;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------
Seq Scan on tbl (cost=0.00..194247.76 rows=9584 width=8) (actual time=0.638..629.741 rows=10000 loops=1)
Filter: ((col1 = 1) AND (col2 = 0))
Rows Removed by Filter: 9990000
Planning time: 0.115 ms
Execution time: 630.076 ms
(5 rows)
好多了! 我们来看看是什么帮助planner做出了这个决定。
SELECT stxname, stxkeys, stxdependencies
FROM pg_statistic_ext
WHERE stxname = 's1';
stxname | stxkeys | stxdependencies
---------+---------+----------------------
s1 | 1 2 | {"1 => 2": 1.000000}
(1 row)
从这一点来看，我们可以看到Postgres意识到col1完全决定了col2，因此有一个系数为1来捕捉这些信息。现在，所有对这两列进行过滤的查询都会有更好的估计。
差异化统计
功能依赖性是你可以捕获列之间的一种关系。另一种你可以捕捉的统计是一组列的不同值的数量。我们在前面提到过，planner捕捉到的是每一列的独特值数的统计，但是当组合多于一列时，这些统计又经常出错。
什么时候有不好的独特统计会伤害到我呢？让我们来看一个例子。
EXPLAIN ANALYZE SELECT col1,col2,count(*) from tbl group by col1, col2;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
GroupAggregate (cost=1990523.20..2091523.04 rows=100000 width=16) (actual time=2697.246..4470.789 rows=1001 loops=1)
Group Key: col1, col2
-> Sort (cost=1990523.20..2015523.16 rows=9999984 width=8) (actual time=2695.498..3440.880 rows=10000000 loops=1)
Sort Key: col1, col2
Sort Method: external sort Disk: 176128kB
-> Seq Scan on tbl (cost=0.00..144247.84 rows=9999984 width=8) (actual time=0.008..665.689 rows=10000000 loops=1)
Planning time: 0.072 ms
Execution time: 4494.583 ms
聚合行时，Postgres会选择做哈希聚合或分组聚合。如果它能在内存中装下哈希表，它就选择哈希聚合，否则它选择将所有的行进行排序，然后根据col1，col2进行分组。
现在，planner估计组的数量（等于col1，col2的不同值的数量）将是100000。它看到它没有足够的work_mem来存储这个哈希表在内存中。所以，它使用基于磁盘的排序来运行查询。然而，在计划的实际部分可以看到，实际行数只有1001。而也许，我们有足够的内存将它们装入内存，并进行哈希聚合。
我们让计划员抓取n_distinct统计，重新运行查询，就知道了。
CREATE STATISTICS s2 (ndistinct) on col1, col2 from tbl;
ANALYZE tbl;
EXPLAIN ANALYZE SELECT col1,col2,count(*) from tbl group by col1, col2;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=219247.63..219257.63 rows=1000 width=16) (actual time=2431.767..2431.928 rows=1001 loops=1)
Group Key: col1, col2
-> Seq Scan on tbl (cost=0.00..144247.79 rows=9999979 width=8) (actual time=0.008..643.488 rows=10000000 loops=1)
Planning time: 0.129 ms
Execution time: 2432.010 ms
(5 rows)
你可以看到，现在的估计值更加准确了（即1000），查询速度也快了2倍左右。我们可以通过运行下面的查询，看看planner学到了什么。
SELECT stxkeys AS k, stxndistinct AS nd
FROM pg_statistic_ext
WHERE stxname = 's2';
k | nd
-----+----------------
1 2 | {"1, 2": 1000}
现实的影响
在实际的生产模式中，你总会有某些列，它们之间有依赖关系或关系，而数据库并不知道。我们在云端的Citus开源和Citus客户中看到的一些例子是。
有月、季、年的列，因为你想在报表中显示所有分组的统计数据。
地理层次结构之间的关系。例如，拥有国家、州和城市列，并通过它们进行过滤/分组。
这里的例子在数据集中只有10M行，我们已经看到，在有相关列的情况下，使用CREATE统计可以显著改善计划，也显示出性能的提高。我们有用户存储了数十亿行的数据，糟糕的计划会带来巨大的影响。在我们的例子中，当计划员选择了一个糟糕的计划时，我们不得不对10M行进行基于磁盘的排序，想象一下，如果有几十亿行的数据，会有多糟糕。
