PostgreSQL Query Optimization: Indexes, Joins and Performance Tuning

Introduction

Seq Scan on orders. Cost: 0.00..45832.00. Rows: 1200000. That's your problem, right there, in one line.

A large share of PostgreSQL performance issues come from three places: missing indexes, stale statistics, and tables that have outgrown their autovacuum thresholds. More often than bad query structure or wrong join types. Missing indexes and bloated tables. The fixes are boring. Finding them is the skill.

So we start with EXPLAIN ANALYZE — the diagnostic. Then indexes — the fix. Then monitoring and maintenance — the thing that keeps it fixed. Everything here targets PostgreSQL 18, the current major release; version-specific behaviour is called out where older releases differ.

Understanding EXPLAIN ANALYZE

Without this, you're guessing. Every optimization conversation starts here.

EXPLAIN shows the planner's estimated plan without executing. EXPLAIN ANALYZE actually runs the query and reports real times and row counts. You almost always want the latter.

EXPLAIN ANALYZE
SELECT o.id, o.total_amount, o.created_at, p.name AS product_name
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
WHERE o.customer_id = 1042
  AND o.created_at >= '2026-01-01'
ORDER BY o.created_at DESC
LIMIT 20;

The output is a tree. Read it from the innermost (most indented) nodes outwards. This sample is the PostgreSQL 18 documentation's own example, run against the regression-test tables tenk1 and tenk2:

EXPLAIN ANALYZE SELECT *
FROM tenk1 t1, tenk2 t2
WHERE t1.unique1 < 10 AND t1.unique2 = t2.unique2;

                                    QUERY PLAN
---------------------------------------------------------------------------------
 Nested Loop  (cost=4.65..118.50 rows=10 width=488) (actual time=0.017..0.051 rows=10.00 loops=1)
   Buffers: shared hit=36 read=6
   ->  Bitmap Heap Scan on tenk1 t1  (cost=4.36..39.38 rows=10 width=244) (actual time=0.009..0.017 rows=10.00 loops=1)
         Recheck Cond: (unique1 < 10)
         Heap Blocks: exact=10
         Buffers: shared hit=3 read=5 written=4
         ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..4.36 rows=10 width=0) (actual time=0.004..0.004 rows=10.00 loops=1)
               Index Cond: (unique1 < 10)
               Index Searches: 1
               Buffers: shared hit=2
   ->  Index Scan using tenk2_unique2 on tenk2 t2  (cost=0.29..7.90 rows=1 width=244) (actual time=0.003..0.003 rows=1.00 loops=10)
         Index Cond: (unique2 = t1.unique2)
         Index Searches: 10
         Buffers: shared hit=24 read=6
 Planning:
   Buffers: shared hit=15 dirtied=9
 Planning Time: 0.485 ms
 Execution Time: 0.073 ms

The inner index scan shows loops=10: it ran once per row from the bitmap scan, so its real cost is 10 × 0.003 ms. Estimated rows=10 matches actual rows=10.00, so the statistics are good. Here's what matters at each node:

Seq Scan on a large table with a WHERE clause. Red flag. Every row is being read. You want Index Scan.

The "actual time" numbers are milliseconds of real time and are averages per loop. The first number is the actual startup time (until the node returned its first row), the second is the time until it finished. Compare estimated rows to actual rows — a big gap means stale statistics. Run ANALYZE. In nested loop joins, check the "loops" count. The inner operation runs once per outer row, so multiply actual time by loops for the real cost. Sort method says "external merge Disk"? The sort spilled to disk because work_mem is too small.

The Buffers: lines show how many pages came from shared buffers (hit) versus outside them (read). Since PostgreSQL 18, EXPLAIN ANALYZE includes buffer statistics automatically; on 17 and earlier write EXPLAIN (ANALYZE, BUFFERS). Many reads relative to hits mean the data isn't cached.

Never run EXPLAIN ANALYZE on DELETE or UPDATE in production without wrapping in a transaction and rolling back. It actually executes the query.

Index Types and When to Use Them

Biggest lever you have. But the wrong index type is barely better than no index at all.

B-tree Indexes

The default. Equality, range, sorting. Right choice for the vast majority of columns.

-- Simple B-tree index on a frequently filtered column
CREATE INDEX idx_orders_customer_id
ON orders (customer_id);

