Postgres ignoring index on join, but all planner estimates are correct?

by David Wolever   Last Updated October 15, 2018 23:06 PM

I've got a query that looks like this:

    SELECT *
    FROM (VALUES ('abc123')) AS user_ids(user_id)
) AS user_ids(user_id)
LEFT JOIN users ON = user_id

But instead of using the index on, the query planner opts for a seq scan on the ~2.5M row users table:

                                QUERY PLAN                                
 Hash Right Join  (cost=0.03..400966.32 rows=1 width=1065)
   Hash Cond: (( = ('abc123'::text))
   ->  Seq Scan on users  (cost=0.00..391452.66 rows=2536966 width=1033) <-- HERE
   ->  Hash  (cost=0.02..0.02 rows=1 width=32)
         ->  Result  (cost=0.00..0.01 rows=1 width=32)

Notice that the estimates (ie, on the size of the users table, and that exactly one row will be returned) are correct.

Also, Postgres will use the index on (a unique primary key index) for other queries:

-> FROM users
-> WHERE id IN ('abc123');
                                 QUERY PLAN                                  
 Index Scan using users_pkey on users  (cost=0.56..8.57 rows=1 width=1033)
   Index Cond: ((id)::citext = 'abc123'::citext)
Tags : postgresql

Related Questions

Postgres gist slow index f_unaccent

Updated April 09, 2018 20:06 PM