From: | PG Bug reporting form <noreply(at)postgresql(dot)org> |
---|---|
To: | pgsql-bugs(at)lists(dot)postgresql(dot)org |
Cc: | rj(at)rjewing(dot)com |
Subject: | BUG #15861: jsonb exists query retuning inconsistent results |
Date: | 2019-06-20 01:49:58 |
Message-ID: | 15861-fed7a8c85fffa2d7@postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
The following bug has been logged on the website:
Bug reference: 15861
Logged by: RJ Ewing
Email address: rj(at)rjewing(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Paquier | 2019-06-20 02:49:41 | Re: BUG #15789: libpq compilation with OpenSSL 1.1.1b fails on Windows with Visual Studio 2017 |
Previous Message | David Marcin | 2019-06-20 01:07:32 | CREATE STATISTICS + Table Inheritance = ERROR: tuple already updated by self |