如果你曾经用Postgres做过一些性能调优，你可能用过EXPLAIN。EXPLAIN向您显示PostgreSQL计划程序为提供的语句生成的执行计划。说明语句中涉及的表会使用顺序扫描、索引扫描等方式进行扫描，在使用多表的情况下会使用join算法。但是Postgres如何生成这些计划呢？决定使用哪个计划的一个非常重要的输入是计划者收集的统计数据。这些统计数据使计划者能够估计执行计划的某个部分将返回多少行，这反过来会影响使用哪种计划或连接算法。它们主要是通过运行ANALYZE或VACUUM（以及一些DDL命令，如CREATEINDEX）来收集或更新的。这些统计信息由规划器存储在pg_class和pg_statistics中。pg_class基本上存储了每个表和索引的条目总数，以及它们占用的磁盘块数。pg_statistic存储了每一列的统计信息，比如哪些列的%值为nulll，哪些是最常见的值，直方图边界等。你可以看看下面的例子，看看Postgres为col1收集的统计类型在下表。下面的查询输出显示规划器（正确地）估计表的col1列中有1000个不同的值，并且还对最常见的值、频率等进行了其他估计。注意我们查询了pg_stats（具有更多可读版本的列统计信息的视图）。CREATETABLEtbl(col1int,col2int);INSERTINTOTblSELECTi/10000,i/100000FROMgenerate_series(1,10000000)s(i);ANALYZEtbl;select*frompg_statswheretablename='tbl'andattname='col1';-[RECORD1]---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------schemaname|publictablename|tblattname|col1inherited|fnull_frac|0avg_width|4n_distinct|1000most_common_vals|{318,564,596,...}most_common_freqs|{0.00173333,0.0017,0.00166667,0.00156667,...}histogram_bounds|{0,8,20,30,39,...}correlation|1most_common_elems|most_common_elem_freqs|elem_count_histogram|当单列统计量不足时，这些单列统计量可以帮助planner估算您的条件选择属性（这是规划器用来估计索引扫描将选择多少行的内容）当查询中有多个条件时，规划器假定列（或where子句条件）彼此独立。当列相关或相关并导致规划器低估或高估这些条件将返回的行数时不适用。让我们看下面的几个例子。为了使查询计划易于阅读，我们通过将max_parallel_workers_per_gather设置为0来关闭每个查询的并行性：EXPLAINANALYZESELECT*FROMtblwherecol1=1;QUERYPLAN----------------------------------------------------------------------------------------------------SeqScanontbl(cost=0.00..169247.80rows=9584width=8)(actualtime=0.641..622.851rows=10000loops=1)Filter:(col1=1)RowsRemovedbyFilter:9990000Planningtime:0.051msExecutiontime:623.185ms(5rows)可以看到，planner估计col1值为的行数19584，而查询返回的实际行数是10000，非常准确。当您在第1列和第2列都包含过滤器时会发生什么。EXPLAINANALYZESELECT*FROMtblwherecol1=1andcol2=0;QUERYPLAN----------------------------------------------------------------------------------------------------------SeqScanontbl(cost=0.00..194248.69rows=100width=8)(actualtime=0.640..630.130rows=10000loops=1)Filter:((col1=1)AND(col2=0))RowsRemovedbyFilter:9990000Planningtime:0.072msExecutiontime:630.467ms(5rows)Planner的估计减少了100倍！让我们试着理解为什么会这样。第一根色谱柱的选择性约为0.001(1/1000)，第二根色谱柱的选择性为0.01(1/100)。为了计算将被这两个“独立”条件过滤的行数，规划器将它们的选择性相乘。因此，我们得到：选择性=0.001*0.01=0.00001。当它乘以我们在表中的行数（10000000）时，我们得到100。这就是规划器估计100的来源。但是，这些列并不是独立的，那我们怎么告诉planner呢？在PostgreSQL中创建统计信息在Postgres10之前，没有简单的方法告诉规划器收集捕获列之间关系的统计信息。然而，Postgres10有一个新特性正好解决了这个问题。您可以使用CREATESTATISTICS创建扩展统计对象并告诉服务器为这些有趣的相关列收集额外的统计信息。函数依赖统计回到我们之前的评估问题，col2的值就是col1/10。用数据库术语来说，我们会说col2在功能上依赖于col1，即col1的值足以决定col2的值，不存在两行col1值相同但值不同的数据col2的。因此，col2上的第二个过滤器没有删除任何行！但是，规划器捕获了足够的统计数据来了解这一点。让我们创建一个stats对象来捕获这些列的统计信息以及ANALYZE所依赖的函数。CREATESTATISTICSs1(dependencies)oncol1,col2fromtbl;ANALYZEtbl;让我们看看当前的计划是怎么来的。EXPLAINANALYZESELECT*FROMtblwherecol1=1andcol2=0;QUERYPLAN----------------------------------------------------------------------------------------------------SeqScanontbl(cost=0.00..194247.76rows=9584width=8)(actualtime=0.638..629.741rows=10000loops=1)Filter:((col1=1)AND(col2=0))RowsRemovedbyFilter:9990000Planningtime:0.115msExecutiontime:630.076ms(5rows)好！让我们看看规划的衡量标准。SELECTstxname,stxkeys,stxdependenciesFROMpg_statistic_extWHEREstxname='s1';stxname|stxkeys|stxdependencies--------+--------+--------------------s1|12|{"1=>2":1.000000}(1row)看这里，我们可以看到Postgres意识到col1完全决定col2，因此使用1的因子来捕获此信息。现在，在所有查询过滤这些列之后，计划将得到更好的评估。ndistinct统计函数依赖关系是您可以在列之间捕获的关系。您可以捕获的另一种统计信息是一组列的不同值。前面我们指出，规划器可以获得每一列的不同值的统计信息，但是当多个列组合在一起时，这些统计信息往往是错误的。这些不好的数据是什么时候影响到我们的？让我们看一个例子。EXPLAINANALYZESELECTcol1,col2,count(*)fromtblgroupbycol1,col2;QUERYPLAN--------------------------------------------------------------------------------------------------------------------------GroupAggregate(cost=1990523.20..2091523.04rows=100000width=16)(actualtime=2697.246..4470.789rows=1001loops=1)GroupKey:col1,col2->Sort(cost=1990523.20..2015523.16rows=9999984width=8)(actualtime=2.680loop0s080=1000Key:col1,col2SortMethod:externalsortDisk:176128kB->SeqScanontbl(cost=0.00..144247.84rows=9999984width=8)(actualtime=0.008..665.689rows=10000000loops=1)Planningtime:0.072grems4:5聚合时选择行做hash聚合或者组合，如果它认为一个hash表合适，就选择hash聚合，否则选择对所有行排序，然后按col1,col2分组。现在planner估计组数（等于numbercol1,col2)的不同值的数量将为100000。它预计它没有足够的work_mem将哈希表存储在内存中。因此，它使用基于磁盘的排序来运行查询。但是，正如您在查询计划中看到的那样，实际的行数只有1001。也许，我们有足够的内存来执行哈希聚合。让规划器捕获n_distinct统计信息，重新运行查询并找出结果。CREATESTATISTICSs2(ndistinct)oncol1,col2fromtbl;ANALYZEtbl;EXPLAINANALYZESELECTcol1,col2,count(*)fromtblgroupbycol1,col2;QUERYPLAN--------------------------------------------------------------------------------------------------------------------------HashAggregate(cost=219247.63..219257.63rows=1000width=16)(actualtime=2431.767..2431.928rows=1001loops=1)GroupKey:col1,col2->SeqScanontbl(cost=0.00..144247.79rows=9999979width=8)(actual8time=0.00..643.4800rows000000000=1)Planningtime:0.129msExecutiontime:2432.010ms(5rows)可以看出现在估计精度更高了（也就是1000），查询速度也提高了2倍左右。我们可以通过运行下面的查询来查看规划器学到了什么。SELECTstxkeysASk,stxndistinctASndFROMpg_statistic_extWHEREstxname='s2';k|nd-----+----------------12|{"1,2":1000}现实对实际生产的影响在您的架构中，您将始终与数据库不知道的列具有一些相互依赖关系或关系。以下是我们在Citus客户中看到的一些示例：有月、季度和年列，因为您希望在报告中显示按所有者分组的统计数据。地理级别之间的关系。例如。有国家、州和城市的列，并按它们进行过滤/分组。这里的示例仅在数据集中只有10M行的情况下，我们已经看到使用CREATE统计信息可以显着改进查询计划并在存在相关列的情况下显示性能改进。在Citus用例中，我们的客户存储了数十亿行数据，糟糕的查询计划的影响可能很严重。在上面的例子中，当规划器选择了一个错误的查询计划时，我们不得不对1000万行进行基于磁盘的排序。想象一下，如果它有数十亿行，那会有多糟糕。Postgres一直在变得更好当我们开始构建Citus时，我们明确选择了Postgres作为构建的基础。通过扩展Postgres，我们选择了一个随着每个版本迭代而变得更好的坚实基础。由于Citus是一个纯粹的扩展，而不是一个分支，因此在使用Citus时可以利用每个版本中出现的所有重要新功能。喜欢上面的吗？如果您有兴趣阅读我们团队的更多文章，请注册我们的每月时事通讯，我们会直接将最新消息发送到您的收件箱。
