Postgres - Index with multiple where clauses

Question

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:

1st index for 1st WHERE = (userid, timestamp)
2nd index for 2nd WHERE = (userid, source, timestamp)

EDIT:

I have created 2 indexes.

1 - (userid, source, timestamp)
2 - (userid, timestamp)

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

Laurenz Albe · Accepted Answer · 2020-04-27 18:08:21Z

4

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.

edited Apr 27, 2020 at 18:08

answered Apr 27, 2020 at 13:30

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

6 Comments

Bergi Over a year ago

Why will this lead to a nested loop? The inner query does not use any outer values, and will return at most a single result.

gozluklu_marti Over a year ago

@Bergi would you say that this is still the best option?

Bergi Over a year ago

@gozluklu_marti Unless you have multiple media with the same timestamp, I'd probably not use a nested query at all. I would always benchmark, and recommend to have a look at the EXPLAIN ANALYZE.

gozluklu_marti Over a year ago

@LaurenzAlbe if you read my updated question, your index is not the best one according to Postgres :)

Laurenz Albe Over a year ago

I have added an explanation for that. The reason is the selectivity of the conditions, which I didn't know about.

|

Collectives™ on Stack Overflow

Postgres - Index with multiple where clauses

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related