CODEWITHSUNDEEP
performancepostgresqlindexingquery-optimization
Juan Carlos Coto
Juan Carlos Coto

Reputation: 12564

Efficiently query table with conditions including array column in PostgreSQL

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.

Upvotes: 6

Views: 2478

Answers (2)

Evan Carroll
Evan Carroll

Reputation: 1

Unfortunately GIN index can't be created on integer column so you will have to create two diffrent indexes for those two columns.

That's not entirely true, you can use btree_gin or -btree_gist

-- feel free to use GIN
CREATE EXTENSION btree_gist;
CREATE INDEX ON table USING gist(id, array_col, timestamp);
VACUUM FULL ANALYZE table;

Now you can run the operation on the index itself

SELECT *
FROM table
WHERE id = ? AND array_col @> ?
ORDER BY timestamp;

Upvotes: 0

Jakub Kania
Jakub Kania

Reputation: 16487

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

Upvotes: 2

Related Questions