PostgreSQL Query Optimization: 10 Patterns That Slow Down Your Database

Most PostgreSQL performance problems come from a small number of recurring patterns. After looking at thousands of slow query reports, the same issues appear over and over: missing indexes, N+1 queries, lock contention, table bloat. The patterns are well-known, but recognizing them in your own database requires knowing what to look for.

This guide covers the 10 most common patterns that slow down PostgreSQL databases, how to identify each one using pg_stat_statements and EXPLAIN, and how to fix them.

1. Sequential Scans on Large Tables

What it is: PostgreSQL reads every row in a table to find the ones matching your WHERE clause, instead of using an index.

How to spot it: In pg_stat_statements, look for queries with high shared_blks_read relative to rows returned. In EXPLAIN ANALYZE, you'll see Seq Scan on a large table with a filter that eliminates most rows.

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 12345;

Seq Scan on orders  (cost=0.00..25000.00 rows=50 width=120) (actual time=45.123..312.456 rows=47 loops=1)
  Filter: (customer_id = 12345)
  Rows Removed by Filter: 999953

Almost a million rows scanned to find 47. That's a missing index.

How to fix it:

CREATE INDEX idx_orders_customer_id ON orders (customer_id);

After adding the index, the same query uses an Index Scan and returns in under 1ms instead of 300ms.

Not every sequential scan is a problem. On small tables (under a few thousand rows), a sequential scan is often faster than an index lookup. The optimizer usually makes the right choice. The issue is when you see sequential scans on tables with hundreds of thousands or millions of rows.

2. N+1 Query Patterns

What it is: Your application fetches a list of parent records, then issues a separate query for each parent to fetch its children. If you load 100 orders, you then issue 100 queries to load items for each order.

How to spot it: In pg_stat_statements, look for queries with extremely high calls count but low mean_exec_time. A query called 50,000 times per hour with a mean time of 0.5ms is almost certainly an N+1 pattern.

SELECT queryid, calls, mean_exec_time, left(query, 80)
FROM pg_stat_statements
WHERE calls > 10000
ORDER BY calls DESC;

The query text will often be something like SELECT * FROM order_items WHERE order_id = $1, called once per parent record.

How to fix it: Replace the loop with a single batch query:

-- Instead of N queries like: SELECT * FROM order_items WHERE order_id = $1
-- Use one query:
SELECT * FROM order_items WHERE order_id = ANY($1::int[]);

Or use a JOIN in the original query to fetch parents and children together. Most ORMs have eager loading mechanisms (includes in ActiveRecord, joinedload in SQLAlchemy, Preload in GORM) that handle this automatically.

3. Missing Composite Indexes

What it is: You have an index on column A, but your query filters on both A and B. PostgreSQL uses the index on A to narrow down results, then does a filter on B, which can still scan many rows.

How to spot it: In EXPLAIN ANALYZE, you'll see an Index Scan followed by a Filter that removes a significant percentage of rows.

Index Scan using idx_orders_customer_id on orders
  Index Cond: (customer_id = 12345)
  Filter: (status = 'pending')
  Rows Removed by Filter: 980

The index found 1,027 rows for this customer, then filtered out 980 to find the 47 pending ones.

How to fix it:

CREATE INDEX idx_orders_customer_status ON orders (customer_id, status);

Column order matters. Put the most selective column first (the one that eliminates the most rows). If you frequently query by customer_id alone and sometimes by customer_id + status, the composite index covers both cases. An index on (status, customer_id) would not efficiently serve queries filtering only by customer_id.

4. Lock Contention from Long-Running Transactions

What it is: A long-running transaction holds locks that block other queries. The blocked queries queue up, connections exhaust, and your application starts timing out.

How to spot it: This pattern often doesn't show up clearly in pg_stat_statements because the time spent waiting for locks is counted as execution time, making the query appear slow rather than blocked. Check pg_stat_activity for blocked queries:

SELECT
  blocked.pid AS blocked_pid,
  blocked.query AS blocked_query,
  blocking.pid AS blocking_pid,
  blocking.query AS blocking_query,
  now() - blocking.xact_start AS blocking_duration
