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

On Mon, Jan 18, 2016 at 9:39 PM, <hukim99(at)gmail(dot)com> wrote:
> 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?

My guess is that there are invalid statistics on this table. If you
run ANALYZE on json_test you should be able to see a sequential scan
in the first case as well. It is weird that the planner chooses a
bitmap scan in the first case for one tuple.
--
Michael