Hey guys, I hope you can help. I am trying to use a GIN Index on an array column and, weirdly, on the exact same server (9.6.3) it uses the index on one database, and not on the other. Here's the test query I am using:
CREATE TEMPORARY TABLE objs (obj_id integer PRIMARY KEY); CREATE TEMPORARY TABLE sets (obj_id integer[], somecount smallint); INSERT INTO objs SELECT generate_series(0,1000000); 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; -- SELECT * FROM sets WHERE obj_id @> ARRAY[2,3]::integer[]; EXPLAIN ANALYZE SELECT * FROM sets WHERE obj_id @> ARRAY[2,3]::integer[]; On one database (same server!) I get the correct output: Bitmap Heap Scan on sets (cost=1026.14..2086.17 rows=276 width=34) (actual time=6.991..7.028 rows=202 loops=1) Recheck Cond: (obj_id @> '{2,3}'::integer[]) Heap Blocks: exact=3 -> Bitmap Index Scan on sets_obj_id_idx (cost=0.00..1026.07 rows=276 width=0) (actual time=6.974..6.974 rows=202 loops=1) Index Cond: (obj_id @> '{2,3}'::integer[]) Planning time: 11.076 ms Execution time: 7.084 ms But on another DB (same! server!) Seq Scan on sets (cost=10000000000.00..10002104164.70 rows=101000 width=34) (actual time=0.531..30015.853 rows=202 loops=1) Filter: (obj_id @> '{2,3}'::integer[]) Rows Removed by Filter: 100999697 Planning time: 0.206 ms Execution time: 30015.883 ms Why is this happening and what can I do to get my GIN indexes working? Thanks! -- Wells Oliver wells.oli...@gmail.com <wellsoli...@gmail.com>