我的 Postgres 表有一个范围列,其中包含带时区的时间戳。我已在范围的下限上创建了一个索引,如下所示:
CREATE INDEX bdg_sys_period_start_idx ON building USING btree (lower(sys_period));
现在我尝试运行以下查询:
select * from building where lower(sys_period) > '2024-05-12 10:31:14.481545+01'::timestamptz;
接下来是有趣的部分。我对表运行 ANALYZE,然后对查询运行 EXPLAIN。我得到了以下结果:
太好了,Postgres 想要使用我的新索引!
然后我启动查询,花了很长时间。我停止查询,然后再次运行 EXPLAIN。令人惊讶的是,查询规划器现在告诉我他想使用顺序扫描。
我发现计划返回的行数从 97k 增加到了 160 万。实际返回的行数是 30 行。
对于这种情况我有很多疑问:
- 查询规划器为什么突然改变主意?
- 是否应该收集范围列的统计信息?我看过这个讨论,但我不确定这是否已经实施。
- 我曾尝试直接在 lower(sys_period) 上创建自定义统计数据:
CREATE STATISTICS IF NOT EXISTS sys_period_start_range ON ( lower(sys_period) ) FROM building;
这会有用吗?
- 我尝试增加 sys_period 列的统计信息的大小
ALTER TABLE building ALTER sys_period SET STATISTICS 1000;
这会有用吗?
在此先感谢您的帮助。
我终于明白了,我觉得自己很愚蠢。我将描述发生了什么,以防其他人遇到同样的问题。我正在使用 DBeaver,并且我为此数据库使用的连接已禁用自动提交。当我运行时
analyze
,它会启动一个事务。然后当我运行时explain
,它会给我预期的查询计划器结果(索引扫描)并回滚当前事务。当我再次运行查询计划器时,先前的查询计划器analyze
已被回滚,并且seq scan
再次出现。您确定您在创建索引
ANALYZE
之后、运行查询之前运行了第一个查询吗?如果您在创建索引ANALYZE
之前运行了查询,那么这就可以解释为什么规划器选择了不同的计划。PostgreSQL 开始收集索引表达式的统计信息,但这在下一次运行之前不会发生ANALYZE
。扩展统计数据是不必要的——它只是重复自动发生的事情。
增加该列的统计目标可能会有所不同;您必须尝试一下。
如果一切都如你所描述的那样,那么估值的大幅上涨就很神秘了。有了这
EXPLAIN (ANALYZE, BUFFERS, SETTINGS)
两种情况的结果,我们也许可以告诉你更多信息。