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@gmail.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