Postgres ORDER BY索引列提高查询性能

Question

我有一个表notification，有2列:id(主键)和entity(jsonb)

SELECT * 
FROM notification 
WHERE entity -> 'name' = 'Hello World' 
ORDER by id DESC

比没有ORDER BY:

的相同查询执行得快得多。

SELECT * 
FROM notification 
WHERE entity -> 'name' = 'Hello World'

没有实体->"名字"指数。

我注意到，第一个查询使用索引扫描，而第二个查询使用序列扫描。执行时间差异:60秒vs 0.5秒。

• 表行数:16696
• 返回结果:95行

怎么解释?

乌利希期刊指南。解释(ANALYZE, BUFFERS)第一个查询:

Index Scan using notification_pkey on notification  (cost=0.41..233277.12 rows=1 width=264) (actual time=480.582..583.623 rows=95 loops=1)
Filter: ((entity ->> 'name'::text) = 'Hello World'::text)
Rows Removed by Filter: 16606
Buffers: shared hit=96807
Planning Time: 0.211 ms
Execution Time: 583.826 ms

第二个查询:

Seq Scan on notification  (cost=0.00..502145.78 rows=1 width=264) (actual time=49675.453..60160.280 rows=95 loops=1)
Filter: ((entity ->> 'name'::text) = 'Hello World'::text)
Rows Removed by Filter: 16606
Buffers: shared hit=91608 read=497908
I/O Timings: read=55311.842
Planning Time: 0.112 ms
Execution Time: 60160.309 ms

乌利希期刊指南2。我在表上执行了VACUUM ANALYZE，但是它并没有提高性能。

Answer 1

似乎您的表膨胀到包含近500000个空的8kB块的程度。由于在索引扫描期间不会读取这些数据，因此索引扫描实际上比顺序扫描要快。

您应该找到并修复导致数据膨胀的问题，然后花时间用

重新组织表。

VACUUM (FULL) notification;