-- Composite index for queries that filter by customer AND date range
CREATE INDEX idx_orders_customer_date
ON orders (customer_id, created_at DESC);

-- Partial index: only index rows that matter for your query
CREATE INDEX idx_orders_active_customer
ON orders (customer_id, created_at DESC)
WHERE status = 'active';

Column order in composite indexes matters. An index on (customer_id, created_at, status) works for filtering by customer_id alone, or customer_id + created_at. For a query that only filters on status, the classic rule is that the index can't help, because the leading column has no condition. PostgreSQL 18 relaxes this with B-tree skip scan: the planner may run repeated searches for each distinct leading value. That only pays off when the skipped leading columns have few distinct values, so don't design around it. If status-only queries matter, index status first or give it its own index, and confirm with EXPLAIN.

Partial Indexes

Criminally underused. A partial index covers only a subset of rows. Million-order table, but most queries filter status = 'pending' (maybe 50,000 rows)? A partial index gives you a tiny, focused index instead of one covering the entire table. Faster to scan, faster to maintain.

GIN Indexes

For columns with multiple values: arrays, JSONB, full-text search vectors. Querying JSONB? GIN. Full-text search? Also GIN.

-- GIN index for JSONB containment queries
CREATE INDEX idx_products_metadata
ON products USING gin (metadata jsonb_path_ops);

-- Now this query can use the index
SELECT * FROM products
WHERE metadata @> '{"category": "electronics", "in_stock": true}';

-- GIN index for full-text search
CREATE INDEX idx_articles_search
ON articles USING gin (to_tsvector('english', title || ' ' || body));

GiST Indexes

Geometric data, range types, PostGIS geospatial queries, nearest-neighbor searches. If you're not doing those things, skip GiST.

Over-indexing is a real cost too. PostgreSQL maintains every index on each INSERT, UPDATE, and DELETE, so indexes nobody uses slow down writes and take disk and cache space. Find candidates in pg_stat_user_indexes:

-- Indexes with no recorded scans since statistics were last reset
SELECT s.schemaname, s.relname AS table_name, s.indexrelname AS index_name,
       pg_size_pretty(pg_relation_size(s.indexrelid)) AS index_size,
       s.idx_scan
FROM pg_stat_user_indexes s
JOIN pg_index i ON i.indexrelid = s.indexrelid
WHERE s.idx_scan = 0
  AND NOT i.indisunique      -- keep unique and primary-key indexes
ORDER BY pg_relation_size(s.indexrelid) DESC;

Treat the result as a list to investigate, not to drop blindly. Unique and primary-key indexes enforce constraints even with zero scans. Counters restart when statistics are reset, so check stats_reset in pg_stat_database to see how much history you have. Each replica keeps its own statistics, so an index unused on the primary may serve read queries on a standby. Monthly or quarterly reports may not have run yet.

Join Optimization Strategies

Three join algorithms: nested loop (good when outer set is small, inner is indexed), hash join (good for equi-joins, bad when the hash table spills to disk), merge join (good when both sides come pre-sorted). PostgreSQL picks for you. Usually it picks well.

When it doesn't:

-- This query joins multiple tables; ensure the most
-- restrictive filter is applied first
SELECT
  c.name AS customer_name,
  p.name AS product_name,
  o.total_amount,
  s.tracking_number
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
LEFT JOIN shipments s ON s.order_id = o.id
WHERE o.created_at >= '2026-02-01'
  AND c.region = 'us-west'
  AND p.category = 'electronics'
ORDER BY o.created_at DESC
LIMIT 50;

Beyond about 8 joins, the planner can't evaluate every ordering. Falls back to heuristics. Sometimes misses.

If joins are slow, check in this order. Are statistics current? Run ANALYZE. Does any column have a skewed distribution? Raise its statistics target with ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000; and then ANALYZE orders; (default_statistics_target, default 100, is the server-wide default). And — this is surprisingly common — are foreign key columns actually indexed? PostgreSQL indexes the referenced primary key automatically, but not the referencing foreign-key column. Without that index, joins and cascading deletes on the child table can fall back to sequential scans.

Query Rewriting Techniques

Sometimes the fix is rewriting the query, not adding an index.

CTEs: Optimization Fences vs Inline

