expression indexes

A normal index is on a column. An expression index is on the result of something, like LOWER(email). It fixes the "I wrapped my column in a function and now my index is useless" problem.

the problem

CREATE INDEX idx_customers_email ON customers (email);

SELECT * FROM customers WHERE LOWER(email) = 'aman@mail.com';

The index stores email values sorted. But you're searching for LOWER(email), which is a different thing. The database can't use the index, it has to compute LOWER for every row. Seq scan.

the fix

Index the expression itself:

CREATE INDEX idx_customers_email_lower ON customers (LOWER(email));

Now the index stores lowercased emails, and WHERE LOWER(email) = '...' can use it.

The catch, your query has to use exactly the same expression. LOWER(email) matches, UPPER(email) doesn't, LOWER(TRIM(email)) doesn't.

common uses

case insensitive lookups (emails, usernames)

CREATE INDEX ON customers (LOWER(email));

searching by part of a date

CREATE INDEX ON orders ((EXTRACT(YEAR FROM order_date)));
-- note the double brackets, needed for anything that's not a simple function call

Though honestly for dates, rewriting the WHERE as a range on a normal index is usually better.

computed values you filter by a lot

CREATE INDEX ON customers ((first_name || ' ' || last_name));
-- helps: WHERE first_name || ' ' || last_name = 'Aman Sharma'

json fields (more in the JSON topic)

CREATE INDEX ON events ((payload->>'type'));

unique expression index

You can enforce uniqueness on an expression. Like "no two customers can have the same email, ignoring case":

CREATE UNIQUE INDEX ON customers (LOWER(email));

Now inserting AMAN@mail.com fails because aman@mail.com exists. Way better than trusting the app to lowercase everything.

rules

• the function has to be IMMUTABLE, same input always gives same output. LOWER is fine. NOW() isn't, its result changes, so you can't index it.
• they cost the same as normal indexes on writes (the expression gets computed on every insert/update).
• run ANALYZE after creating one so the planner has stats on the expression.

alternatives worth knowing

• generated columns, store the computed value as a real column and index that:

  ALTER TABLE customers ADD COLUMN email_lower TEXT GENERATED ALWAYS AS (LOWER(email)) STORED;
  CREATE INDEX ON customers (email_lower);

  Easier to see what's going on, costs a bit of disk.
• citext, a postgres extension type that's case-insensitive by default.

stuff that trips people up

• query expression not exactly matching the index expression.
• missing double brackets, CREATE INDEX ON t (EXTRACT(...)) errors, needs ((...)).
• trying to index non-immutable stuff like NOW() or random().

practice

Check queries.sql.

-- expression indexes
-- run sample-data.sql first

-- setup: a bigger customers table so the planner actually uses indexes
DROP TABLE IF EXISTS big_customers;
CREATE TABLE big_customers AS
SELECT
  g AS customer_id,
  CASE WHEN g % 2 = 0 THEN 'User' || g || '@Mail.com' ELSE 'user' || g || '@mail.com' END AS email,
  'Name' || g AS first_name
FROM generate_series(1, 300000) g;
ANALYZE big_customers;


-- examples ---------------------------------------------------------

CREATE INDEX idx_big_email ON big_customers (email);

-- can't use idx_big_email
EXPLAIN ANALYZE SELECT * FROM big_customers WHERE LOWER(email) = 'user500@mail.com';

CREATE INDEX idx_big_email_lower ON big_customers (LOWER(email));
ANALYZE big_customers;

-- now it can
EXPLAIN ANALYZE SELECT * FROM big_customers WHERE LOWER(email) = 'user500@mail.com';


-- practice ---------------------------------------------------------

-- 1. does the index get used for WHERE UPPER(email) = 'USER500@MAIL.COM'? check with EXPLAIN.
-- 2. on the real customers table, make it impossible to insert a duplicate email ignoring case.
--    then try inserting 'AMAN@MAIL.COM' and see what happens.
-- 3. create an index that helps: SELECT * FROM events WHERE payload->>'device' = 'mobile';
-- 4. why does this fail? CREATE INDEX ON orders ((order_date > NOW()));
-- 5. clean up


-- answers ----------------------------------------------------------

-- 1
EXPLAIN SELECT * FROM big_customers WHERE UPPER(email) = 'USER500@MAIL.COM';
-- no, different expression, seq scan

-- 2
CREATE UNIQUE INDEX idx_customers_email_ci ON customers (LOWER(email));
INSERT INTO customers (customer_id, first_name, email) VALUES (99, 'Test', 'AMAN@MAIL.COM');
-- ERROR: duplicate key value violates unique constraint
DROP INDEX idx_customers_email_ci;

-- 3
CREATE INDEX idx_events_device ON events ((payload->>'device'));

-- 4
-- NOW() isn't immutable, the result would change over time so the index would go stale.
-- postgres refuses: "functions in index expression must be marked IMMUTABLE"

-- 5
DROP INDEX IF EXISTS idx_events_device;
DROP TABLE big_customers;