Na versão 9.6.3, adicionar NULLIF() na cláusula WHERE altera o plano de uma varredura de heap de bitmap para uma varredura de índice e altera o tempo de execução de cerca de 0,3 ms para 43.000 ms.
Aqui está uma pergunta relacionada: https://stackoverflow.com/questions/21062148/how-to-query-postgres-on-optional-params
Esta é a consulta e o plano rápidos:
SELECT
c.name_first, c.name_last, c.postal_code,
v.tag as plate_number, v.tag_state as plate_state, v.year, v.make, v.model
FROM customers_vehicles AS v
JOIN customers AS c ON (v.customer_id = c.id)
WHERE ('XXXXXX'::text is null OR v.tag='XXXXXX');
┌───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ QUERY PLAN │
├───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Nested Loop (cost=824.67..451863.32 rows=43830 width=228) (actual time=0.202..0.203 rows=1 loops=1) │
│ -> Bitmap Heap Scan on customers_vehicles v (cost=824.12..121946.77 rows=43830 width=164) (actual time=0.104..0.104 rows=1 loops=1) │
│ Recheck Cond: (tag = 'XXXXXX'::text) │
│ Heap Blocks: exact=1 │
│ -> Bitmap Index Scan on customers_vehicles_tag_idx (cost=0.00..813.16 rows=43830 width=0) (actual time=0.090..0.090 rows=1 loops=1) │
│ Index Cond: (tag = 'XXXXXX'::text) │
│ -> Index Scan using customers_pkey on customers c (cost=0.56..7.52 rows=1 width=128) (actual time=0.093..0.094 rows=1 loops=1) │
│ Index Cond: (id = v.customer_id) │
│ Planning time: 0.268 ms │
│ Execution time: 0.256 ms │
└───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
E esta é a consulta e o plano lentos:
SELECT
c.name_first, c.name_last, c.postal_code,
v.tag as plate_number, v.tag_state AS plate_state, v.year, v.make, v.model
FROM customers_vehicles AS v
JOIN customers as c on (v.customer_id = c.id)
WHERE (nullif('XXXXXX'::text,'') IS NULL OR v.tag='XXXXXX');
┌──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ QUERY PLAN │
├──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Nested Loop (cost=1.12..7509681.67 rows=87441 width=228) (actual time=5462.111..42209.513 rows=1 loops=1) │
│ -> Index Scan using customers_vehicles_customer_id_idx on customers_vehicles v (cost=0.56..6921180.68 rows=87441 width=164) (actual time=5462.023..42209.423 rows=1 loops=1) │
│ Filter: ((NULLIF('XXXXXX'::text, ''::text) IS NULL) OR (tag = 'XXXXXX'::text)) │
│ Rows Removed by Filter: 8766005 │
│ -> Index Scan using customers_pkey on customers c (cost=0.56..6.72 rows=1 width=128) (actual time=0.080..0.081 rows=1 loops=1) │
│ Index Cond: (id = v.customer_id) │
│ Planning time: 0.209 ms │
│ Execution time: 42209.549 ms │
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
A única diferença está na consulta lenta na cláusula WHERE: 'XXXXXX' IS NULLmuda para nullif('XXXXXX','') IS NULL. Parece que o planejador não pode otimizar a primeira condição se ela contiver NULLIF(). Existe uma maneira de coagi-lo a fazê-lo?
Tente alterar seu predicado para o seguinte:
WHERE ('XXXXXX'::text is null OR v.tag='XXXXXX' OR 'XXXXXX' = '');Como isso é efetivamente o que a
NULLIFinstrução está fazendo do ponto de vista lógico. Estou assumindo que'XXXXXX'é uma coluna neste cenário e não apenas uma string aleatória deX's, correto?