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: | Whole Thread | Raw Message | 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.
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"? |