PostgreSQL Indexing and Query Optimization: How I Approach Slow Queries

Do not add indexes simply because a query is slow.

An index can help, but it can also add write overhead, consume storage, confuse the planner in some workloads, or fail to address the real bottleneck.

Understand Query Behavior

I start with the execution plan.

EXPLAIN SELECT ...

and when it is safe in the environment:

EXPLAIN ANALYZE SELECT ...

Conceptually, I want to know whether PostgreSQL is doing sequential scans, index scans, nested loops, hash joins, or sorting large result sets. I also compare estimated rows with actual rows. Bad estimates can point to stale statistics or data distribution issues.

Index Design Is Workload-Specific

B-tree indexes are the default tool for many equality and range queries, but the column order matters.

An index like:

(tenant_id, status, created_at)

is not equivalent to three independent indexes on tenant_id, status, and created_at.

Composite indexes should reflect the filters, ordering, selectivity, and access pattern of the query. They should not be copied from one workload to another without evidence.

Selectivity and Ordering

Indexes help most when they narrow the search meaningfully or support ordering that avoids expensive sorts. A low-selectivity column may not help much by itself if most rows share the same value.

For queries that filter by tenant and status and order by creation time, index order can matter. The correct choice depends on the actual query and data distribution.

ORM N+1 Queries

Slow database behavior is often created in application code.

Conceptually:

Users
-> query orders separately for every user

In Django, select_related and prefetch_related can address different relationship patterns. The point is not the method name; the point is loading related data intentionally instead of accidentally issuing one query per row.

Pagination

Offset pagination can become expensive for large datasets because the database may still have to walk past many rows.

Cursor or keyset pagination can be better when the product needs stable forward movement through large ordered datasets. It also has trade-offs: implementation complexity, sorting constraints, and less flexible arbitrary page jumps.

Connection Pooling

Database performance includes connection management. Too many application workers with too many connections can overwhelm PostgreSQL even if each query is reasonable.

Query optimization and deployment sizing belong in the same conversation.

Partitioning

Partitioning can help when the data shape and query patterns support pruning or operational maintenance. It can also add complexity without improving the real query path.

I would not reach for partitioning before understanding indexes, query plans, table size, retention, and access patterns.

Workflow

My workflow is:

Measure
-> inspect execution plan
-> identify bottleneck
-> change one thing
-> measure again

Slow queries should be handled like engineering investigations, not superstition.