Will index speed up finding max TIMESTAMPTZ?

user_1135135 0
Viewed 44

I have a table conversation_views with columns: user_id | conversation_id | viewed_at

And a INDEX on conversation_views (conversation_id, user_id)

I would like query:

select
  max(viewed_at)
from conversation_views
where
  conversation_id=%L
  and user_id=%L

Will this query be performant with this indexing or is there another strategy I can use? I'm using Postgres 9.6.12.

postgresql-9.6 database-indexes query-optimization postgresql sql
1 Answers

The better index for that query would probably be:

CREATE INDEX on conversation_views (conversation_id, user_id, viewed_at)

That way it can descend the index to the spot where the max viewed_at for the given conversation_id and user_id occurs, and read it directly (possibly scanning a bit if there are non-visible tuples at that spot which need to be ignored).

How much better this will than your existing index depends on how selective the combo of (conversation_id, user_id) are. If there are only a few rows for each combo, the difference will be small. If there are tens of thousands the difference can be very large.

But to answer your title question, your current index should already be massively better than no index at all for most realistic scenarios.

user_17212390
Related