PostgreSQL Bugs

Collected from the PG bugs email list.

Bug ID15861
PG Version11.3
OSubuntu 18.04
Opened2019-06-20 01:49:58+00
Reported byRJ Ewing
StatusNew

Body of first available message related to this bug follows.

The following bug has been logged on the website:

Bug reference:      15861
Logged by:          RJ Ewing
Email address:      (redacted)
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

Messages

DateAuthorSubject
2019-06-20 01:49:58+00PG Bug reporting formBUG #15861: jsonb exists query retuning inconsistent results
2019-06-20 06:56:51+00Sergei KornilovRe: BUG #15861: jsonb exists query retuning inconsistent results
2019-06-20 13:48:17+00RJ EwingRe: BUG #15861: jsonb exists query retuning inconsistent results
2019-06-20 13:53:48+00Sergei KornilovRe: BUG #15861: jsonb exists query retuning inconsistent results