Since PostgreSQL 12, simple non-recursive CTEs are automatically inlined. They optimize like subqueries. Before 12, they were always materialized — an optimization fence the planner couldn't see through. If you need the old behavior, say MATERIALIZED explicitly:

-- Inlined CTE (PostgreSQL 12+): planner can optimize across the boundary
WITH recent_orders AS (
  SELECT id, customer_id, total_amount, created_at
  FROM orders
  WHERE created_at >= now() - INTERVAL '30 days'
)
SELECT c.name, sum(ro.total_amount) AS monthly_spend
FROM recent_orders ro
JOIN customers c ON c.id = ro.customer_id
WHERE c.region = 'eu-central'
GROUP BY c.name
ORDER BY monthly_spend DESC;

-- Materialized CTE: forces separate evaluation
WITH heavy_calculation AS MATERIALIZED (
  SELECT product_id, avg(unit_price) AS avg_price
  FROM order_items
  GROUP BY product_id
)
SELECT p.name, hc.avg_price
FROM heavy_calculation hc
JOIN products p ON p.id = hc.product_id
WHERE hc.avg_price > 100;

EXISTS vs IN

The old advice that EXISTS always beats IN doesn't hold in modern PostgreSQL. The planner usually turns both IN (subquery) and EXISTS into the same semi-join, so they often produce identical plans. Pick whichever reads better and check EXPLAIN if in doubt.

But the real issue is NOT IN vs NOT EXISTS. NOT IN has a NULL gotcha: if the subquery returns any NULL, the entire NOT IN evaluates to false for every row. Not a performance bug. A correctness bug. Use NOT EXISTS.

Avoiding Correlated Subqueries

Correlated subquery runs once per outer row. Million-row outer query means a million subquery executions. Rewrite as a join. Or a lateral join if you need to reference outer columns.

Connection Pooling and Configuration

Optimized queries on a badly configured instance will still be slow. The defaults are deliberately conservative so PostgreSQL starts on small machines: shared_buffers defaults to 128 MB and work_mem to 4 MB. A dedicated database server usually needs more.

Key Configuration Parameters

shared_buffers: 25% of total RAM. On a 64 GB server, 16 GB. Don't go higher — the OS file cache needs room too.

work_mem: memory per sort or hash operation before spilling to disk. Default is 4 MB, often too small for analytical queries. Raise it gradually (for example 16-64 MB), or set it higher per session for heavy reports. But this is per-operation, not per-query — a complex query with multiple sorts could use several multiples. Multiply by expected concurrent connections before committing.

effective_cache_size: allocates nothing. Just tells the planner how much total cache is available so it can decide between index scans and seq scans. Set to 75% of total RAM.

random_page_cost. Default is 4.0. Assumes spinning disks. On SSDs, drop it to 1.1. This one change can shift the planner from sequential scans to index scans on many queries at once. Test it on a copy of production first and compare plans.

Connection Pooling with PgBouncer

PostgreSQL spawns a process per connection. 500 concurrent connections means 500 processes. Memory adds up fast.

PgBouncer sits between app and database. 500 app connections, 50 actual PostgreSQL connections. Transaction pooling works for most web apps. Prepared statements no longer force session pooling: since PgBouncer 1.21 it tracks protocol-level named prepared statements in transaction mode, and since 1.24 max_prepared_statements defaults to 200, so it is on out of the box. SQL-level PREPARE/EXECUTE is not covered; it still needs session pooling. Session-level features such as SET without LOCAL, advisory locks and LISTEN also need session mode.

Partitioning Large Tables

At hundreds of millions of rows, even indexed queries slow down. Indexes themselves bloat. VACUUM takes forever. Partitioning splits one logical table into smaller physical ones.

Range partitioning by date. Most common. Best place to start:

-- Create a partitioned table by date range
CREATE TABLE events (
  id bigserial,
  event_type text NOT NULL,
  payload jsonb,
  created_at timestamptz NOT NULL DEFAULT now()
) PARTITION BY RANGE (created_at);

-- Create monthly partitions
CREATE TABLE events_2026_01 PARTITION OF events
  FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');

CREATE TABLE events_2026_02 PARTITION OF events
  FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');

CREATE TABLE events_2026_03 PARTITION OF events
  FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');

-- Create indexes on each partition (inherited automatically in PG 11+)
CREATE INDEX idx_events_created_at ON events (created_at);
CREATE INDEX idx_events_type ON events (event_type);

