Oleg (obartunov) wrote,
Oleg
obartunov

Fixed ! Bug in indexing scalars with jsquery

Nikita Glukhov recently identified and fixed a bug in jsquery (https://github.com/postgrespro/jsquery). Indexing of scalars was broken, please upgrade jsquery !

The bug could be demonstrated in this example (query returns different results for seqscan and when using index):


create table test(js jsonb);
insert into test values ('1');
select count(*) from test where js @@ '$ = 1';
 count
-------
     1
(1 row)

create index test_path_value_idx on test using gin(js jsonb_path_value_ops);
CREATE INDEX
set enable_seqscan = off;
SET
select count(*) from test where js @@ '$ = 1';
 count
-------
     0
(1 row)

explain select count(*) from test where js @@ '$ = 1';
                                       QUERY PLAN
----------------------------------------------------------------------------------------
 Aggregate  (cost=12.02..12.03 rows=1 width=8)
   ->  Bitmap Heap Scan on test  (cost=8.00..12.01 rows=1 width=0)
         Recheck Cond: (js @@ '$ = 1'::jsquery)
         ->  Bitmap Index Scan on test_path_value_idx  (cost=0.00..8.00 rows=1 width=0)
               Index Cond: (js @@ '$ = 1'::jsquery)
(5 rows)
Tags: jsonb, jsquery, pgen
Subscribe

  • What is the size of jsonb ?

    It's interesting to know how big is your jsonb and it's not trivial as it looks, since jsonb is a binary format and also may be TOASTed (compressed).…

  • Partial TOAST decompression for jsonb

    Inspired by commit support for partial TOAST decompression "When asked for a slice of a TOAST entry, decompress enough to return the slice instead…

  • Bloom index for bigint

    Bloom index by default works for int4 and text, but other types with hash function and equality operator could be supported. Just use opclass…

  • Post a new comment

    Error

    Anonymous comments are disabled in this journal

    default userpic

    Your IP address will be recorded 

  • 0 comments