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

The following bug has been logged on the website:

Bug reference:      13874
Logged by:          Hyoungwook Kim
Email address:      hukim99@gmail.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.

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

Michael Paquier <michael.paquier@gmail.com> writes:
> On Mon, Jan 18, 2016 at 9:39 PM,  <hukim99@gmail.com> wrote:
>> 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.

Yes, after an ANALYZE you'd get the same plan either way.  The difference
in behavior here is because you get sort of a half-baked ANALYZE
(specifically, an update in the table's recorded tuple count) at the time
of CREATE INDEX.  I don't think this is a bug; and I note that for this
size of table, it really hardly matters which plan the planner chooses.

            regards, tom lane