FROM pg_stat_activity blocked
JOIN pg_locks bl ON bl.pid = blocked.pid
JOIN pg_locks blk ON blk.locktype = bl.locktype
  AND blk.relation = bl.relation
  AND blk.pid != bl.pid
JOIN pg_stat_activity blocking ON blocking.pid = blk.pid
WHERE NOT bl.granted;

How to fix it:

• Set statement_timeout and idle_in_transaction_session_timeout to prevent runaway transactions
• Use SET LOCAL lock_timeout = '5s' in transactions that modify data, so they fail fast rather than waiting indefinitely
• Avoid mixing DDL (schema changes) with DML (data operations) in the same transaction
• Consider using SKIP LOCKED for queue-style workloads where contention is expected

5. Table Bloat Causing Excessive I/O

What it is: PostgreSQL's MVCC architecture means that UPDATEs and DELETEs don't immediately remove old row versions. VACUUM reclaims dead tuples, but if it falls behind (or is disabled), tables accumulate dead rows that queries must scan past.

How to spot it: Check the ratio of dead tuples to live tuples:

SELECT
  relname,
  n_live_tup,
  n_dead_tup,
  round(n_dead_tup::numeric / NULLIF(n_live_tup, 0) * 100, 2) AS dead_pct,
  last_vacuum,
  last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;

A dead_pct above 20% is concerning. Above 50% is an emergency.

How to fix it:

• Tune autovacuum to be more aggressive: lower autovacuum_vacuum_scale_factor (default 0.2) to something like 0.05 for high-churn tables
• Increase autovacuum_vacuum_cost_limit to let vacuum do more work per cycle
• For severely bloated tables, consider pg_repack to rebuild the table without locking it
• Monitor vacuum lag: if last_autovacuum is hours or days old on a busy table, autovacuum can't keep up

6. Unoptimized JOINs

What it is: PostgreSQL chooses a nested loop join on two large tables, reading the inner table once for every row in the outer table. This is fine when the inner table has a good index and returns few rows per lookup. It's catastrophic when both tables are large and there's no useful index.

How to spot it: In EXPLAIN ANALYZE, look for Nested Loop with a high loops count on the inner side, or Hash Join where the hash table doesn't fit in work_mem and spills to disk.

Nested Loop  (actual time=0.123..45678.901 rows=5000 loops=1)
  -> Seq Scan on table_a  (actual time=0.010..12.345 rows=100000 loops=1)
  -> Index Scan using idx on table_b  (actual time=0.001..0.450 rows=0 loops=100000)

100,000 loops on the inner side. Even at 0.45ms each, that's 45 seconds.

How to fix it:

• Add an appropriate index on the join column of the inner table
• If a hash join would be better, increase work_mem to avoid disk spills: SET work_mem = '256MB' (per-session, not globally)
• Rewrite the query to filter more aggressively before the JOIN, reducing the number of rows on each side
• Consider materializing intermediate results for complex multi-table joins

7. Excessive Sorting Without Supporting Index

