query not using gin index

[bwalsh]

Thank you for a great product. I am having problems where jsquery is not using the gin index.

Given this table and index

 \d vertex
                    Table "public.vertex"
 Column |       Type        | Collation | Nullable | Default
--------+-------------------+-----------+----------+---------
 gid    | character varying |           | not null |
 label  | character varying |           | not null |
 data   | jsonb             |           |          |
Indexes:
    "vertex_data" gin (data)
    "vertex_gid" btree (gid)
    "vertex_label" btree (label)

This standard jsonb query uses index

 explain  select gid, data->'start' as "start", data->'chromosome'  from vertex where label = 'Allele' and data @> '{"start": 35305275}' or data @> '{"start": 35305277}'  ;
                                                             QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on vertex  (cost=225.56..36911.68 rows=9623 width=106)
   Recheck Cond: ((data @> '{"start": 35305275}'::jsonb) OR (data @> '{"start": 35305277}'::jsonb))
   Filter: ((((label)::text = 'Allele'::text) AND (data @> '{"start": 35305275}'::jsonb)) OR (data @> '{"start": 35305277}'::jsonb))
   ->  BitmapOr  (cost=225.56..225.56 rows=11300 width=0)
         ->  Bitmap Index Scan on vertex_data  (cost=0.00..110.38 rows=5650 width=0)
               Index Cond: (data @> '{"start": 35305275}'::jsonb)
         ->  Bitmap Index Scan on vertex_data  (cost=0.00..110.38 rows=5650 width=0)
               Index Cond: (data @> '{"start": 35305277}'::jsonb)
(8 rows)

This query does not use the index.

explain select gid, data->'start' as "start", data->'chromosome'  from vertex where label = 'Allele' and data @@ 'start = 35305275 OR start = 35305277'  ;
                                                    QUERY PLAN
------------------------------------------------------------------------------------------------------------------
 Seq Scan on vertex  (cost=0.00..293886.85 rows=3977 width=106)
   Filter: ((data @@ '("start" = 35305275 OR "start" = 35305277)'::jsquery) AND ((label)::text = 'Allele'::text))
(2 rows)

Adding hints does not help

 explain select gid, data->'start' as "start", data->'chromosome'  from vertex where label = 'Allele' and data @@ 'start /*-- index */ = 35305275 OR start /*-- index */ = 35305277'  ;
                                                                   QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on vertex  (cost=0.00..293886.85 rows=3977 width=106)
   Filter: ((data @@ '("start" /*-- index */  = 35305275 OR "start" /*-- index */  = 35305277)'::jsquery) AND ((label)::text = 'Allele'::text))
(2 rows)

I'd really like to use jsquery for queries on the form

explain select gid, data->'start' as "start", data->'chromosome'  from vertex where label = 'Allele' and data @@ 'start ($ > 35305274 AND $ < 35305278) ';
                                                  QUERY PLAN
--------------------------------------------------------------------------------------------------------------
 Seq Scan on vertex  (cost=0.00..293886.85 rows=3977 width=106)
   Filter: ((data @@ '"start"($ > 35305275 AND $ < 35305277)'::jsquery) AND ((label)::text = 'Allele'::text))
(2 rows)

[akorotkov]

Hi!

jquery doesn't work with default GIN opclass for jsonb. You have to specify jsquery opclass, for instance:

CREATE INDEX vertex_data_jquery_idx ON vertex USING gin (data jsonb_path_value_ops);

[bwalsh]

perfect. thank you very much for the quick response.

# explain select gid, data->'start' as "start", data->'chromosome'  from vertex where label = 'Allele' and data @@ 'start ($ > 35305274 AND $ < 35305278) ';
                                   QUERY PLAN
---------------------------------------------------------------------------------
 Bitmap Heap Scan on vertex  (cost=95.75..19929.65 rows=4011 width=106)
   Recheck Cond: (data @@ '"start"($ > 35305274 AND $ < 35305278)'::jsquery)
   Filter: ((label)::text = 'Allele'::text)
   ->  Bitmap Index Scan on vertex_data  (cost=0.00..94.74 rows=5699 width=0)
         Index Cond: (data @@ '"start"($ > 35305274 AND $ < 35305278)'::jsquery)
(5 rows)

Time: 0.689 ms
#  select gid, data->'start' as "start", data->'chromosome'  from vertex where label = 'Allele' and data @@ 'start ($ > 35305274 AND $ < 35305278) ';
                       gid                       |  start   | ?column?
-------------------------------------------------+----------+----------
 Allele:000010b2803f336a076a45db8cc4ccc34a587680 | 35305277 | "10"
 Allele:41f2a16500d995b488a31e4a32f97308ef794149 | 35305277 | "10"
 Allele:b0b64df19182d3fe63d9edc3856f97446126f353 | 35305275 | "10"
(3 rows)

Time: 0.933 ms