How does one query a postgres array using the gin index?

Question

I have a postgres array column with a gin index:

CREATE TABLE things (
    id integer NOT NULL,
    tags character varying(255)[]
);

CREATE INDEX index_things_on_tags ON things USING gin (tags);

There are a few ways to check for the presence of an element in the column, using various array operators. Here are the ones I've seen:

select * from things where 'blue' = ANY (tags)
select * from things where tags <@ '{"blue"}'
select * from things where '{"blue","yellow"}' && tags;

In postgres 9.3:

Will the first one use the gin index?
I'm pretty sure the second one will use the index. However, it is different from the first. it doesn't allow me to check if blue is one of the tags, it requires me to specify the exact array. Is there a way to make the style of syntax in 2 achieve what 1 is achieving?
In the third, I want any row that has any one of blue or yellow. Will this query use the gin index? If not, how can I do this query with an index?

Just run explain analyze and all your questions will be answered — user330315
– user330315, Commented Mar 1, 2014 at 1:26

Mildred · Accepted Answer · 2024-02-29 14:18:40Z

22

Why not test and see?

regress=> SET enable_seqscan  = off;
SET

regress=> explain select * from things where 'blue' = ANY (tags);
                                QUERY PLAN                                 
---------------------------------------------------------------------------
 Seq Scan on things  (cost=10000000000.00..10000000037.67 rows=6 width=36)
   Filter: ('blue'::text = ANY ((tags)::text[]))
(2 rows)

regress=> explain select * from things where tags <@ '{"blue"}';
                                     QUERY PLAN                                     
------------------------------------------------------------------------------------
 Bitmap Heap Scan on things  (cost=12.05..21.52 rows=6 width=36)
   Recheck Cond: (tags <@ '{blue}'::character varying[])
   ->  Bitmap Index Scan on index_things_on_tags  (cost=0.00..12.05 rows=6 width=0)
         Index Cond: (tags <@ '{blue}'::character varying[])
(4 rows)

regress=> explain select * from things where '{"blue","yellow"}' && tags;
                                     QUERY PLAN                                      
-------------------------------------------------------------------------------------
 Bitmap Heap Scan on things  (cost=12.10..22.78 rows=12 width=36)
   Recheck Cond: ('{blue,yellow}'::character varying[] && tags)
   ->  Bitmap Index Scan on index_things_on_tags  (cost=0.00..12.09 rows=12 width=0)
         Index Cond: ('{blue,yellow}'::character varying[] && tags)
(4 rows)

So Pg is using the index for the && and <@ queries, but not for = ANY (...).

I'm sure it'd be possible to teach Pg to transform x = ANY (y) into ARRAY[x] @> y, but it doesn't at the moment.

What 2 does is exactly what you say you want. Test if "blue" is one of the tags. It's not an equality test, it's a membership test.

However, if instead of checking a literal value like 'blue' you use a value from another table, I noticed that:

somethings.tag = ANY (otherthings.tags) was faster if the number of otherthings rows was small (it does a sequantial scan on otherthings and uses the index for somethings)
ARRAY[somethings.tag] <@ otherthings.tags was faster if the number of otherthings is larger because it uses the gin index on otherthings but it seems the ARRAY[] construct forces a sequantial scan on somethings. If however you don't need to construct the array, it should use the gin index on both sides.

edited Feb 29, 2024 at 14:18

Mildred

4854 silver badges15 bronze badges

answered Feb 28, 2014 at 22:13

Craig Ringer

329k83 gold badges742 silver badges820 bronze badges

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

4 Comments

John Bachir Over a year ago

Thanks! I wasn't familiar with SET enable_seqscan = off; in order to test index usage on small data.

John Bachir Over a year ago

here's what i'm observing about the behavior of 2: gist.github.com/jjb/9281339

Craig Ringer Over a year ago

@JohnBachir Reverse it; @>. BTW, sqlfiddle.com is much more useful for that kind of thing.

John Bachir Over a year ago

oh right. hah! thanks. also didn't know about sqlfiddle.com, very nice!

Collectives™ on Stack Overflow

How does one query a postgres array using the gin index?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related