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;