Anti-pattern: a function on an indexed column (non-sargable)
DATE_TRUNC or EXTRACT applied to the column prevents the index from being used. Rewriting the filter as a value range makes it sargable, and the B-tree index becomes usable again.
Prerequisites
Tout moteur — le principe est universel
SQL
-- Anti-pattern : la fonction sur la colonne neutralise l'index
SELECT * FROM orders
WHERE DATE_TRUNC('day', created_at) = '2026-06-10';
-- Correctif : filtre en plage, index exploitable
SELECT * FROM orders
WHERE created_at >= '2026-06-10'
AND created_at < '2026-06-11';
-- Même logique pour une année entière
SELECT * FROM orders
WHERE created_at >= '2026-01-01'
AND created_at < '2027-01-01';Result
-- Avant (fonction sur colonne) :
Seq Scan on orders (actual time=0.018..1240.221 rows=412)
Filter: (date_trunc('day', created_at) = '2026-06-10')
Execution Time: 1271.480 ms
-- Après (filtre en plage) :
Index Scan using idx_orders_created on orders
(actual time=0.044..0.892 rows=412)
Execution Time: 1.024 ms -- x1200 plus rapideSQLSargableIndexAnti-pattern