current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
stack overflow careers
Sign up ×
Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them, it only takes a minute:
up vote 8 down vote favorite
2

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:

  1. select * from things where 'blue' = ANY (tags)
  2. select * from things where tags <@ '{"blue"}'
  3. 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?
share|improve this question
    
Just run explain analyze and all your questions will be answered – a_horse_with_no_name Mar 1 '14 at 1:26

1 Answer 1

up vote 9 down vote accepted

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.

share|improve this answer
    
Thanks! I wasn't familiar with SET enable_seqscan = off; in order to test index usage on small data. – John Bachir Feb 28 '14 at 22:26
    
here's what i'm observing about the behavior of 2: gist.github.com/jjb/9281339 – John Bachir Feb 28 '14 at 22:29
2  
@JohnBachir Reverse it; @>. BTW, sqlfiddle.com is much more useful for that kind of thing. – Craig Ringer Feb 28 '14 at 22:31
    
oh right. hah! thanks. also didn't know about sqlfiddle.com, very nice! – John Bachir Feb 28 '14 at 22:41

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.