IntermediatePerformance & Indexes

Query optimization: avoid functions on indexed columns

The query

SQL

-- BAD: function on indexed column prevents index use
SELECT * FROM employees WHERE UPPER(last_name) = 'SMITH';

-- GOOD: use expression index instead
CREATE INDEX idx_emp_last_name_upper ON employees(UPPER(last_name));
SELECT * FROM employees WHERE UPPER(last_name) = 'SMITH';

-- OR: use ILIKE which can use trigram index
CREATE EXTENSION pg_trgm;
CREATE INDEX idx_emp_trgm ON employees USING gin(last_name gin_trgm_ops);
SELECT * FROM employees WHERE last_name ILIKE 'smith';

Tested against PostgreSQL 16

Note

“Functions on WHERE columns prevent index use. Solutions: expression indexes or ILIKE with trigram index.

Tables referenced

employees10 cols