Postgres ORDER BY indexed column increases performance of the query

Question

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.

it is very likely that the ORDER BY clause requires some extra operation and maybe be necessary to change the work_mem parameter, but just to be sure, please share the explain analyze output for both queries — Anthony Sotolongo
– Anthony Sotolongo, Commented Apr 18, 2022 at 15:25
Is this repeatable? If so, I would say your table is extremely bloated, but your index is not. — jjanes
– jjanes, Commented Apr 18, 2022 at 16:04
The first plan has 0 rows: (actual time=216.013..216.014 rows=0 loops=1) it is ok? — Anthony Sotolongo
– Anthony Sotolongo, Commented Apr 18, 2022 at 17:28

Laurenz Albe · Accepted Answer · 2022-04-19 08:42:43Z

1

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;

answered Apr 19, 2022 at 8:42

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres ORDER BY indexed column increases performance of the query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related