Beyond primary keys, most production Postgres databases bleed performance through missing partial indexes, expression indexes, and un-indexed foreign keys. Here is how to diagnose and fix each pattern

PostgreSQL Query Performance: Indexes Developers Forget to Add

Your Postgres database feels fast during development. You have a primary key on every table, a handful of obvious indexes, and queries return in milliseconds. Then production arrives — with real data volumes, concurrent users, and latency spikes that no amount of connection pooling seems to fix.

The culprit is almost never the obvious missing index. It is the subtle ones: the partial index that would have cut your working set by 90%, the expression index that Postgres cannot use because you wrapped a column in a function, and the foreign key that triggers a sequential scan on every cascade check. This article walks through each pattern with real diagnostic output and prescribes exactly when and how to add the right index.

Start With EXPLAIN ANALYZE, Not Guesswork

Before adding any index, run EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) on your slow query. The two signals that matter most are:

Seq Scan on a large table — Postgres is reading every row.
Rows Removed by Filter — the ratio of rows discarded versus rows returned tells you how selective the predicate is.

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT id, status, created_at
FROM orders
WHERE tenant_id = 42 AND status = 'pending';

If you see Seq Scan on orders (rows=1200000) ... Rows Removed by Filter: 1198743, you are paying for 1.2 million row reads to return 1,257 rows. That is the kind of waste a well-chosen index eliminates in one migration.

Pattern 1 — The Un-Indexed Foreign Key

Postgres automatically creates a unique index on the referenced side of a foreign key (usually the primary key). It creates nothing on the referencing side. This is intentional — foreign key columns are not always query predicates — but in practice, they almost always are.

Consider an order_items table with a foreign key on order_id. Every time you query WHERE order_id = $1, Postgres scans the entire order_items table unless you add the index yourself:

CREATE INDEX idx_order_items_order_id ON order_items (order_id);

The impact compounds during ON DELETE CASCADE operations. Postgres must verify no child rows exist before deleting a parent row, and it does so with a sequential scan if no index covers the foreign key column. A single delete on orders can trigger dozens of full scans on order_items, shipments, invoices, and every other referencing table.

Rule of thumb: index every foreign key column unless you have a measured reason not to.

Pattern 2 — Missing Partial Indexes

A partial index covers only the rows that match a WHERE clause you supply at index-creation time. They are smaller, faster to update, and often dramatically more selective than a full-column index.

The classic missed opportunity is a status column with extreme cardinality skew. In a SaaS orders table, 97% of rows might have status = 'completed' and only 3% have status = 'pending'. Your support dashboard only ever queries pending orders. A full index on status helps Postgres a little; a partial index helps it enormously:

CREATE INDEX idx_orders_pending
ON orders (created_at DESC)
WHERE status = 'pending';

Now the index contains only the 3% of rows your dashboard queries, and Postgres can satisfy the sort on created_at without a separate sort step. Query time on a 10-million-row table can drop from 800 ms to under 10 ms.

Other high-value partial index candidates:

Soft-deleted rows: WHERE deleted_at IS NULL
Unprocessed jobs: WHERE processed = false
Active subscriptions: WHERE cancelled_at IS NULL

Pattern 3 — Expression Indexes You Are Not Using

Postgres can only use an index when the query predicate matches the indexed expression exactly. Wrapping a column in any function breaks index usage silently — no error, no warning, just a sequential scan.

The most common offender is case-insensitive search:

-- This query cannot use a plain index on email:
SELECT * FROM users WHERE LOWER(email) = LOWER($1);

The fix is an expression index that mirrors the function in the query:

CREATE INDEX idx_users_lower_email ON users (LOWER(email));

The same pattern applies to DATE(created_at) for day-level filtering, EXTRACT(YEAR FROM ...) for annual reports, and any computed column you filter on repeatedly. If the function appears in your WHERE clause regularly, it belongs in an index definition.

A quick way to find these in production is to search pg_stat_statements for queries with high total_exec_time and low calls ratio, then run EXPLAIN ANALYZE to confirm a Seq Scan is the bottleneck.

Pattern 4 — Over-Indexed Tables Hiding the Real Problem

Counterintuitively, too many indexes can degrade read performance. Postgres's query planner evaluates every available index for each query. On tables with 15 or 20 indexes, planning overhead accumulates, and the planner occasionally chooses a suboptimal index.

More practically, every index you add increases write latency and VACUUM workload. Use pg_stat_user_indexes to identify indexes with zero or near-zero idx_scan values over a representative time window, and drop them:

SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan < 10
ORDER BY idx_scan ASC;

Pruning dead indexes often improves write throughput and gives the planner cleaner choices for your remaining, purposeful indexes.

Pattern 5 — Composite Index Column Order

A composite index on (tenant_id, created_at) supports queries filtering on tenant_id alone or on both columns together. It does not support queries filtering on created_at alone. This is the leftmost prefix rule, and violating it is one of the most common causes of unexpected sequential scans in multi-tenant SaaS applications.

Design composite indexes by leading with the highest-cardinality equality predicate first, followed by range or sort columns. If your query is WHERE tenant_id = $1 AND created_at > $2 ORDER BY created_at DESC, the index (tenant_id, created_at) lets Postgres satisfy both the filter and the sort without a separate pass.

Ongoing Maintenance: Make This a Habit

Index strategy is not a one-time migration task. As query patterns evolve, so should your indexes. Integrate these checks into your engineering workflow:

Weekly: review pg_stat_statements for new high-cost queries.
Per release: run EXPLAIN ANALYZE on any query touching tables that grew significantly.
Quarterly: audit unused indexes with pg_stat_user_indexes and prune aggressively.

Postgres also ships with pg_trgm for trigram-based text search indexes and BRIN indexes for append-heavy time-series tables — both are underused and can replace far heavier solutions when matched to the right access pattern.

Why This Matters for Your Project

Whether you are building a multi-tenant SaaS product, a mobile app backend, or an internal data platform, database performance compounds at scale in ways that are expensive to retrofit. Getting index strategy right early — partial indexes for skewed data, expression indexes for computed predicates, and consistent coverage of foreign keys — means your backend scales with load rather than against it. At Code!nk Technologies, this is one of the first things we audit when a client's growing product starts showing latency they cannot explain. The fix is rarely more infrastructure — it is almost always a smarter index sitting one migration away.