TopolearnThe right specialised index can turn a full scan into a millisecond lookup at a fraction of the size.
Beyond the plain B-tree, PostgreSQL offers several ways to make an index fit a query exactly.
An expression index indexes a computed value. A query on lower(email) cannot use an index on email, but it can use one on lower(email). The query must use the same expression.
A partial index covers only rows matching a predicate: CREATE INDEX ON orders (ordered_at) WHERE status = 'pending'. If pending orders are 1% of the table, the index is 1% of the size, and queries that include status = 'pending' can use it. A unique partial index enforces rules like "at most one active subscription per customer".
A covering index adds payload columns with INCLUDE: CREATE INDEX ON orders (customer_id) INCLUDE (total). A query needing only those columns can be answered by an index-only scan, without visiting the table, provided the visibility map says the pages are all-visible (vacuum maintains it).
Other index access methods:
- GIN (inverted index): many keys per row. Use it for
jsonbcontainment, arrays, full-text search, and trigram (pg_trgm) searches forLIKE '%term%'. - GiST: balanced trees over geometric data, ranges and nearest-neighbour searches (PostGIS uses it). It also powers exclusion constraints, such as "no overlapping bookings".
- BRIN: stores only min/max per block range. Tiny, and very effective on huge tables whose values follow physical order, such as an append-only
created_at. Useless when values are scattered. - Hash: equality only; rarely better than B-tree.
Every index adds write cost. Check pg_stat_user_indexes.idx_scan periodically and drop indexes that are never used.
Example
-- Expression index: case-insensitive email lookup
CREATE INDEX customers_email_lower_idx ON customers (lower(email));
SELECT id FROM customers WHERE lower(email) = 'ana@example.com';
-- Partial index: small, only the rows a hot query needs
CREATE INDEX orders_pending_idx ON orders (ordered_at) WHERE status = 'pending';
SELECT id FROM orders WHERE status = 'pending' ORDER BY ordered_at LIMIT 50;
-- Partial unique index: one pending order per customer
CREATE UNIQUE INDEX one_pending_per_customer
ON orders (customer_id) WHERE status = 'pending';
-- Covering index enabling index-only scans
CREATE INDEX orders_cust_total_idx ON orders (customer_id) INCLUDE (total);
SELECT SUM(total) FROM orders WHERE customer_id = 2;
-- BRIN on a huge append-only table
CREATE INDEX events_created_brin ON events USING brin (created_at);
-- Unused indexes
SELECT indexrelname, idx_scan FROM pg_stat_user_indexes WHERE idx_scan = 0;Remember
- Expression indexes serve queries that use the same expression
- Partial indexes are small and can enforce conditional uniqueness
- INCLUDE columns enable index-only scans
- GIN for jsonb/arrays/full text, GiST for ranges and geometry, BRIN for huge ordered tables
Watch out for
- Creating an expression index on lower(email) but querying WHERE email ILIKE ..., which cannot use it.