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)