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]?

Answer 1

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.