1

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)
2

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:

=> 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:

=> 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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.