CODEWITHSUNDEEP
postgresqlindexing
gozluklu_marti
gozluklu_marti

Reputation: 55

Postgres - Index with multiple where clauses

I have a query that includes two WHERE clauses. Looks like this:

SELECT m 
FROM Media m 
WHERE m.userid = :id 
  AND m.timestamp = (SELECT MAX(mm.timestamp) 
                     FROM Media mm 
                     WHERE mm.userid = :id 
                       AND mm.source IN :sources 
                       AND mm.timestamp < :date)

What I want to know if this query will be faster with one index or should I create two seperate indexes for each WHERE clauses? Like:

EDIT:

I have created 2 indexes.

When I analyze the query, It always showing the second index used for the query.

Upvotes: 2

Views: 1632

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246133

Assuming that user.id is really userid, the perfect index would be

CREATE INDEX ON media(userid, source, timestamp);

That is perfect for the inner query, and the index is also good for the outer query.

To extend on that, the above assumes that all these conditions are selective, that is, they significantly reduce the number of result rows.

In your case, it seems that the condition mm.source IN :sources is not very selective, perhaps because there are only few distinct values for the column, or because you happen to query for a value that occurs frequently.

In that case, it is better to omit the column from the index, because that will make the index smaller without much loss. All other things equal, PostgreSQL will choose to scan the smaller index.

Upvotes: 4

Related Questions