我们遇到了一个奇怪的问题,Postgres,最初是在我们准备从10.21升级到11+时发现的.在较新版本的Postgres中,我们最初认为的是性能回归,实际上,一旦为我们的数据库生成了更准确的统计数据,性能回归就变成了性能回归.结果是,对于有问题的表,last_analyze
和last_autoanalyze
都是NULL
.升级纠正了这一点,但使查询的某个子集变得慢得多.
背景
我们目前正在生产中运行PG 10.21.我们在这里与之交互的表events_test
包括我们正在测试的两列account
(varchar(40)
)和timestamp
(timestamptz
).在account
,timestamp
有指数,在account
,and,timestamp
有一个综合指数.
我们的测试查询是
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM events_test
WHERE account = '1234' AND TIMESTAMP >= (NOW() AT TIME ZONE 'EST' - interval '1 month');
我们正在对两个不同的account
值进行测试--有些我们希望返回大量数据(帐户1234
返回数万行),另一些返回少量数据(帐户5678
返回数十行).
在我们当前的生产数据库上,这对于我们的目的来说运行得足够快,对于大响应(帐户1234
)大约运行80-1234
ms,对于小响应(帐户5678
)大约运行1ms.在这两种情况下,查询规划器 Select 从对复合索引events_test_account_timestamp_idx
的索引扫描开始.
过期的统计信息
对于返回大量行的帐户,查询规划器大大低估了要返回的行数-它估计大约500行,但实际上大约是60,000行.这是因为我们的生产数据库上的表统计数据已过期.
手动运行ANALYZE events_test;
或升级到PG11、PG12或PG14(这似乎会导致在升级后不久运行此重新分析)更新表统计信息,并使行估计值更接近真实值-对于返回更多数据的帐户,新的估计值约为20,000行.
查询规划器
不幸的是,这种更准确的估计似乎会导致查询规划器运行效率低得多的查询.对于数据很少的帐户,行估计值不变,规划者继续使用综合指数.然而,对于返回数据量较大的帐户,规划者现在 Select 对events_test_timestamp_idx
进行索引扫描,然后对account
字段进行筛选,这会使查询的执行时间达到400-500ms.
预期返回少量行的查询将继续在所有版本的Postgres上使用复合索引.
我们已经try 了什么
我们一直在寻找关于Postgres绕过综合指数的类似问题,但我们没有发现任何有用的东西.另外:
分析、重新编制索引、统计目标
我们已经try 将default_statistics_target
从default_statistics_target
更新为default_statistics_target
0(也短暂地try 了10和default_statistics_target
00),然后重新分析我们的表并重新建立索引,并且较慢的查询计划没有改变.
Postgres版本
这个问题最初是在我们针对较新版本的Postgres进行验证时发现的.我们在升级到PG11、PG12和PG14后进行了测试,在所有情况下都显示出性能较慢的查询规划.我们也可以通过重新分析来更新我们的统计数据,从而导致它在PG10上发生,所以这似乎不是Postgres更新中规划者行为的变化.
力综合指数
我们已经验证了,我们确实可以通过以下测试命令(暂时)删除规划者现在首选的timestamp
索引来让forcePostgres使用综合指数:
BEGIN;
DROP INDEX events_test_timestamp_idx;
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM events_test
WHERE account = '1234' AND TIMESTAMP >= (NOW() AT TIME ZONE 'EST' - interval '1 month');
ROLLBACK;
这会导致Postgres恢复到综合指数events_test_account_timestamp_idx
,但是它现在 Select 在该索引上执行bitmap索引扫描.这将继续以低于events_test_account_timestamp_idx
ms的速度运行,在性能上可与之前对综合指数的直接指数扫描相媲美.
Query EXPLAIN
s
以下是在生产环境中,在PG10上更高效地运行该查询时的样子:
Index Scan using events_test_account_timestamp_idx on events_test (cost=0.11..1109.11 rows=579 width=206) (actual time=0.062..116.439 rows=71700 loops=1)
Index Cond: (((account)::text = '1234'::text) AND ("timestamp" >= (timezone('EST'::text, now()) - '1 mon'::interval)))
Buffers: shared hit=68619 read=1249
I/O Timings: read=23.426
Planning time: 0.662 ms
Execution time: 119.339 ms
下面是在PG14上更新统计数据后的结果(但所有版本的输出都是相似的):
Index Scan using events_test_timestamp_idx on events_test (cost=0.11..31867.79 rows=18994 width=204) (actual time=0.164..311.960 rows=55583 loops=1)
Index Cond: ("timestamp" >= ((now() AT TIME ZONE 'EST'::text) - '1 mon'::interval))
Filter: ((account)::text = '1234'::text)
Rows Removed by Filter: 462327
Buffers: shared hit=492867
Planning:
Buffers: shared hit=144 read=30
I/O Timings: read=6.160
Planning Time: 7.021 ms
Execution Time: 314.676 ms
最后,如果我们通过临时删除时间戳索引来强制它使用综合索引,则在PG14上(但同样,在所有版本上都类似):
Bitmap Heap Scan on events_test (cost=187.05..35867.12 rows=18992 width=204) (actual time=11.373..38.937 rows=55582 loops=1)
Recheck Cond: (((account)::text = '1234'::text) AND ("timestamp" >= ((now() AT TIME ZONE 'EST'::text) - '1 mon'::interval)))
Heap Blocks: exact=13586
Buffers: shared hit=13803
-> Bitmap Index Scan on events_test_account_timestamp_idx (cost=0.00..186.10 rows=18992 width=0) (actual time=9.376..9.376 rows=55582 loops=1)
Index Cond: (((account)::text = '1234'::text) AND ("timestamp" >= ((now() AT TIME ZONE 'EST'::text) - '1 mon'::interval)))
Buffers: shared hit=217
Planning:
Buffers: shared hit=5
Planning Time: 0.316 ms
Execution Time: 41.446 ms
这里的核心问题似乎是,在准确估计的情况下,Postgresexpects对单个指数进行筛选(31867.79)要比使用综合指数(35867.12)快,而实际上在上面的情况下,综合指数要快近10倍.这很有趣,因为上面的最后两个EXPLAIN
人使用了相同的一组统计数据来做出这些预测.