Query for February 2026 data? Only scans events_2026_02. Every other month ignored. That's partition pruning.

Dropping old data becomes instant too. Detach and drop a partition versus deleting millions of rows and dealing with the resulting bloat. Night and day.

But don't partition just because a table is large. It adds schema complexity. Cross-partition queries can be slower. Consider it past 100 million rows, or when you have a clear time-based retention policy.

Monitoring and Maintenance

Tables grow. Data distributions shift. New queries appear. Without monitoring, performance degrades silently until someone files a ticket.

pg_stat_statements

Install this extension. If you install nothing else, install this. Tracks execution stats for every normalized query: total time, call count, rows returned, buffer usage. It ships with PostgreSQL but must be preloaded and created first:

-- 1. postgresql.conf (needs a server restart):
--    shared_preload_libraries = 'pg_stat_statements'

-- 2. In each database you want to inspect:
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- 3. Optional: clear the counters to start a fresh measurement window
SELECT pg_stat_statements_reset();

Managed services (RDS, Cloud SQL, Azure) usually preload it already; you only run CREATE EXTENSION. Then:

-- Find the top 10 slowest queries by total execution time
SELECT
  round(total_exec_time::numeric, 2) AS total_time_ms,
  calls,
  round(mean_exec_time::numeric, 2) AS avg_time_ms,
  round((100 * total_exec_time / sum(total_exec_time) OVER())::numeric, 2) AS pct,
  substring(query, 1, 120) AS short_query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Run this weekly, alongside the unused-index query above. A scheduled job that saves the output makes trends visible. Often one or two queries account for most of the total execution time. Fix those and everything else speeds up — less CPU contention, less I/O pressure.

VACUUM and Autovacuum

Dead tuples. MVCC means updated and deleted rows stick around until VACUUM cleans them. Let them accumulate and your tables bloat. Queries slow down scanning through garbage.

Autovacuum handles this, but the defaults are conservative. The default autovacuum_vacuum_scale_factor of 0.2 means autovacuum triggers when 20% of rows are dead. On a 100-million-row table? That's 20 million dead rows before cleanup starts. Lower it to 0.01 or 0.005 for large tables.

autovacuum_vacuum_cost_delay throttles autovacuum. Default is 2ms. If autovacuum can't keep up, drop to 0 during off-peak hours. Keep autovacuum_analyze_threshold aggressive for tables where data distribution changes frequently.

Monitor bloat with pgstattuple. When it's severe, regular VACUUM won't reclaim space. VACUUM FULL locks the table. pg_repack does it online.

Keeping Statistics Fresh

Stale statistics produce bad plans. Run ANALYZE manually after bulk loads, schema changes, anything that significantly shifts data distribution. If EXPLAIN ANALYZE shows estimated rows wildly different from actual rows, this is almost certainly the cause.

Performance work is never done. But pg_stat_statements tells you where to look, and that's most of the battle.

Start Here

If you only have an afternoon, work through this in order:

1. Turn on visibility. Preload and create pg_stat_statements, then rank queries by total_exec_time.
2. Explain the top offenders. Run EXPLAIN ANALYZE (inside BEGIN … ROLLBACK for writes). Look for sequential scans on large filtered tables, large estimated-versus-actual row gaps, high loops counts and sorts spilling to disk.
3. Fix statistics before indexes. Run ANALYZE on tables with bad estimates, and raise per-column statistics targets for skewed columns.
4. Add the missing indexes. Foreign-key columns first, then composite indexes that match your WHERE and ORDER BY column order, and partial indexes for hot subsets.
5. Prune carefully. Review zero-scan indexes, keeping unique and primary-key indexes and checking replicas and the statistics reset date first.
6. Tune the obvious settings. shared_buffers around 25% of RAM, effective_cache_size around 75%, random_page_cost about 1.1 on SSDs, and a measured increase to work_mem.
7. Pool connections. PgBouncer in transaction mode; protocol-level prepared statements work there on 1.21+.
8. Keep autovacuum ahead. Lower autovacuum_vacuum_scale_factor on large tables and watch dead tuples in pg_stat_user_tables.
9. Partition only with a reason. Very large tables with time-based queries or retention policies.

Re-measure with pg_stat_statements after each change, so you know which one actually helped.