3

Two of a PostgreSQL DB table's columns are JSONB field (j1, j2). My queries are on (j1->>'f1')::int and (j2->>'f2'), for example:

SELECT id from table where (j1->>'f1')::int = 1 AND (j2->>'f2') IS NULL

Since I have millions of records, and queries started to take long minutes, I created a composite index:

CREATE INDEX "f1_f2" ON table (((j1->>'f1')::int), (j2->>'f2'));

However, this didn't really help. Looking at EXPLAIN, I see that Seq Scan is still being used. Is this because PG does not handle composite JSONB fields? Any other reason that I should check?

As requested, I am attaching the complete execution plan. Thank you!

EXPLAIN (ANALYZE, buffers, format text) SELECT id FROM table WHERE (j1->>'f1')::int = 1 AND (j2->>'f2') IS NULL;

"Gather  (cost=1000.00..1551479.43 rows=29850 width=19) (actual time=0.617..354008.670 rows=2949996 loops=1)"
"  Workers Planned: 2"
"  Workers Launched: 2"
"  Buffers: shared hit=53872590 read=7841241 dirtied=668 written=15597"
"  ->  Parallel Seq Scan on table (cost=0.00..1547494.43 rows=12438 width=19) (actual time=8.702..353420.358 rows=983332 loops=3)"
"        Filter: (((j2->> 'f2'::text) IS NULL) AND (((j1 ->> 'f1'::text))::integer = 1))"
"        Rows Removed by Filter: 5821066"
"        Buffers: shared hit=53872590 read=7841241 dirtied=668 written=15597"
"Planning Time: 0.155 ms"
"Execution Time: 354200.845 ms"

I am also adding the SQL generated for the index using pgadmin's "CREATE script" function:

-- Index: f1_f2

-- DROP INDEX public.f1_f2;

CREATE INDEX f1_f2
    ON public.table USING btree
    (((j1->> 'f1'::text)::integer) ASC NULLS LAST, (j2 ->> 'f2'::text) COLLATE pg_catalog."default" ASC NULLS LAST)
    TABLESPACE pg_default;

After SET enable_seqscan=off, I got the following EXPLAIN. Note that in addition to the composite index, I also have an index on each field. I see that the plan used the single indexes but not the composite one.

"Gather  (cost=171490.09..1957279.19 rows=35622 width=19) (actual time=3346.873..132192.242 rows=3526839 loops=1)"
"  Workers Planned: 2"
"  Workers Launched: 2"
"  Buffers: shared hit=8524759 read=1998657 dirtied=331 written=7043"
"  ->  Parallel Bitmap Heap Scan on table (cost=170490.09..1952716.99 rows=14842 width=19) (actual time=3310.690..131586.964 rows=1175613 loops=3)"
"        Recheck Cond: (((j1->> 'f1'::text))::integer = 1)"
"        Rows Removed by Index Recheck: 1801933"
"        Filter: ((j2->> 'f2'::text) IS NULL)"
"        Rows Removed by Filter: 1211720"
"        Heap Blocks: exact=18098 lossy=349995"
"        Buffers: shared hit=8524759 read=1998657 dirtied=331 written=7043"
"        ->  Bitmap Index Scan on f1 (cost=0.00..170481.18 rows=7124349 width=0) (actual time=3335.250..3335.250 rows=7167075 loops=1)"
"              Index Cond: (((j1->> 'f1'::text))::integer = 1)"
"              Buffers: shared hit=4 read=20205 written=809"
"Planning Time: 0.160 ms"
"Execution Time: 132381.519 ms"
7
  • 1
    Do a set enable_seqscan=off first. If it still does a seqscan, then it thinks the index is not usable here. If it switches, then it just thinks a seqscan will be faster (e.g. because the query returns a large chunk of the table). – jjanes Aug 9 '20 at 13:01
  • 1
    The planner thinks you are returning a lot of rows (although not as many as you actually are!) and so thinks the sequential scan will be faster. Especially as seq scan is probably believed to get more benefit from parallel execution than an index scan. – jjanes Aug 9 '20 at 15:05
  • 1
    Does running vacuum analyze the_table change anything? – a_horse_with_no_name Aug 10 '20 at 5:41
  • 1
    If you set enable_seqscan to off and drop index f1, is your new index used? Does the situation improve if you increase work_mem substantially? – Laurenz Albe Aug 10 '20 at 11:05
  • 1
    The index f1 is probably quite a bit smaller than f1_f2, and the planner might think the reduced size makes up for the reduced specificity. Is there a reason to have both? You could make a partial index, moving (j2->>'f2') IS NULL from an index column to a WHERE clause on the index. Vacuuming the table might help. Also, increasing work_mem will make the bitmap scan actually faster, but probably won't do anything to convince the planner to use it over the seq scan. – jjanes Aug 10 '20 at 12:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.