Thread: [GENERAL] A question on GIN indexes and arrays
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[];
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
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:
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.oliver@gmail.com
wells.oliver@gmail.com
On Sun, Aug 20, 2017 at 1:28 PM, Wells Oliver <wells.oliver@gmail.com> wrote:
Why is this happening and what can I do to get my GIN indexes working? Thanks!
What extensions do you have installed in each database? I bet one of them (like intarray) redefines @> for one of your databases.
Try fully qualifying the operator. OPERATOR(pg_catalog.@>)
Cheers,
Jeff
YES!
\dx yields intarray was installed (back in the 9.1 days, maybe) and that was clobbering the @> operator.
Looking into the implications of removing intarray now that we're at 9.6, seems harmless, but will do my due diligence.
Thanks Jeff.
On Sun, Aug 20, 2017 at 3:15 PM, Jeff Janes <jeff.janes@gmail.com> wrote:
On Sun, Aug 20, 2017 at 1:28 PM, Wells Oliver <wells.oliver@gmail.com> wrote:Why is this happening and what can I do to get my GIN indexes working? Thanks!What extensions do you have installed in each database? I bet one of them (like intarray) redefines @> for one of your databases.Try fully qualifying the operator. OPERATOR(pg_catalog.@>)Cheers,Jeff
Wells Oliver
wells.oliver@gmail.com
wells.oliver@gmail.com
Sorry, as a final follow up here, another option (should anyone run into this and want to keep the intarray extension) is to create the index using the gin__int_ops operator:
CREATE INDEX ON sets USING GIN(obj_id gin__int_ops);
On Sun, Aug 20, 2017 at 4:22 PM, Wells Oliver <wells.oliver@gmail.com> wrote:
YES!\dx yields intarray was installed (back in the 9.1 days, maybe) and that was clobbering the @> operator.Looking into the implications of removing intarray now that we're at 9.6, seems harmless, but will do my due diligence.Thanks Jeff.--On Sun, Aug 20, 2017 at 3:15 PM, Jeff Janes <jeff.janes@gmail.com> wrote:On Sun, Aug 20, 2017 at 1:28 PM, Wells Oliver <wells.oliver@gmail.com> wrote:Why is this happening and what can I do to get my GIN indexes working? Thanks!What extensions do you have installed in each database? I bet one of them (like intarray) redefines @> for one of your databases.Try fully qualifying the operator. OPERATOR(pg_catalog.@>)Cheers,JeffWells Oliver
wells.oliver@gmail.com
Wells Oliver
wells.oliver@gmail.com
wells.oliver@gmail.com