迁移到新版本时的Postgres性能和查询计划差异

Question

我们正在将数据库从Postgres 9.6升级到13.4。在升级期间，我们观察到在新的13.4 postgres实例上查询性能要慢得多。

在一个新实例上性能会变慢是有道理的——然而，我们看到查询是用明显不同的查询计划执行的(DB模式和索引当然是相同的)。

我们试图理解不同查询计划的原因。例如，我们有一个查询在新的13.4版本上运行速度慢了几个数量级(例如，26s vs 200ms)。

在"版本升级"中是否有标准步骤?我们遗漏的迁移过程，这将有助于解释较差的性能和截然不同的查询计划?

编辑:示例查询和不同的查询计划在Postgres 9.6和13.4

查询

EXPLAIN ANALYZE
SELECT
*
FROM
"my_table"
WHERE
"foo" = TRUE
AND "bar" = FALSE
AND("baz" > '2021-09-16 18:58:42.311+00'
OR("baz" = '2021-09-16 18:58:42.311+00'
AND "id" > 81353542))
ORDER BY
"baz" ASC,
"id" ASC
LIMIT 1;

Postgres 9.6 Query Plan:

Limit  (cost=548886.22..548886.22 rows=1 width=152) (actual time=1232.777..1232.778 rows=1 loops=1)
->  Sort  (cost=548886.22..549884.61 rows=1996789 width=152) (actual time=1232.776..1232.776 rows=1 loops=1)
Sort Key: baz, id
Sort Method: top-N heapsort  Memory: 25kB
->  Bitmap Heap Scan on my_table  (cost=30571.88..546889.43 rows=1996789 width=152) (actual time=531.993..1014.384 rows=1914762 loops=1)
Recheck Cond: ((foo AND (NOT bar) AND (baz > '2021-09-16 18:58:42.311+00'::timestamp with time zone)) OR (foo AND (NOT bar) AND (baz = '2021-09-16 18:58:42.311+00'::timestamp with time zone) AND (id > 81353542)))"
Filter: (foo AND (NOT bar))
Heap Blocks: exact=83762
->  BitmapOr  (cost=30571.88..30571.88 rows=1996789 width=0) (actual time=512.609..512.609 rows=0 loops=1)
->  Bitmap Index Scan on my_table_foo_bar_ts_id  (cost=0.00..30370.08 rows=1996789 width=0) (actual time=512.588..512.588 rows=2704908 loops=1)
Index Cond: ((foo = true) AND (bar = false) AND (baz > '2021-09-16 18:58:42.311+00'::timestamp with time zone))"
->  Bitmap Index Scan on my_table_foo_bar_ts_id  (cost=0.00..2.12 rows=1 width=0) (actual time=0.020..0.020 rows=0 loops=1)
Index Cond: ((foo = true) AND (bar = false) AND (baz = '2021-09-16 18:58:42.311+00'::timestamp with time zone) AND (id > 21153592))"
Planning time: 0.181 ms
Execution time: 1233.303 ms

Postgres 13.4 Query Plan:

Limit  (cost=0.11..0.64 rows=1 width=152) (actual time=32871.675..32871.677 rows=1 loops=1)
->  Index Scan using my_table_foo_bar_ts_id on my_table  (cost=0.11..1040476.82 rows=1962741 width=152) (actual time=32871.673..32871.674 rows=1 loops=1)
Index Cond: ((foo = true) AND (bar = false))
Filter: ((baz > '2021-09-16 18:58:42.311+00'::timestamp with time zone) OR ((baz = '2021-09-16 18:58:42.311+00'::timestamp with time zone) AND (id > 81353542)))"
Rows Removed by Filter: 18745951
Planning Time: 0.182 ms
Execution Time: 32871.705 ms

Answer 1

这是我的想法。

postgresql没有使用位图索引扫描，因为它高估了它认为会返回的记录数量，并决定进行"组合索引扫描"。相反,刚读表一次。

所以我建议做"分析",以确保您的数据是最新的也可能你的索引损坏了，所以试着重新索引再试一次

vaccume analyze;
analyze [table];
reindex;