Postgres indexing: why your index isn't being used

Indexing is the highest-leverage thing you can do to a Postgres database, and also the easiest to get subtly wrong. An index that isn't used costs you writes and disk for nothing. Below are the rules that account for most of the wins, and most of the confusion.

1. Column order in a composite index is not cosmetic

A B-tree index can only be used left to right. An index on (tenant_id, created_at) helps:

• where tenant_id = $1 ✓
• where tenant_id = $1 order by created_at desc ✓ (and the sort disappears)
• where created_at > $1 ✗ — the leading column is missing

The rule of thumb: equality columns first, then the range or ORDER BY column. Reversing it — (created_at, tenant_id) — forces the index to scan every row in the date range and filter tenants afterwards.

2. A function on the column hides the index

where lower(email) = 'sam@example.com' cannot use an index on email, because the index stores the original values. Index the expression instead:

create index users_email_lower_idx on users (lower(email));

The expression in the query must match the one in the index. If you find yourself writing lower() everywhere, consider the citext type or normalising on write instead.

3. A type mismatch does the same thing, invisibly

Comparing a bigint column to a string literal, or a uuid column to text, can force a cast on the column side and leave your index unused. This is a common way for an ORM to quietly break a query. If a plan says Seq Scan where you expected an index, check the actual types with \d tablename.

4. Leading wildcards need trigrams, not B-trees

like 'wattle%' can use a B-tree. like '%wattle%' never can — the index is sorted by the start of the string. For substring and fuzzy matching, use trigrams:

create extension if not exists pg_trgm;
create index products_name_trgm_idx on products using gin (name gin_trgm_ops);

-- now this can use the index
select * from products where name ilike '%merino%';

One more subtlety on prefix matching: on a database whose collation isn't C, a plain B-tree won't be used for LIKE 'x%' unless you build it with the pattern operator class:

create index products_sku_prefix_idx on products (sku text_pattern_ops);

5. Sometimes the planner is right to ignore your index

If a query returns 30% of a table, hopping through an index and then fetching each row from the heap is genuinely slower than reading the table straight through. A sequential scan isn't a failure — it's the correct plan for an unselective query. The fix isn't a hint (Postgres deliberately has none); it's making the query more selective, or accepting the scan.

6. Partial indexes: index only the rows you query

Most tables have a hot subset. Soft-deleted rows, completed jobs, archived records — you rarely query them, but they inflate every index. A partial index covers only what matters:

create index orders_open_idx on orders (customer_id)
  where status in ('pending', 'processing');

It's smaller, faster to scan, and cheaper to maintain. The catch: the planner will only use it when it can prove your query's WHERE clause implies the index's, so the predicate needs to appear in the query.

7. Covering indexes and index-only scans

If an index contains every column a query needs, Postgres can answer from the index alone. INCLUDE adds payload columns without making them part of the search key:

create index orders_lookup_idx on orders (tenant_id, created_at) include (total_cents, status);

Index-only scans also depend on the visibility map, so a table that autovacuum hasn't touched recently will still visit the heap — visible in the plan as Heap Fetches.

8. Foreign keys are not indexed for you

Postgres indexes the referenced side (it's a primary key), but not the referencing column. That's fine for inserts, and painful when you delete or update a parent row: every child table gets scanned to check the constraint. If delete from customers where id = … is mysteriously slow, index the child columns.

9. Build and drop indexes concurrently in production

A plain CREATE INDEX takes a lock that blocks writes to the table for the whole build. On a live table, use:

create index concurrently orders_customer_idx on orders (customer_id);

It's slower and can't run inside a transaction block, but it doesn't block your application. If it fails part-way it leaves an INVALID index behind, which you should drop and re-create:

select indexrelid::regclass from pg_index where not indisvalid;
drop index concurrently orders_customer_idx;

10. Delete the indexes you aren't using

Every index is paid for on every insert, update and delete. Find the freeloaders:

select relname as table, indexrelname as index,
       idx_scan as scans, pg_size_pretty(pg_relation_size(indexrelid)) as size
from pg_stat_user_indexes
order by idx_scan asc, pg_relation_size(indexrelid) desc;

Two cautions: let the stats accumulate long enough to include monthly and quarterly jobs, and never drop an index backing a primary key or unique constraint. Also look for redundant indexes — if you have (tenant_id, created_at), a separate index on (tenant_id) is usually dead weight.

The short version

• Equality columns first, range/sort column last.
• Index expressions if you query expressions.
• Watch for casts, they're the silent index killer.
• %foo% means trigrams; foo% may need text_pattern_ops.
• Partial indexes for hot subsets, INCLUDE for index-only scans.
• Index your foreign keys.
• CONCURRENTLY in production, always.
• Audit and drop what nothing scans.

All of this is plain PostgreSQL — nothing here is specific to any host, WattleDB included. Your databases run stock Postgres, so these techniques (and every index you've already built) move with you.