BUG #13874: The index of a json field which is created after data are inserted doesn't work.

From: hukim99(at)gmail(dot)com
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #13874: The index of a json field which is created after data are inserted doesn't work.
Date: 2016-01-18 12:39:42
Message-ID: 20160118123942.2961.98513@wrigleys.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: 13874
Logged by: Hyoungwook Kim
Email address: hukim99(at)gmail(dot)com
PostgreSQL version: 9.4.5
Operating system: OS X 10.11.2
Description:

Hi team,

Please see the following results.

test=# CREATE TABLE json_test (id serial primary key, data jsonb);
CREATE TABLE
test=# CREATE INDEX idb_json_test_data ON json_test USING GIN (data);
CREATE INDEX
test=# INSERT INTO json_test (data) VALUES ('[ { "id": "id1" }, { "id":
"id3" } ]');
INSERT 0 1
test=# EXPLAIN SELECT * FROM json_test WHERE data @> '[{"id": "id2"}]';
QUERY PLAN

----------------------------------------------------------------------------------
Bitmap Heap Scan on json_test (cost=16.01..20.02 rows=1 width=36)
Recheck Cond: (data @> '[{"id": "id2"}]'::jsonb)
-> Bitmap Index Scan on idb_json_test_data (cost=0.00..16.01 rows=1
width=0)
Index Cond: (data @> '[{"id": "id2"}]'::jsonb)
(4 rows)

test=# DROP TABLE json_test;
DROP TABLE
test=# CREATE TABLE json_test (id serial primary key, data jsonb);
CREATE TABLE
test=# INSERT INTO json_test (data) VALUES ('[ { "id": "id1" }, { "id":
"id3" } ]');
INSERT 0 1
test=# CREATE INDEX idb_json_test_data ON json_test USING GIN (data);
CREATE INDEX
test=# EXPLAIN SELECT * FROM json_test WHERE data @> '[{"id": "id2"}]';
QUERY PLAN
----------------------------------------------------------
Seq Scan on json_test (cost=0.00..1.01 rows=1 width=36)
Filter: (data @> '[{"id": "id2"}]'::jsonb)
(2 rows)

The only difference between two instructions above is whether the index is
created before or after data insertion. Isn't it a bug?

Thanks.

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Andrew Gierth 2016-01-18 12:54:51 Re: BUG #13863: Select from views gives wrong results
Previous Message Tatsuo Ishii 2016-01-18 12:32:07 Re: BUG #13872: "represention"?