Postgres ignoring index on join, but all planner estimates are correct?
The problem is that the string literals in the subquery / VALUES statement are of type text, while the users.id column is of type citext, and the citext index can't be used when comparing citext = text.

When the input type is citext the index is used:

=&gt; explain select * from users where hash = 'abc123'::citext;
                                 QUERY PLAN                                  
-----------------------------------------------------------------------------
 Index Scan using users_pkey on users  (cost=0.56..8.57 rows=1 width=1033)
   Index Cond: ((hash)::citext = 'abc123'::citext)
(2 rows)


But the index isn't used when the input type is text:

=&gt; explain select * from users where hash = 'abc123'::text;
                          QUERY PLAN                          
--------------------------------------------------------------
 Seq Scan on users  (cost=0.00..397795.08 rows=1 width=1033)
   Filter: ((hash)::text = 'abc123'::text)
(2 rows)