sql - 为什么 pg 查询会在一段时间后停止使用索引？

Question

我在 Postgres 12.0 中有这个查询:

SELECT "articles"."id"
FROM "articles"
WHERE ((jsonfields ->> 'etat') = '0' OR (jsonfields ->> 'etat') = '1' OR (jsonfields ->> 'etat') = '2')
ORDER BY ordre ASC;

此时:

    QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort  (cost=1274.09..1274.97 rows=354 width=8) (actual time=13.000..13.608 rows=10435 loops=1)
Sort Key: ordre
Sort Method: quicksort  Memory: 874kB
->  Bitmap Heap Scan on articles  (cost=15.81..1259.10 rows=354 width=8) (actual time=1.957..10.807 rows=10435 loops=1)
Recheck Cond: (((jsonfields ->> 'etat'::text) = '1'::text) OR ((jsonfields ->> 'etat'::text) = '2'::text) OR ((jsonfields ->> 'etat'::text) = '0'::text))
Heap Blocks: exact=6839
->  BitmapOr  (cost=15.81..15.81 rows=356 width=0) (actual time=1.171..1.171 rows=0 loops=1)
->  Bitmap Index Scan on myidx  (cost=0.00..5.18 rows=119 width=0) (actual time=0.226..0.227 rows=2110 loops=1)
Index Cond: ((jsonfields ->> 'etat'::text) = '1'::text)
->  Bitmap Index Scan on myidx  (cost=0.00..5.18 rows=119 width=0) (actual time=0.045..0.045 rows=259 loops=1)
Index Cond: ((jsonfields ->> 'etat'::text) = '2'::text)
->  Bitmap Index Scan on myidx  (cost=0.00..5.18 rows=119 width=0) (actual time=0.899..0.899 rows=8066 loops=1)
Index Cond: ((jsonfields ->> 'etat'::text) = '0'::text)
Planning Time: 0.382 ms
Execution Time: 14.234 ms
(15 lignes)

一段时间后:

    QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------
Sort  (cost=7044.04..7079.35 rows=14127 width=8) (actual time=613.445..614.679 rows=15442 loops=1)
Sort Key: ordre
Sort Method: quicksort  Memory: 1108kB
->  Seq Scan on articles  (cost=0.00..6070.25 rows=14127 width=8) (actual time=0.060..609.477 rows=15442 loops=1)
Filter: (((jsonfields ->> 'etat'::text) = '1'::text) OR ((jsonfields ->> 'etat'::text) = '2'::text) OR ((jsonfields ->> 'etat'::text) = '3'::text))
Rows Removed by Filter: 8288
Planning Time: 0.173 ms
Execution Time: 615.744 ms
(8 lignes)

我需要重新创建索引:

DROP INDEX myidx;
CREATE INDEX myidx ON articles ( (jsonfields->>'etat') );

为什么？如何解决这个问题？

我试图减少内存以禁用 seqscan。它不起作用。
我尝试执行 select pg_stat_reset();。它不起作用。

Answer 1

pg_stat_reset()不重置表统计信息。它只会重置计数器(比如索引的使用频率)，它对查询计划没有影响。

要更新表统计信息，请使用 ANALYZE(或 VACUUM ANALYZE，同时使用它)。 autovacuum 应该通常会自动处理这个问题。

您的第一个查询找到 rows=10435，第二个查询找到 rows=15442。 Postgres 期望在第一个中找到 rows=354 (!)，但在第二个中找到 rows=14127。它在很大程度上低估了第一个结果行的数量，这有利于索引。所以您的第一个查询只是偶然很快。

表统计发生变化，可能出现表和索引膨胀。最重要的是，您的成本设置可能具有误导性。考虑 random_page_cost 的较低设置(可能还有 cpu_index_tuple_cost 和其他人)。

相关:

• Keep PostgreSQL from sometimes choosing a bad query plan

如果重新创建索引导致不同的查询计划，则索引可能已经膨胀。 (膨胀的索引也会阻止 Postgres 使用它。)更积极的 autovacuum 设置，通常或仅针对表，甚至仅针对索引可能会有所帮助。

此外，表达式索引引入了额外的统计信息(在您的情况下，jsonfields->>'etat' 上的基本统计信息)。删除索引也会删除那些。新的表达式索引以空统计信息开始，这些统计信息由下一个手动 ANALYZE 或 autovacuum 填充。因此，通常情况下，您应该在创建表达式索引后在表上运行 ANALYZE - 除了在您的情况下，您目前似乎仅在基于误导性统计信息时才获得快速查询，因此请先解决该问题。

也许重新审视您的数据库设计。 etat 值真的必须嵌套在 JSON 列中吗？将其作为单独的列总体来说可能便宜很多。

尽管如此，您的第一个(快速)查询计划中最昂贵的部分是 Bitmap Heap Scan，其中 Postgres 读取实际数据页以返回 id值。自 Postgres 11 以来，使用“覆盖”索引的快捷方式是可能的:

CREATE INDEX myidx ON articles ((jsonfields->>'etat')) INCLUDE (ordre, id);

但这更依赖于 autovacuum 及时完成工作，因为它要求可见性 map 是最新的。

或者，如果您的 WHERE 子句是常量(始终过滤 (jsonfields ->> 'etat') = ANY ('{0,1, 2}'))，部分索引将至高无上:

CREATE INDEX myidx ON articles (ordre, id)
WHERE (jsonfields ->> 'etat') = ANY ('{0,1,2}');