Postgres ignoring index on join, but all planner estimates are correct? 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))
   -&gt;  Seq Scan on users  (cost=0.00..391452.66 rows=2536966 width=1033) &lt;-- HERE
   -&gt;  Hash  (cost=0.02..0.02 rows=1 width=32)
         -&gt;  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:

=&gt; EXPLAIN SELECT *
-&gt; FROM users
-&gt; 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)
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)