A question on GIN indexes and arrays

From: Wells Oliver <wells(dot)oliver(at)gmail(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: A question on GIN indexes and arrays
Date: 2017-08-20 20:28:38
Message-ID: CAOC+FBXQBjwg0s4d_mz33fb8TpKFyKr-+pOu9YoBbktbToZVag@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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(dot)oliver(at)gmail(dot)com <wellsoliver(at)gmail(dot)com>

Responses

Browse pgsql-general by date

  From Date Subject
Next Message marcelo 2017-08-20 21:54:05 Selecting some schema not suported for libpq PQconnectdbParams
Previous Message Alan Millington 2017-08-20 16:50:18 plpython2.dll missing from Enterprise DB Postgres distribution