Thread: BUG #15861: jsonb exists query retuning inconsistent results

BUG #15861: jsonb exists query retuning inconsistent results

From
PG Bug reporting form
Date:
The following bug has been logged on the website:

Bug reference:      15861
Logged by:          RJ Ewing
Email address:      rj@rjewing.com
PostgreSQL version: 11.3
Operating system:   ubuntu 18.04
Description:

When running the following query:

select count(*) from network_1.sample_photo where data ? 'photoID';

I am receiving inconsistent result that are different every time I run the
query. Somewhere in the 16k range. However I know for a fact that every row
in the table has a 'photoID' key. There are 103k rows.


The following query works correctly every time:

select count(*) from network_1.sample_photo where not data ?
data->>'photoID' is not null;



When running explain, the later is performing a seq scan, where as the
former has the following plan:

 Aggregate  (cost=409.55..409.56 rows=1 width=8) (actual time=44.498..44.499
rows=1 loops=1)
   ->  Bitmap Heap Scan on sample_photo  (cost=20.80..409.29 rows=103
width=0) (actual time=7.008..35.916 rows=16431 loops=1)
         Recheck Cond: (data ? 'photoID'::text)
         Heap Blocks: exact=9932
         ->  Bitmap Index Scan on idx_network_1_sample_photo_data
(cost=0.00..20.77 rows=103 width=0) (actual time=5.273..5.274 rows=16431
loops=1)
               Index Cond: (data ? 'photoID'::text)

The index "idx_network_1_sample_photo_data" is a gin index.


I am not able to reproduce this on my local machine. This problem happened
in version 10, and I just upgraded to 11 without any improvement. I tried a
vaccum full analyze & to rebuild the table indexes.

Is this a bug? Or am I missing something?

Thanks in advance


Re: BUG #15861: jsonb exists query retuning inconsistent results

From
Sergei Kornilov
Date:
Hello

Please check your gin_fuzzy_search_limit setting: https://www.postgresql.org/docs/current/gin-tips.html
Seems this setting is in effect.

regards, Sergei



Re: BUG #15861: jsonb exists query retuning inconsistentresults

From
RJ Ewing
Date:
Ya, that is most likely the issue. I have the gin_fuzzy_search_limit set to 15k.

Is there a way around this? Having a search limit of 15k seems reasonable to me when doing a FTS, however I would not want that limit when doing an exists query. Any way to tell the planner to not use the index? Or other alternative?

Thanks

RJ Ewing
On Jun 19, 2019, 11:56 PM -0700, Sergei Kornilov <sk@zsrv.org>, wrote:
Hello

Please check your gin_fuzzy_search_limit setting: https://www.postgresql.org/docs/current/gin-tips.html
Seems this setting is in effect.

regards, Sergei

Re: BUG #15861: jsonb exists query retuning inconsistent results

From
Sergei Kornilov
Date:
Hi

gin_fuzzy_search_limit is just PGC_USERSET setting. You can change it anytime with SET command, for example for
concretetransaction:
 

begin;
set local gin_fuzzy_search_limit to 0; -- zero is default value
/* some queries */
commit;

regards, Sergei