'PostgreSql not using index

I have a table named snapshots with a column named data in jsonb format An Index is created on snapshots table

create index on snapshots using(( data->>'creator' ));

The following query was using index initially but not after couple of days

SELECT id, data - 'sections' - 'sharing' AS data FROM snapshots WHERE data->>'creator' = '[email protected]' ORDER BY xmin::text::bigint DESC

below is the output by running explain analyze

Sort  (cost=19.10..19.19 rows=35 width=77) (actual time=292.159..292.163 rows=35 loops=1)
  Sort Key: (((xmin)::text)::bigint) DESC
  Sort Method: quicksort  Memory: 30kB
  ->  Seq Scan on snapshots  (cost=0.00..18.20 rows=35 width=77) (actual time=3.500..292.104 rows=35 loops=1)
        Filter: ((data ->> 'creator'::text) = '[email protected]'::text)
        Rows Removed by Filter: 152
Planning Time: 0.151 ms
Execution Time: 292.198 ms
postgresqlindexing


Solution 1:[1]

A table with 187 rows is very small. For very small tables, a sequential scan is the most efficient strategy.

What is surprising here is the long duration of the query execution (292 milliseconds!). Unless you have incredibly lame or overloaded storage, this must mean that the table is extremely bloated – it is comparatively large, but almost all pages are empty, with only 187 live rows. You should rewrite the table to compact it:

VACUUM (FULL) snapshots;

Then the query will become must faster.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1

Related Questions