Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.

Need to come up with a way to efficiently execute a query with and array and integer columns in the WHERE clause, ordered by a timestamp column. Using PostgreSQL 9.2.

The query we need to execute is:

SELECT id 
from table 
where integer = <int_value> 
  and <text_value> = any (array_col) 
order by timestamp 
limit 1;

int_value is an integer value, and text_value is a 1 - 3 letter text value.

The table structure is like this:

    Column     |            Type             |       Modifiers
---------------+-----------------------------+------------------------
 id            | text                        | not null
 timestamp     | timestamp without time zone |
 array_col     | text[]                      |
 integer       | integer                     |

How should I design indexes / modify the query to make it as efficient as possible?

Thanks so much! Let me know if more information is needed and I'll update ASAP.

share|improve this question

1 Answer

up vote 1 down vote accepted

PG can use indexes on array but you have to use array operators for that so instead of <text_value> = any (array_col) use ARRAY[<text_value>]<@array_col (http://stackoverflow.com/a/4059785/2115135). You can use the command SET enable_seqscan=false; to force pg to use indexes if it's possible to see if the ones you created are valid. Unfortunately GIN index can't be created on integer column so you will have to create two diffrent indexes for those two columns. See the execution plans here: http://sqlfiddle.com/#!12/66a71/2

share|improve this answer
Ok, thanks. What about the order by? How can I address that? – Juan Carlos Coto Mar 19 at 17:00
Oh, I missed that. In that case index(integer,timestamp) should be faster than two separate index on (integer) and (timestamp) but I'm afraid that's a bit above my level so I can't guarantee that so you should try it out. – Jakub Kania Mar 19 at 17:21

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.