How do types affect PosgreSQL query planning

After some long hours hunting for a performance problem hard to figure out, we finally found the cause of the problem: We had a bug where we were using numerics instead of integers to search for rows by their primary key.

Although PostgreSQL is smart enough to coerce your query parameters when you use the wrong type, doing so may affect the performance (in my case, by several orders of magintude).

Imagine you have a comments table whose id is an integer primary key.

These 2 queries would return the same results:

select from comments where id = 3.0;

select from comments where id = 3;

BUT.... let's try to explain the correct query:

explain select from comments where id = 3;
QUERY PLAN
-----------------------------------------------------------------------
 Index Scan using comments_pkey on comments  (...)
 Index Cond: (id = 3)
(2 rows)

Fine. We are using our precious index. What about the other one?

explain select from comments where id = 3.0;
QUERY PLAN
-----------------------------------------------------------------------
  Seq Scan on comments  (...)
  Filter: ((id)::numeric = 3)
(2 rows)

I don't know why PostgreSQL is not able to cast the numeric to integer to do an index scan, but it does a Seq Scan on ALL THE TABLE! Throw in some hundreds of thousands of rows and, voilà, a HUGE problem. And, as the query does indeed work, you may spend some hours trying to figure out where your performance problem is.