Effective query table with conditions, including an array column in PostgreSQL

Question

We need to come up with a way to efficiently execute the query with the columns of the array and the whole in the WHERE sorted by the timestamp column. Using PostgreSQL 9.2.

The query we need to fulfill is as follows:

 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 text value of 1-3 letters.

The structure of the table is as follows:

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

How do I design indexes / modify a query to make it as efficient as possible?

Thank you very much! Let me know if more information is needed and I will update as soon as possible.

Answer 1

PG can use indexes for an array, but you need to use array operators for this, so instead of <text_value> = any (array_col) use ARRAY[<text_value>]<@array_col ( fooobar.com/questions/58611 / ... ). You can use the SET enable_seqscan=false; command SET enable_seqscan=false; to force pg to use indexes if you can see if the ones you created are really valid. Unfortunately, the GIN index cannot be created in the integer column, so you will need to create two different indexes for these two columns. See Execution Plans here: http://sqlfiddle.com/#!12/66a71/2