您所不了解的Postgres 10功能：CREATE STATISTICS（译）
优化器（planner）收集统计数据是决定使用哪种计划的非常重要的参考信息。 这些统计信息使优化器（planner）可以估计执行计划的特定部分后将返回多少行，这将影响执行计划将要使用的扫描或联接算法的类型。 统计主要通过运行ANALYZE或VACUUM（以及一些DDL命令，如CREATE INDEX）来收集/更新它们。
这些统计信息由存储在pg_class和pg_statistics中。 Pg_class基本上存储每个表和索引中的条目总数，以及它们所占用的磁盘块数。 Pg_statistic存储有关每个列的统计信息，例如该列的值的为空的百分比，最常见的值是什么，直方图范围等。 您可以在下面的表格中查看以下示例，该示例针对针对col1收集的Postgres统计类型。 下面的查询输出显示，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）将假定列（或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），为了计算将被这两个“独立”条件过滤的行数，计划器将其选择性乘以。 因此，我们得到：选择性= 0.001 * 0.01 = 0.00001。
将其乘以表中的行数，即10000000，我们得到100。这就是计划者估计的100的来源。
如果这些列不是独立的（有多个列之间存在依赖关系），我们如何告诉优化器（planner）呢？ 译者注：
早些年曾经执着地研究过SQLServer对非相关列预估的算法，
类似于pg，SQLServer从预估行数从2012版的p 0*p 1*p 2*p 3……*RowCount，演变为P 0*P 1 1/2 * P 2 1/4 * P 3 1/8……* RowCount， https://www.cnblogs.com/wy123/p/5790855.html 在PostgreSQL创建统计表信息 在Postgres 10之前，没有一种简单的方法可以告诉优化器（planner）收集统计数据，这些统计数据捕获了列之间的这种关系。 但是，在Postgres 10中，有一个新功能可以解决此问题。 CREATE STATISTICS可用于创建扩展的统计对象，这些对象告诉服务器收集有关这些有趣的相关列的额外统计信息。
相关列的统计信息(Functional dependency statistics)
回到我们先前的估计问题，问题在于col2的值实际上只是col的1/10。 译者注：一个表中有两个字段c1和c2，比如c1代表“省份Id”，c2代表“县Id”，这样c1和c2就存在依赖关系。 在数据库术语中，我们可以说col2在功能上取决于col1。这意味着col1的值足以确定col2的值，并且没有两行具有相同的col1值但具有不同的col2值。 因此，col2上的第二个过滤器实际上不会删除任何行！但是，优化器（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。现在，所有在这两个列上都具有过滤器的查询将具有更好的估计。 非相关列的统计信息（ndistinct statistics） 功能依赖性是可以在列之间捕获的一种关系。您可以捕获的另一种统计数据是一组列的不同值的数量。 前面我们曾提到，计划者为每一列捕获了不同值数量的统计信息，但是当组合多个列时，这些统计信息常常是错误的 译者注：比如一个订单表中有两个字段c1和c2，比如c1代表“UserId”，c2代表订单类型“OrderType”（假如有服饰，食品，3C产品等），很明显，一个用户可以随意购买任何类型的商品，UserId和OrderType之间没有任何依赖关系 糟糕的统计数据何时会伤害我们？让我们来看一个例子。
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。也许，我们有足够的内存来将它们容纳在内存中，并进行哈希聚合。 让我们要求 优化器(planner)捕获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} Real-world implications
实际情况下的影响
在实际的生产模式中，您总是会拥有某些列，而这些列之间具有数据库不知道的相互依存关系。我们与Citus客户一起看到的一些例子是：
- 由于要在报表中显示按所有人分组的统计信息，因此具有月，季度和年的列。
- 地理层次结构之间的关系,例如:具有国家，州和城市列，并按它们进行过滤/分组。
当我们着手构建Citus时，我们明确选择了Postgres作为基础。通过扩展Postgres，我们选择了一个坚实的基础，可以随着每个发行版的不断完善。
因为Citus是纯粹的扩展，而不是分支，所以使用Citus时可以利用每个发行版中的所有出色新功能。 享受您正在阅读的内容吗？
如果您有兴趣阅读我们团队的更多帖子，请注册我们的每月时事通讯，并将最新内容直接发送到您的收件箱。
目录 返回
首页