PostgreSQL Indexing: The Missing Guide for Application Developers

Every developer knows indexes speed up queries. Far fewer developers know which type of index to create, how to verify it's being used, or the subtle mistakes that cause PostgreSQL to ignore your index entirely.

The four index types you'll actually use

B-tree (default): The workhorse. Use it for equality and range queries on any comparable type. This is 95% of your indexes.

Partial index: A B-tree (or other) index with a WHERE clause. Index only the rows you actually query:

-- Index only active users, not the full table
CREATE INDEX idx_users_active_email
ON users (email)
WHERE deleted_at IS NULL;

If your queries always filter on deleted_at IS NULL, this index is smaller and faster than a full-table index.

Composite index: An index on multiple columns. The column order matters — the index can be used for queries that filter on a prefix of the indexed columns, but not for queries that skip a prefix column.

CREATE INDEX idx_orders_tenant_status ON orders (tenant_id, status, created_at);
-- ✅ Used by: WHERE tenant_id = ? AND status = ?
-- ✅ Used by: WHERE tenant_id = ? AND status = ? AND created_at > ?
-- ❌ NOT used by: WHERE status = ?  (skips tenant_id, the leading column)

Covering index (INCLUDE): An index that includes additional columns beyond the indexed ones, enabling index-only scans:

CREATE INDEX idx_users_email ON users (email) INCLUDE (id, name);
-- Query on email that only needs id and name never touches the heap

EXPLAIN ANALYZE: your indexing feedback loop

Don't guess whether your index is being used. Verify it:

EXPLAIN (ANALYZE, BUFFERS) 
SELECT id, name FROM users WHERE email = 'user@example.com';

Look for:

• Index Scan or Index Only Scan → your index is being used ✅
• Seq Scan → your index is being ignored ❌
• Buffers: shared hit=X read=Y → low read means data was cached

Why PostgreSQL ignores your index (and how to fix it)

1. The LIKE '%prefix%' problem: B-tree indexes can't support leading wildcards. Use pg_trgm extension + GIN index for substring search.

2. Function applied to indexed column:

-- ❌ Index on email is NOT used
WHERE LOWER(email) = 'user@example.com'

-- ✅ Fix: use a functional index
CREATE INDEX idx_users_email_lower ON users (LOWER(email));

3. Implicit type cast: If the column is varchar and you query with an integer, PostgreSQL casts and ignores the index. Match types explicitly.

4. Low selectivity: An index on a boolean column in a large table may not be used if PostgreSQL estimates a table scan is cheaper. Partial indexes solve this.

5. Statistics are stale: PostgreSQL uses table statistics to decide whether to use an index. After a large data load, run ANALYZE to update them.

Indexes on CodeMyFYP Academy

The queries I optimized most aggressively were:

• Course listing by university + published status → composite index on (university_id, published, created_at DESC)
• Student search within a tenant → composite index on (tenant_id, name) with GIN trigram for fuzzy search
• Payment status lookup → partial index on (payment_id) where status IN ('PENDING', 'AUTHORIZED')

In each case, EXPLAIN ANALYZE before and after showed the improvement clearly — not as an assumption, but as a measured fact.

The maintenance cost you need to plan for

Every index slows down INSERT, UPDATE, and DELETE operations on the indexed table. For write-heavy tables, index proliferation is a real cost. The rule I follow: if I can't point to a specific slow query that this index fixes, I don't create it.

Unused indexes accumulate silently. Query pg_stat_user_indexes to find indexes that haven't been used since the last statistics reset:

SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY tablename;

Delete the unused ones. Your write performance will thank you.