Postgres EXPLAIN and where to put indexes

Question

I'm trying to make some sense from postgres EXPLAIN output. I want to learn where to put indexes in different scenarios.

One example is:

EXPLAIN for: SELECT "users".* FROM "users" INNER JOIN "user_sessions" ON "user_sessions"."user_id" = "users"."id" WHERE "user_sessions"."token" = $1 AND (user_sessions.expires_at > '2017-09-29 08:56:26.388291') [["token", "foo"]]

Gives me:

 Nested Loop  (cost=0.30..16.35 rows=1 width=192)
   ->  Index Scan using index_user_sessions_on_token on user_sessions  (cost=0.15..8.17 rows=1 width=4)
         Index Cond: ((token)::text = 'foo'::text)
         Filter: (expires_at > '2017-09-29 08:56:26.388291'::timestamp without time zone)
   ->  Index Scan using users_pkey on users  (cost=0.15..8.17 rows=1 width=192)
         Index Cond: (id = user_sessions.user_id)

Would a single index [user_id, token, expires_at] be "more" appropriate than two indexes with user_id and [token, expires_at]?

Note: your query will yield duplicates if more than one matching user_sessions rows are found. (I suggest using an EXISTS() construct) — joop
– joop, Commented Sep 29, 2017 at 11:02

Laurenz Albe · Accepted Answer · 2017-09-29 09:27:05Z

2

You could improve by dropping the index on token and replacing it with one on (token, expires_at). Then the whole condition on user_sessions could be used as index condition, which would save unnecessary index reads and heap checks and make the query more efficient.

To see if that really has much effect, run EXPLAIN (ANALYZE, BUFFERS) in both cases. If there are a lot of rows removed by the filter and more blocks touched, the query will be slower.

An index on user_sessions (user_id) won't help at all, because this condition is never searched: First, PostgreSQL find all rows in user_session based on the conditions on token and expires_at, then it finds all matching rows in users.

answered Sep 29, 2017 at 9:27

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

1 Comment

SandeliusME Over a year ago

Awesome response, last paragraph is spot on. Now I have a better understanding how it works. Thank you!

Collectives™ on Stack Overflow

Postgres EXPLAIN and where to put indexes

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related