Partial, expression and unique indexes

conceptThe concept

A partial index has a WHERE clause: CREATE INDEX ... ON orders (created_at) WHERE status = 'pending'. It is small, cheap to maintain, and used when the query's WHERE implies the index predicate. Ideal when the queried subset is a small fraction of the table, such as unprocessed jobs or open orders.

An expression index indexes the result of an expression: CREATE INDEX ON customers (lower(email)). The query must use the same expression (WHERE lower(email) = ...). ANALYZE also collects statistics for indexed expressions, which can fix estimates for expressions the planner otherwise guesses at.

A unique index can be partial too: CREATE UNIQUE INDEX ON subscriptions (customer_id) WHERE active allows any number of inactive rows but at most one active row per customer.

whyWhy it matters

A job queue table with 50 million done rows and 200 pending ones needs an index over 200 rows, not 50 million. Case-insensitive login lookups are the classic expression index.

Use caseReal-world scenario

The fulfilment worker polls for pending orders, the login form looks up customers by email regardless of case, and each customer may have only one default address.

Try itExamples

Run and edit these in the interactive lesson.

BEGIN;
CREATE INDEX orders_pending_idx ON orders (created_at) WHERE status = 'pending';
CREATE INDEX orders_status_created_full ON orders (status, created_at);
SELECT relname, pg_size_pretty(pg_relation_size(oid)) AS size
FROM pg_class WHERE relname IN ('orders_pending_idx', 'orders_status_created_full');
EXPLAIN SELECT id FROM orders WHERE status = 'pending' ORDER BY created_at LIMIT 20;
ROLLBACK;

BEGIN;
CREATE INDEX customers_email_lower_idx ON customers (lower(email));
ANALYZE customers;
EXPLAIN SELECT * FROM customers WHERE lower(email) = 'priya.sharma7@example.com';
EXPLAIN SELECT * FROM customers WHERE email ILIKE 'priya.sharma7@example.com';
ROLLBACK;

EXPLAIN SELECT * FROM orders WHERE lower(status) = 'pending';

BEGIN;
CREATE INDEX orders_lower_status_idx ON orders (lower(status));
ANALYZE orders;
EXPLAIN SELECT * FROM orders WHERE lower(status) = 'pending';
ROLLBACK;

BEGIN;
CREATE TABLE addresses (
  id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id int NOT NULL REFERENCES customers (id),
  line1 text NOT NULL,
  is_default boolean NOT NULL DEFAULT false
);
CREATE UNIQUE INDEX addresses_one_default ON addresses (customer_id) WHERE is_default;
INSERT INTO addresses (customer_id, line1, is_default) VALUES (1, '1 Main St', true), (1, '2 Side St', false), (1, '3 Old Rd', false);
INSERT INTO addresses (customer_id, line1, is_default) VALUES (1, '4 New Ave', true);

Avoid / preferDo this, not that

CREATE INDEX ON customers (email);
SELECT * FROM customers WHERE lower(email) = lower($1);

CREATE INDEX ON customers (lower(email));
SELECT * FROM customers WHERE lower(email) = lower($1);

-- partial index WHERE status = 'pending', queried with
SELECT * FROM orders WHERE status = $1 AND customer_id = $2;

SELECT * FROM orders WHERE status = 'pending' AND customer_id = $1;

PitfallsCommon mistakes

• Writing the query predicate differently from the partial index predicate (for example status IN ('pending') versus a parameter). The planner must be able to prove the query implies the index predicate; with a generic prepared statement parameter it often cannot.
• Creating an expression index and querying a slightly different expression (lower(trim(email))).
• Forgetting to ANALYZE after creating an expression index, so the new statistics are not there yet.

LimitsWhen not to use it

Do not create partial indexes on predicates that change often (this week's date range, a feature flag) or that queries express in many different ways; the index silently stops matching.

SecuritySecurity notes

Partial unique indexes enforce business rules the application can get wrong under concurrency, such as one active subscription per customer: CREATE UNIQUE INDEX ON subscriptions (customer_id) WHERE active.

ChallengeTry it yourself

BEGIN;
CREATE INDEX orders_pending_by_customer ON orders (customer_id) WHERE status = 'pending';
SELECT pgm.uses_index($q$SELECT * FROM orders WHERE status = 'pending' AND customer_id = 42$q$) AS pending_uses_index,
       pgm.uses_index($q$SELECT * FROM orders WHERE status = 'paid' AND customer_id = 42$q$) AS paid_uses_index;
ROLLBACK;

InterviewQuestions you should be able to answer

QuizCheck your understanding

4 questions with instant feedback in the interactive lesson; a wrong answer goes into your review deck.

Go deeperOfficial documentation

• Multicolumn indexes
• Index-only scans and covering indexes
• Partial indexes