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 行。这个估计来自一个粗略的假设，即除非知道更精确的信息，否则 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)   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 文档中没有这样的说明。

让我们一步一步地理解下这个情况。

1. ALTER INDEX和ALTER TABLE采用了相同的语法规则。

2. ALTER INDEX不适用的情况，在运行时会被排除掉。

3. ALTER INDEX ... SET STATISTICS ...没有被禁止，并且与ALTER TABLE ... SET STATISTICS ...的工作方式相同。

4. 索引表达式在内部作为属性命名为：expr、expr1、expr2 …