PostgreSql not using index

Question

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

How many rows has the table? Using an index and then reading data from other columns can be useless with small table or even with big table with small cardinality. — user_0
– user_0, Commented Feb 3, 2022 at 7:50
It has 187 rows, and the total size of table is 88 MB as data column has large amount of data as it has images stored in it — Ajay Yadav
– Ajay Yadav, Commented Feb 3, 2022 at 8:04
The output of explain(analyze, BUFFERS, TIMING) would interesting to see (especially if you can do `set track_io_timing=on;' before that). 200ms to scan such a tiny table seems quite slow - but if the values are large, that might be an explanation. — user330315
– user330315, Commented Feb 3, 2022 at 8:17
Try to execute your query with set enable_seqscan=off; this will force Postgres to use the index if it can. If it the does use the index, that means the statistics suggest to the optimizer that a seqscan may be faster. try vacuum analyse to regenerate the statistics. — Guillaume F.
– Guillaume F., Commented Feb 3, 2022 at 8:57
Just want to add one thing I had resolved this issue once by dropping the index and recreating it. — Ajay Yadav
– Ajay Yadav, Commented Feb 3, 2022 at 10:06

Laurenz Albe · Accepted Answer · 2022-02-08 09:19:00Z

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.

edited Feb 8, 2022 at 9:19

answered Feb 3, 2022 at 13:31

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

3 Comments

Ajay Yadav Over a year ago

Is the above select query most efficient, or can it be improved to even more faster query if i only want to select 2 fields from data column(jsonb)?

Ajay Yadav Over a year ago

Its still not using index, i have executed vacuum analyze.

Laurenz Albe Over a year ago

I was talking about VACUUM (FULL). The execution plan is good, what is bad is the long time ut takes to read 187 rows from the table. There is no advantage in using an index here.

Collectives™ on Stack Overflow

PostgreSql not using index

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related