执行计划解决-part5
现在，在最后一篇文章中，我将尝试解释数据库是如何在操作 X和操作 Y之间做出选择的。
你可能听说过，数据库的查询规划器是基于统计信息来选择执行操作的。那么，这些统计信息到底是什么呢？
让我们想象一个最简单的情况：
SELECT * FROM table WHERE column = some_value;
如果所有行的 some_value 都相同，那么使用（即使存在）该列上的索引就没有意义了。
另一方面，如果该列是唯一（或近乎唯一）的，那么使用索引就是一个非常好的选择。
让我们看看发生了什么：
create table test ( all_the_same int4, almost_unique int4 );
CREATE TABLE
insert into test ( all_the_same, almost_unique )
select 123, random() * 1000000 from generate_series(1,100000);
INSERT 0 100000
现在，我有一个包含 100,000 行的表，其中 all_the_same 列的值始终相同（都是 123），而 almost_unique 列则几乎是唯一的：
select count(*), count(distinct almost_unique) from test;
count | count
--------+-------
100000 | 95142
(1 row)
现在，为了使条件对等，我将创建两个简单的索引：
create index i1 on test (all_the_same);
CREATE INDEX
create index i2 on test (almost_unique);
CREATE INDEX
好的，测试环境已准备就绪。那么，实际的执行计划会是什么样呢？
explain select * from test where all_the_same = 123;
QUERY PLAN
------------------------------------------------------------
Seq Scan on test (cost=0.00..1693.00 rows=100000 width=8)
Filter: (all_the_same = 123)
(2 rows)
explain select * from test where almost_unique = 123;
QUERY PLAN
---------------------------------------------------------------
Index Scan using i2 on test (cost=0.29..8.31 rows=1 width=8)
Index Cond: (almost_unique = 123)
(2 rows)
如你所见，数据库做出了明智的选择。但有趣的是rows=这个估算值——它是如何知道查询可能返回多少行的呢？
答案在于 ANALYZE 命令，或 VACUUM ANALYZE。
当对表执行ANALYZE 时，数据库会获取一些随机样本（稍后会详细说明），并基于这些样本来收集统计信息。那么，这些统计信息是什么？它们存储在哪里？我们能否查看它们？当然可以：
select * from pg_statistic where starelid = 'test'::regclass;
-[ RECORD 1 ]--------------------------------------------------------------------------
starelid | 16882
staattnum | 1
stainherit | f
stanullfrac | 0
stawidth | 4
stadistinct | 1
stakind1 | 1
stakind2 | 3
stakind3 | 0
stakind4 | 0
stakind5 | 0
staop1 | 96
staop2 | 97
staop3 | 0
staop4 | 0
staop5 | 0
stanumbers1 | {1}
stanumbers2 | {1}
stanumbers3 | [null]
stanumbers4 | [null]
stanumbers5 | [null]
stavalues1 | {123}
stavalues2 | [null]
stavalues3 | [null]
stavalues4 | [null]
stavalues5 | [null]
-[ RECORD 2 ]---------------------------------------------------------------------------
starelid | 16882
staattnum | 2
stainherit | f
stanullfrac | 0
stawidth | 4
stadistinct | -0.92146
stakind1 | 1
stakind2 | 2
stakind3 | 3
stakind4 | 0
stakind5 | 0
staop1 | 96
staop2 | 97
staop3 | 97
staop4 | 0
staop5 | 0
stanumbers1 | {0.0001,0.0001,0.0001,0.0001,0.0001,0.0001,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05}
stanumbers2 | [null]
stanumbers3 | {-0.000468686}
stanumbers4 | [null]
stanumbers5 | [null]
stavalues1 | {21606,27889,120502,289914,417495,951355,283,1812,3774,6028,6229,10372,12234,13291,18309,18443,21758,22565,26634,28392,28413,31208,32890,36563,39277,40574,44527,49954,53344,53863,56492,56715,60856,62993,64294,65275,65355,68353,71194,74718,77205,82096,82783,84764,85301,87498,90990,94043,97304,98779,101181,103700,103889,106288,108562,110796,113154,117850,121578,122643,123874,126299,129236,129332,129512,134430,134980,136987,137368,138175,139001,141519,142934,143432,143707,144501,148633,152481,154327,157067,157799,162437,164072,164337,165942,167611,170319,171047,177383,184134,188702,189005,191786,192718,196330,197851,199457,202652,202689,205983}
stavalues2 | {2,10560,20266,31061,40804,50080,59234,69240,79094,89371,99470,109557,119578,130454,140809,152052,162656,173855,183914,194263,204593,214876,224596,233758,243246,253552,264145,273855,283780,294475,303972,314544,324929,335008,346169,356505,367395,376639,387302,397004,407093,416615,426646,436146,445701,455588,466463,475910,485228,495434,505425,515853,525374,534824,545387,554794,563591,573721,584021,593368,602935,613238,623317,633947,643431,653397,664177,673976,684042,694791,703922,714113,724602,735848,745596,754477,764171,772535,781924,791652,801703,812487,822196,831618,841665,850722,861532,872067,881570,891654,901595,910975,921698,931785,940716,950623,960551,970261,979855,989540,999993}
stavalues3 | [null]
stavalues4 | [null]
stavalues5 | [null]
这个表（pg_statistic）当然在官方文档中有描述，但它非常晦涩难懂。当然，你也可以在源码中找到非常精确的解释，但这通常并不是最佳选择。
幸运的是，有一个表是基于这个表，它包含相同的数据，但以更易读的方式呈现：
select * from pg_stats where tablename = 'test';
-[ RECORD 1 ]----------+----------------------------------------------------------------schemaname | public
tablename | test
attname | all_the_same
inherited | f
null_frac | 0
avg_width | 4
n_distinct | 1
most_common_vals | {123}
most_common_freqs | {1}
histogram_bounds | [null]
correlation | 1
most_common_elems | [null]
most_common_elem_freqs | [null]
elem_count_histogram | [null]
-[ RECORD 2 ]----------+----------------------------------------------------------------
schemaname | public
tablename | test
attname | almost_unique
inherited | f
null_frac | 0
avg_width | 4
n_distinct | -0.92146
most_common_vals | {21606,27889,120502,289914,417495,951355,283,1812,3774,6028,6229,10372,12234,13291,18309,18443,21758,22565,26634,28392,28413,31208,32890,36563,39277,40574,44527,49954,53344,53863,56492,56715,60856,62993,64294,65275,65355,68353,71194,74718,77205,82096,82783,84764,85301,87498,90990,94043,97304,98779,101181,103700,103889,106288,108562,110796,113154,117850,121578,122643,123874,126299,129236,129332,129512,134430,134980,136987,137368,138175,139001,141519,142934,143432,143707,144501,148633,152481,154327,157067,157799,162437,164072,164337,165942,167611,170319,171047,177383,184134,188702,189005,191786,192718,196330,197851,199457,202652,202689,205983}
most_common_freqs | {0.0001,0.0001,0.0001,0.0001,0.0001,0.0001,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05}
histogram_bounds | {2,10560,20266,31061,40804,50080,59234,69240,79094,89371,99470,109557,119578,130454,140809,152052,162656,173855,183914,194263,204593,214876,224596,233758,243246,253552,264145,273855,283780,294475,303972,314544,324929,335008,346169,356505,367395,376639,387302,397004,407093,416615,426646,436146,445701,455588,466463,475910,485228,495434,505425,515853,525374,534824,545387,554794,563591,573721,584021,593368,602935,613238,623317,633947,643431,653397,664177,673976,684042,694791,703922,714113,724602,735848,745596,754477,764171,772535,781924,791652,801703,812487,822196,831618,841665,850722,861532,872067,881570,891654,901595,910975,921698,931785,940716,950623,960551,970261,979855,989540,999993}
correlation | -0.000468686
most_common_elems | [null]
most_common_elem_freqs | [null]
elem_count_histogram | [null]
好的。那么我们能从中学到什么呢？
schemaname、tablename 和 attname 列的含义似乎很明显。inherited 字段仅表示该表的值是否包含从继承了该列的任何父表中获得的值。
所以，如果我有：
create table z () inherits (test);
然后我在表 z 中插入一些数据，那么表 test 的统计信息中inherited = true。
其余各列的含义如下：
-
null_frac（空值比例）——表示该列中有多少比例的行包含空值（NULL）。这是一个分数值，范围在 0 到 1 之间。例如，0.1 表示 10% 的行是 NULL。
-
avg_width（平均宽度）——表示该列中数据的平均存储宽度（以字节为单位）。对于固定长度的数据类型（例如这里的 int4），这个值并不太有趣，因为长度是固定的。但对于可变长度类型（如 text、varchar、numeric 等），这个值就很有参考价值。
-
n_distinct（不同值的数量）——这是一个非常重要的统计值。
- 如果该值为正数（≥1），则表示该列中不同值的估计数量（注意：不是比例，而是具体的数量）。例如，all_the_same 列中所有值都相同，因此 n_distinct 正确地显示为 1。
- 如果该值为负数，则其含义不同：它表示具有唯一值的行所占的比例。例如，almost_unique 列的 n_distinct 为 -0.92146，表示约 92.146% 的行具有唯一值（略低于之前计算出的 95.142%）。
这些估计值可能不准确，原因是我们之前提到的“随机抽样”机制，稍后会进一步解释。
-
most_common_vals（最常见值）——这是一个数组，列出该列中出现频率最高的若干个值。例如，在 almost_unique 列中，PostgreSQL 认为值 21606、27889、120502、289914、417495、951355 是最常见的。
-
most_common_freqs（最常见值的频率）——与 most_common_vals 对应，表示这些值出现的频率（以分数表示，总和不超过 1）。例如，某个值出现 20% 的行中，则其频率为 0.2。在 almost_unique 中，这些“最常见值”实际上并不常见，这是由于“随机抽样”带来的偏差所致。
-
histogram_bounds（直方图边界）——一个值的数组，用于将整个数据集划分为包含大致相等行数的区间。例如，almost_unique 列中，值在 2 到 10560 之间的行数，大致等于值在 931785 到 940716 之间的行数。这有助于查询优化器估算范围查询的选择率（selectivity）。
-
correlation（相关性）——这是一个有趣的统计量，表示表中数据的物理存储顺序（磁盘上的行顺序）与该列值的逻辑顺序之间的相关程度。取值范围为 -1 到 1：
- 接近 1 或 -1 表示存在较强的相关性（即值的顺序与物理存储顺序高度一致）。
- 接近 0 表示几乎没有相关性。
例如，在执行CLUSTER test USING i2（即按 almost_unique 列对表进行物理重排序）后，相关性从原来的 -0.000468686 显著提升到 -0.919358，说明数据的物理顺序与列值顺序高度匹配。
-
most_common_elems、most_common_elem_freqs 和 elem_count_histogram —— 这三个字段类似于 most_common_vals、most_common_freqs 和 histogram_bounds，但用于非标量数据类型，例如数组（array）、全文检索向量（tsvector）等。它们分别表示最常见元素、元素频率以及元素数量的直方图，用于分析复合类型的数据分布。
所有这些统计信息都是基于对表数据的随机抽样生成的，因此可能与实际值存在偏差。可以通过调整
default_statistics_target或使用ANALYZE命令来提高统计准确性。
基于这些统计信息，PostgreSQL 能够估算查询中任意部分将返回多少行数据。有了这些估算，查询规划器就可以决定采用哪种访问方式更高效：是顺序扫描（seq scan）、索引扫描（index scan），还是位图索引扫描（bitmap index scan）。在执行表连接时，也能据此判断哪种连接方式更快：哈希连接（Hash Join）、归并连接（Merge Join），还是嵌套循环连接（Nested Loop）。
如果你仔细查看上面的统计输出，可能会产生一个疑问：输出内容非常宽，most_common_vals、most_common_freqs 和 histogram_bounds 这些数组中包含了很多值，为什么会有这么多？
原因很简单：这是由配置决定的。
在 postgresql.conf 配置文件中，有一个名为 default_statistics_target 的参数。这个参数控制着 PostgreSQL 在生成统计信息时，为这些数组保留多少个值。在我的例子中，使用的是默认值，即 100。这意味着每个列的最常见值列表最多保留 100 个值，直方图也最多划分为 100 个区间。
但你可以轻松地修改这个值：
- 全局修改：通过编辑
postgresql.conf文件，调整default_statistics_target的值，然后重新加载配置（或重启数据库，视修改方式而定）。 - 针对特定列修改：可以使用
ALTER TABLE ... ALTER COLUMN ... SET STATISTICS命令，为某一列单独设置统计目标值。例如：
ALTER TABLE test ALTER COLUMN almost_unique SET STATISTICS 200;
这会将 almost_unique 列的统计信息采样目标提高到 200，从而在执行 ANALYZE 时收集更详细的统计信息（如更长的 most_common_vals 数组或更精细的直方图）。
⚠️ 注意：提高
statistics_target可以提升查询规划的准确性，尤其是在数据分布复杂或倾斜的列上，但也会略微增加ANALYZE的执行时间和统计信息的存储开销。
因此，合理设置统计目标值，是优化查询执行计划的重要手段之一。
alter table test alter column almost_unique set statistics 5;
在执行上述 ALTER TABLE ... SET STATISTICS 命令并运行 ANALYZE 之后，pg_stats 中的数据变得更短了：
select * from pg_stats where tablename = 'test' and not inherited and attname = 'almost_unique';
-[ RECORD 1 ]----------+---------------------------------------------------------
schemaname | public
tablename | test
attname | almost_unique
inherited | f
null_frac | 0
avg_width | 4
n_distinct | -0.92112
most_common_vals | {114832,3185,3774,6642,11984}
most_common_freqs | {0.0001,6.66667e-05,6.66667e-05,6.66667e-05,6.66667e-05}
histogram_bounds | {2,199470,401018,596414,798994,999964}
correlation | 1
most_common_elems | [null]
most_common_elem_freqs | [null]
elem_count_histogram | [null]
更改统计目标还有另一个影响。
让我来演示一下。首先，我将撤销之前通过 ALTER TABLE 对统计数量所做的更改：
alter table test alter column almost_unique set statistics -1;
现在：
$ analyze verbose test;
INFO: analyzing "public.test"
INFO: "test": scanned 443 of 443 pages, containing 100000 live rows and 0 dead rows; 30000 rows in sample, 100000 estimated total rows
ANALYZE
$ alter table test alter column almost_unique set statistics 10;
ALTER TABLE
$ alter table test alter column all_the_same set statistics 10;
ALTER TABLE
$ analyze verbose test;
INFO: analyzing "public.test"
INFO: "test": scanned 443 of 443 pages, containing 100000 live rows and 0 dead rows; 3000 rows in sample, 100000 estimated total rows
ANALYZE
请注意，第二次 ANALYZE 仅检测了 3000 行，而不是第一次的 30000 行。
这就是所谓的“随机抽样”。
在中等或大型表上分析所有行的代价会非常高昂。
因此，数据库采取了一种相当聪明的做法。
首先，它会读取表中随机的一部分数据页（提醒：每个页面大小为 8kB）。读取的页数为 300 * statistics_target。
这意味着，在我的情况下，default_statistics_target = 100，它将读取 30000 页。（但我的表没有这么多页，因此实际上它读取了全部页。）
从这些页面中，ANALYZE 仅获取有关存活行和死亡行的信息。随后，它再从随机抽样的行中获取数据，抽样行数同样是 300 * statistics_target，并基于这些数据计算列的统计信息。
在我的例子中，表有 100,000 行，但使用 default_statistics_target = 100 时，只有约三分之一的行被分析。当统计目标值降低时，被分析的行数更少——仅有 3000 行。
你可能会说：好吧，但这样一来这些统计信息就不准确了。有可能某个非常常见的值恰好没有出现在被扫描的行中。没错，你说得对，这种情况是可能的。但其实并不太可能。我的意思是——你获取的是数据的一个随机部分。恰好抽到的那 x% 的数据中完全不包含某个在其他所有行中都存在的值，这种概率是很小的。
这也意味着，有时运行 ANALYZE 可能会“破坏”你的查询。例如——你获取了不同数据页的统计信息，恰好导致某些值被跳过（或者相反——某些实际上并不常见的值，仅仅因为数据库恰好抽到了包含这些值的页/行，而被计入 most_common_vals）。基于这样的统计信息，数据库可能会生成次优的执行计划。
如果遇到这种情况，解决方案相当简单——提高统计目标值（statistics target）。这会使 ANALYZE 执行更多的工作，扫描更多的行，从而进一步降低此类问题再次发生的可能性。
不过，设置过大的统计目标值也有缺点。ANALYZE 当然需要做更多的工作，但这是维护性的开销，通常我们并不太在意。真正的问题在于，pg_statistic 中包含的数据越多，PostgreSQL 查询规划器在生成执行计划时就需要考虑更多的数据。因此，尽管将 default_statistics_target 设置为其最大值 10,000 看起来很诱人，但实际上我从未见过哪个数据库将其设置得如此之高。
当前默认值 100 是从 8.4 版本开始引入的。在此之前的版本中，默认值为 10，那时在 IRC 上经常能看到建议用户将其调高的情况。现在，默认值为 100，大多数情况下已经足够，基本无需调整。
最后我还得提一下一个我不太想谈的话题，那就是那些让 PostgreSQL 查询规划器采用不同操作方式的设置参数。
首先，我不想谈论这个问题的原因是——我非常清楚这些设置很容易被滥用。所以请务必记住：这些设置仅用于调试问题，而不是用来解决生产问题的。任何在正常运行模式下使用这些设置的应用程序，至少是值得怀疑的，甚至可以说是完全错误的。没错，我知道有时候你不得不这么做，但那种情况极为罕见。
现在，说完了这些，我们来看一下这些选项。
在 postgresql.conf 中，你有以下几个类似的设置：
enable_bitmapscan = on enable_hashagg = on enable_hashjoin = on enable_indexscan = on enable_indexonlyscan = on enable_material = on enable_mergejoin = on enable_nestloop = on enable_seqscan = on enable_sort = on enable_tidscan = on
这些设置用于禁用特定的执行操作。
例如，将 enable_seqscan 设置为 false（可以通过 SQL 会话中的 SET 命令来实现，无需修改 postgresql.conf），会导致查询规划器尽可能使用其他方式，以避免执行顺序扫描（seq scan）。
但由于在某些情况下无法避免顺序扫描（例如表上根本没有索引），因此这些设置实际上并不会完全禁用对应的操作，而是会给使用该操作赋予一个极高的代价（cost）。
例如，对于我们的测试表，我们知道使用 “all_the_same = 123” 进行查询会采用顺序扫描，因为这种方式代价较低：
explain select * from test where all_the_same = 123;
QUERY PLAN
------------------------------------------------------------
Seq Scan on test (cost=0.00..1693.00 rows=100000 width=8)
Filter: (all_the_same = 123)
(2 rows)
但如果我禁用顺序扫描：
set enable_seqscan = false;
SET
explain select * from test where all_the_same = 123;
QUERY PLAN
-----------------------------------------------------------------------
Index Scan using i1 on test (cost=0.29..3300.29 rows=100000 width=8)
Index Cond: (all_the_same = 123)
(2 rows)
我们可以看到，使用索引扫描获取相同数据的预估代价大约是原来的两倍（3300.29 对比 1693）。
如果我删除 i1 索引：
drop index i1;
DROP INDEX
set enable_seqscan = false;
SET
explain select * from test where all_the_same = 123;
QUERY PLAN
-----------------------------------------------------------------------------
Seq Scan on test (cost=10000000000.00..10000001693.00 rows=100000 width=8)
Filter: (all_the_same = 123)
(2 rows)
现在我们看到，当没有其他选择时——只能使用顺序扫描（有趣的是它并没有选择在 i2 上进行索引扫描，毕竟 i2 包含了表中所有行的指针）——代价急剧上升到 10,000,000,000，这正是设置 enable_* = false 所造成的效果。
