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 (
    SELECT *
    FROM (VALUES ('abc123')) AS user_ids(user_id)
) AS user_ids(user_id)
LEFT JOIN users ON users.id = user_id

But instead of using the index on users.id, 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: ((users.id)::text = ('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 users.id (a unique primary key index) for other queries:

=> EXPLAIN SELECT *
-> 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