postgres: why isn't this query using the GIN index on the array value?

Question

SQL:

CREATE TEMPORARY TABLE objs (obj_id integer);
CREATE TEMPORARY TABLE sets (obj_id integer[], somecount smallint);

INSERT INTO objs SELECT generate_series(0,10000);
INSERT INTO sets
    SELECT ARRAY[p1.obj_id, p2.obj_id,p3.obj_id], generate_series(0,100)
    FROM objs as p1
    CROSS JOIN objs AS p2
    CROSS JOIN objs AS p3
    WHERE p2.obj_id = p1.obj_id + 1 AND p3.obj_id = p2.obj_id + 1;

CREATE INDEX ON sets USING GIN(obj_id);
SET enable_seqscan = off;
EXPLAIN ANALYZE SELECT * FROM sets WHERE obj_id @> ARRAY[1,2]::integer[];

Yields:

                                                       QUERY PLAN                                                       
------------------------------------------------------------------------------------------------------------------------
 Seq Scan on sets  (cost=10000000000.00..10000021039.74 rows=25 width=34) (actual time=0.037..333.496 rows=202 loops=1)
   Filter: (obj_id @> '{1,2}'::integer[])
   Rows Removed by Filter: 1009697
 Planning time: 0.727 ms
 Execution time: 333.529 ms
(5 rows)

Why is this doing a sequence scan and not using the index?

UPDATE

Running this on one DB on my server uses the bitmap heap scan on the index (great!) and running it another does not (boo!) and I don't know why. Same server, different databases.

The postgres engine may be making a different decision based on the data. Is the execution time of the sequence scan significantly slower? Also, is it necessary to use an integer array? Why not have an additional table? Postgres is built to deal with tables and foreign keys moreso than arrays. — marksiemers
– marksiemers, Commented Aug 21, 2017 at 18:58

Wells · Accepted Answer · 2017-08-21 16:31:13Z

1

The postgres extension intarray was installed in the database that used the sequential scan, it was clobbering the @> operator. Three options:

changing the call to OPERATOR(pg_catalog.@>) uses in the GIN index.
Create the index using the gin__int_ops option: CREATE INDEX ON sets USING GIN(obj_id gin__int_ops);
remove the intarray extension (but I require it elsewhere, so nope)

edited Aug 21, 2017 at 16:31

answered Aug 20, 2017 at 23:27

Wells

11k16 gold badges58 silver badges93 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

postgres: why isn't this query using the GIN index on the array value?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related