Efficiently query table with conditions including array column in PostgreSQL

Question

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.

Answer 1

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 (https://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