Collected from the PG bugs email list.
|Reported by||RJ Ewing|
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
|2019-06-20 01:49:58+00||PG Bug reporting form||BUG #15861: jsonb exists query retuning inconsistent results|
|2019-06-20 06:56:51+00||Sergei Kornilov||Re: BUG #15861: jsonb exists query retuning inconsistent results|
|2019-06-20 13:48:17+00||RJ Ewing||Re: BUG #15861: jsonb exists query retuning inconsistent results|
|2019-06-20 13:53:48+00||Sergei Kornilov||Re: BUG #15861: jsonb exists query retuning inconsistent results|