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.

Community · Accepted Answer · 2017-05-23 11:57:26Z

2

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

edited May 23, 2017 at 11:57

CommunityBot

11 silver badge

answered Mar 19, 2013 at 16:38

Jakub Kania

16.6k2 gold badges44 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Juan Carlos Coto Over a year ago

Ok, thanks. What about the order by? How can I address that?

Jakub Kania Over a year ago

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.

Evan Carroll · Accepted Answer · 2018-06-12 09:23:59Z

0

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;

answered Jun 12, 2018 at 9:23

Evan Carroll

1

Collectives™ on Stack Overflow

Efficiently query table with conditions including array column in PostgreSQL

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related