What it is: ORDER BY on a column without a supporting index forces PostgreSQL to sort the result set in memory (or on disk if it doesn't fit in work_mem).

How to spot it: In EXPLAIN ANALYZE, look for Sort Method: external merge Disk which means the sort spilled to disk, or Sort Method: quicksort Memory: XXkB with a high memory value.

Sort  (actual time=234.567..245.678 rows=500000 loops=1)
  Sort Key: created_at
  Sort Method: external merge  Disk: 98304kB

98MB sorted on disk. This is slow.

How to fix it:

• Add an index on the sort column: CREATE INDEX idx_orders_created_at ON orders (created_at)
• If you're sorting and filtering, use a composite index: CREATE INDEX idx_orders_status_created ON orders (status, created_at) for queries like WHERE status = 'active' ORDER BY created_at
• Increase work_mem if sorts are slightly above the threshold for in-memory sorting
• Add a LIMIT clause if you only need the first N results. With a matching index, PostgreSQL can use an Index Scan and stop early

8. Function Volatility Misuse

What it is: PostgreSQL classifies functions as VOLATILE, STABLE, or IMMUTABLE. A VOLATILE function is re-evaluated for every row. If you call a VOLATILE function in a WHERE clause, it prevents index usage and forces a sequential scan.

How to spot it: Look for function calls in WHERE clauses. now() is STABLE, which is fine. But custom functions default to VOLATILE unless explicitly marked otherwise.

-- This prevents index usage if my_function is VOLATILE
SELECT * FROM events WHERE my_function(event_date) > 100;

How to fix it:

• Mark functions as IMMUTABLE if they always return the same output for the same input (e.g., a hash function, a format function)
• Mark functions as STABLE if they return the same result within a single query but might change between queries (e.g., anything that reads from a table)
• Avoid calling functions on indexed columns in WHERE clauses. Instead, compute the value and compare: WHERE event_date > my_function() rather than WHERE my_function(event_date) > threshold
• Use expression indexes if you must filter on a function result: CREATE INDEX idx_events_func ON events (my_function(event_date))

9. Connection Pool Exhaustion

What it is: Your application opens more database connections than PostgreSQL can handle efficiently. Each connection consumes about 10MB of memory for the process context. At 200+ connections, you're spending significant memory and CPU on connection management rather than query execution.

How to spot it: Check current connection count against your max:

SELECT count(*) AS current_connections,
  (SELECT setting FROM pg_settings WHERE name = 'max_connections') AS max_connections
FROM pg_stat_activity;

If you're consistently above 80% of max_connections, you have a problem. Also check for idle connections:

SELECT state, count(*)
FROM pg_stat_activity
GROUP BY state;

A high count of idle connections means your application is holding connections it's not using.

How to fix it:

• Use a connection pooler like PgBouncer or Pgpool-II. PgBouncer in transaction pooling mode can multiplex hundreds of application connections onto a much smaller number of database connections.
• Tune your application's connection pool: set a maximum that's well below max_connections, and enable idle connection timeout
• For serverless environments or high-concurrency apps, set max_connections on the database side and rely on the pooler to queue excess connections rather than rejecting them

10. Over-Indexing

What it is: Every index you add speeds up reads on the indexed columns but slows down every INSERT, UPDATE, and DELETE on the table. Each write operation must update every index on the table. A table with 15 indexes has 15x the write amplification of an unindexed table.

How to spot it: Check for unused indexes:

SELECT
  indexrelname AS index_name,
  relname AS table_name,
  idx_scan AS times_used,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelid NOT IN (SELECT conindid FROM pg_constraint)
ORDER BY pg_relation_size(indexrelid) DESC;

This shows indexes that have never been used for a scan (excluding indexes that back constraints like primary keys and unique constraints). If an index hasn't been scanned in weeks or months, it's costing you write performance for no read benefit.

How to fix it:

• Drop unused indexes. Before dropping, verify the index isn't used by a periodic batch job that runs infrequently. Check over a full business cycle (at least a month).
• Consolidate overlapping indexes. If you have indexes on (a), (a, b), and (a, b, c), the composite indexes can serve queries that the single-column index handles. You may not need all three.
• Use partial indexes for queries that only need a subset of rows: CREATE INDEX idx_orders_pending ON orders (created_at) WHERE status = 'pending' is smaller and cheaper to maintain than a full index.

Catching These Patterns Automatically

Each of these patterns is identifiable from data that PostgreSQL already exposes. pg_stat_statements reveals query performance trends, pg_stat_user_tables shows vacuum and bloat status, pg_stat_user_indexes exposes index usage, and EXPLAIN ANALYZE gives per-query execution detail.

The challenge is collecting and analyzing this data continuously. Checking manually works for one-off investigations, but production databases need ongoing monitoring to catch regressions before users notice them. A query that performs fine today can degrade over a week as data volume grows or access patterns change.

Basira automates the collection of pg_stat_statements data, computes deltas to show trends over time, and uses AI to identify optimization opportunities. The agent setup is API-driven, and the $29/database/month pricing doesn't change based on query volume or table count.

But whether you use a tool or build your own monitoring, the patterns are the same. Learn to recognize these 10, and you'll solve the majority of PostgreSQL performance problems you encounter.