Postgres ORDER BY indexed column increases performance of the query-CodePudding

I have a table notification with 2 columns: id (primary key) and entity (jsonb)

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

Executes much faster, than the same query without the ORDER BY:

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

There is no entity -> 'name' index.

I've noticed, that the first query uses index scan, while the second one - a sequence scan. The difference in execution time: 60 sec vs 0.5 sec.

Number of rows in the table: 16696
Returned result: 95 rows

How to explain it?

UPD. EXPLAIN (ANALYZE, BUFFERS) for the first query:

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

For the second query:

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

UPD 2. I executed VACUUM ANALYZE on the table, but it didn't improve the performance.

CodePudding user response：

It seems that your table is bloated to the extent that it contains almost 500000 empty 8kB blocks. Since those are not read during an index scan, the index scan is actually faster than the sequential scan.

You should find and fix the problem that causes that bloat, then take the down time to reorganize the table with

VACUUM (FULL) notification;