由 John Doe 二月 26, 2024
PostgreSQL 会收集索引表达式的统计信息，以便选择出好的查询计划。
PostgreSQL 规划器的选择率估计偏差
让我们看看下面这个例子：
CREATE TABLE test AS (
SELECT random() x, random() y FROM generate_series(1, 1000000));
ANALYZE test;
EXPLAIN ANALYZE SELECT * FROM test WHERE x + y < 0.01;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------
Seq Scan on test (cost=0.00..20406.00 rows=333333 width=16) (actual time=1.671..113.693 rows=56 loops=1)
Filter: ((x + y) < '0.01'::double precision)
Rows Removed by Filter: 999944
我们创建了一个表，其中有两列x和y，它们的值相互独立，且均匀地分布在 0 到 1 之间。尽管我们对该表进行了 ANALYZE，但 PostgreSQL 优化器估计x + y < 0.01条件的选择率为 1/3。您可以看到，这个估计和实际值相差甚远：我们实际上查到了 56 行，而不是估计的 333333 行。这个估计来自一个粗略的假设，即除非知道更精确的信息，否则<操作符会选择出 1/3 的行。当然，在这种情况下，规划器有可能表现更好。例如，它可以尝试从x和y各自的直方图计算出x + y的直方图。然而，PostgreSQL 优化器目前没有执行如此昂贵和复杂的计算。
通过索引为表达式生成统计信息
一旦我们基于x + y定义了一个索引，情况就发生了变化。
CREATE INDEX test_idx ON test ((x + y));
ANALYZE test;
EXPLAIN ANALYZE SELECT * FROM test WHERE x + y < 0.01;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on test (cost=13.39..1838.32 rows=641 width=16) (actual time=0.040..0.107 rows=56 loops=1)
Recheck Cond: ((x + y) < '0.01'::double precision)
Heap Blocks: exact=56
-> Bitmap Index Scan on test_idx (cost=0.00..13.23 rows=641 width=0) (actual time=0.028..0.028 rows=56 loops=1)
Index Cond: ((x + y) < '0.01'::double precision)
除了将索引用于此查询以外，还获得了更准确的方式，来估计x + y < 0.01选择的行数。行数估计得到了改进，因为 PostgreSQL 现在会为x + y表达式收集单独的统计数据。您可以通过查询系统表来确认这一点。
SELECT * FROM pg_stats WHERE tablename = 'test_idx';
-[ RECORD 1 ]----------+--------------------------------------------------------------------------------------------------------------------------------------------
schemaname | public
tablename | test_idx
attname | expr
inherited | f
null_frac | 0
avg_width | 8
n_distinct | -0.999863
most_common_vals | {0.262215601745993,0.319712610449642,0.3959802063182,0.404356196057051,0.40578526025638,0.437070866115391,0.462984828744084,0.4651908758096
most_common_freqs | {2e-06,2e-06,2e-06,2e-06,2e-06,2e-06,2e-06,2e-06,2e-06,2e-06,2e-06,2e-06,2e-06,2e-06,2e-06,2e-06,2e-06,2e-06,2e-06,2e-06,2e-06,2e-06,2e-06,
histogram_bounds | {0.00104234321042895,0.0141074191778898,0.0200657406821847,0.0247588600032032,0.0284962640143931,0.0315022920258343,0.0346860070712864,0.03
correlation | -0.00176553
most_common_elems | NULL
most_common_elem_freqs | NULL
elem_count_histogram | NULL
因此，对于x + y表达式，产生了直方图、最常见值等统计信息，这导致对于x + y < 0.01有了更准确的选择率估计。然而，仍然存在 1 个数量级的误差（估计为 641 行而不是 56 行）。我们能改善这一点吗？是的，PostgreSQL有一个收集统计信息的质量参数，可以使用 ALTER TABLE … SET STATISTICS … 命令对每列进行调优。使用此命令，您可以调整统计数据数组的大小。
但在我们的例子中，我们没有列，只有一个索引表达式。这似乎是一个问题，因为文档中没有记录的方法来优化统计质量。
优化索引表达式的统计质量
然而，这似乎是可能的。有一个方法允许高级 DBA 这样做。
ALTER INDEX test_idx ALTER COLUMN expr SET STATISTICS 10000;
ANALYZE test;
EXPLAIN ANALYZE SELECT * FROM test WHERE x + y < 0.01;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on test (cost=4.96..258.61 rows=69 width=16) (actual time=0.022..0.074 rows=56 loops=1)
Recheck Cond: ((x + y) < '0.01'::double precision)
Heap Blocks: exact=56
-> Bitmap Index Scan on test_idx (cost=0.00..4.94 rows=69 width=0) (actual time=0.014..0.014 rows=56 loops=1)
Index Cond: ((x + y) < '0.01'::double precision)
该方法起作用了。当我们使用大小为 10000 的数组收集统计信息时，估计行数变成了 69。它只有 23% 的估计误差，这对于查询规划来说已经足够好了。
总结
ALTER INDEX ... SET STATISTICS ...是什么意思？在 PostgreSQL 文档中没有这样的说明。
让我们一步一步地理解下这个情况。
ALTER INDEX和ALTER TABLE采用了相同的语法规则。ALTER INDEX不适用的情况，在运行时会被排除掉。ALTER INDEX ... SET STATISTICS ...没有被禁止，并且与ALTER TABLE ... SET STATISTICS ...的工作方式相同。- 索引表达式在内部作为属性命名为：
expr、expr1、expr2…