Como os tipos afetam o planejamento da consulta PosgreSQL

Depois de algumas horas procurando um problema de desempenho difícil de descobrir, finalmente encontramos a causa do problema: tivemos um bug em que estávamos usando números em vez de inteiros para pesquisar linhas por sua chave primária.

Embora o PostgreSQL seja inteligente o suficiente para forçar seus parâmetros de consulta quando você usa o tipo errado, fazer isso pode afetar o desempenho (no meu caso, por várias ordens de magintude).

Imagine que você tem uma tabela de comentários cujo id é uma chave primária inteira.

Essas 2 consultas retornariam os mesmos resultados:

selecione nos comentários onde id = 3,0;

selecione nos comentários onde id = 3;

MAS …. vamos tentar explicar a consulta correta:

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

Bem. Estamos usando nosso precioso índice. E o outro?

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

Eu não sei porque o PostgreSQL não é capaz de converter o numérico para inteiro para fazer uma varredura de índice, mas faz uma varredura Seq em TODA A TABELA! Jogue algumas centenas de milhares de linhas e, voilà, um problema ENORME. E, como a consulta realmente funciona, você pode passar algumas horas tentando descobrir onde está o seu problema de desempenho.