PostgreSQL Query Optimization: 10 Techniques That Actually Work

Most PostgreSQL performance advice is either too obvious ("add an index") or too abstract ("tune your queries"). Here are 10 specific techniques with real SQL examples, ordered by how much impact they typically have. Each one includes concrete before/after evidence so you can measure the improvement yourself.

---

1. Read EXPLAIN ANALYZE Output Properly

Every optimization starts with understanding what the database is actually doing. Most people run EXPLAIN ANALYZE, glance at the total cost, and miss the real signals.

Here is a query that looks simple but performs terribly:

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.order_id, o.total_amount, c.customer_name
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE o.status = 'pending'
  AND o.created_at > now() - interval '30 days';

Nested Loop  (cost=0.43..28456.12 rows=15 width=52) (actual time=0.089..342.671 rows=847 loops=1)
  ->  Seq Scan on orders o  (cost=0.00..24831.00 rows=15 width=24) (actual time=0.045..298.123 rows=847 loops=1)
        Filter: ((status = 'pending') AND (created_at > ...))
        Rows Removed by Filter: 4999153
        Buffers: shared hit=12104 read=66327
  ->  Index Scan using customers_pkey on customers c  (cost=0.43..0.48 rows=1 width=36) (actual time=0.008..0.008 rows=1 loops=847)
        Buffers: shared hit=2541
Planning Time: 0.184 ms
Execution Time: 342.891 ms

Three things jump out:

Sequential scan discarding 99.98% of rows. The orders scan found 847 rows after filtering out 4,999,153. That is a textbook missing index.

Estimated vs actual row mismatch. The planner estimated 15 rows but got 847. Stale statistics are causing a bad join strategy. A hash join would be more efficient here, but the planner chose a nested loop because it thought only 15 rows would come out.

Buffer reads dominate. shared read=66327 means PostgreSQL pulled 66,327 pages (about 518 MB) from disk. After adding the right index, this drops to under 100 pages.

The three numbers to check on every EXPLAIN node: the ratio of rows to Rows Removed by Filter, the gap between estimated and actual row counts, and shared read vs shared hit in the Buffers output.

---

2. Fix Missing Indexes (The Right Ones)

Adding indexes everywhere is not the answer. Every index slows writes, consumes memory, and needs vacuuming. The goal is to add the indexes that eliminate the most sequential scan work with the least write overhead.

Start with the system catalogs:

-- Tables doing the most sequential scanning
SELECT
    schemaname,
    relname AS table_name,
    seq_scan AS sequential_scans,
    seq_tup_read AS rows_read_by_seq_scans,
    idx_scan AS index_scans,
    n_live_tup AS estimated_live_rows
FROM pg_stat_user_tables
WHERE seq_scan > 100
  AND n_live_tup > 10000
ORDER BY seq_tup_read DESC
LIMIT 20;

This shows which tables are hemorrhaging performance through full scans. A table with 5 million rows and 10,000 sequential scans per day is reading up to 50 billion rows through brute force.

Now find the specific columns being filtered:

-- Indexes that exist but are never used (candidates for removal)
SELECT
    schemaname,
    relname AS table_name,
    indexrelname AS index_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 schemaname = 'public'
ORDER BY pg_relation_size(indexrelid) DESC;

Removing unused indexes frees write throughput and memory for the indexes that matter.

Tools like myDBA.dev automate this analysis -- the index advisor analyzes your actual query workload and recommends specific indexes with estimated impact, so you do not have to piece together pg_stat_user_tables, pg_stat_statements, and EXPLAIN output manually.

---

3. Use Partial Indexes for Skewed Data

PostgreSQL's partial indexes are one of its most underused features. When a WHERE clause eliminates 90% or more of rows, a partial index handles only the rows you actually query.

Consider a table with 10 million orders where 97% are completed:

-- Full index: 214 MB, covers all 10M rows
CREATE INDEX idx_orders_status ON orders (status);

-- Partial index: 6.4 MB, covers only the 300K active rows
CREATE INDEX idx_orders_active
    ON orders (status, created_at)
    WHERE status IN ('pending', 'processing', 'shipped');

Before (full index):

Index Scan using idx_orders_status on orders  (cost=0.43..18924.56 rows=312 width=48) (actual time=0.052..14.891 rows=298 loops=1)
  Buffers: shared hit=892 read=156

After (partial index):

Index Scan using idx_orders_active on orders  (cost=0.29..124.56 rows=312 width=48) (actual time=0.031..0.487 rows=298 loops=1)
  Buffers: shared hit=43

The partial index is 33x smaller, fits entirely in shared_buffers, and reads 96% fewer pages. Writes to completed orders -- the vast majority of updates -- no longer touch this index at all.

---

4. Add Covering Indexes to Eliminate Heap Lookups

A standard index scan has two steps: find the row pointer in the index, then fetch the actual row from the heap (table data) to get the remaining columns. A covering index includes extra columns so PostgreSQL can answer the query from the index alone.

-- Standard index: requires heap lookup for customer_name and total_amount
CREATE INDEX idx_orders_customer ON orders (customer_id);

-- Covering index: index-only scan, no heap lookup needed
CREATE INDEX idx_orders_customer_covering
    ON orders (customer_id)
    INCLUDE (order_date, total_amount, status);

The difference shows up clearly in EXPLAIN:

EXPLAIN (ANALYZE, BUFFERS)
SELECT order_date, total_amount, status
FROM orders
WHERE customer_id = 42;

Before (standard index):

Index Scan using idx_orders_customer on orders  (cost=0.43..56.12 rows=12 width=28) (actual time=0.034..0.089 rows=12 loops=1)
  Buffers: shared hit=15

After (covering index):

Index Only Scan using idx_orders_customer_covering on orders  (cost=0.43..4.12 rows=12 width=28) (actual time=0.021..0.028 rows=12 loops=1)
  Heap Fetches: 0
  Buffers: shared hit=4

Heap Fetches: 0 means no table access at all. This matters most on wide tables where heap pages are large and scattered. Note: for index-only scans to work efficiently, the visibility map must be up to date, which means regular vacuuming.

Use INCLUDE columns (not key columns) for data you are retrieving but not filtering or sorting on. Key columns affect index ordering and size. INCLUDE columns are stored in leaf pages only.

---

5. Fix N+1 Queries

The most common performance problem in applications using ORMs. The pattern: fetch a list of parent records, then loop through each one to fetch its children.

# The N+1 problem: 1 query + N queries
orders = db.query("SELECT * FROM orders WHERE status = 'pending'")  # 1 query
for order in orders:
    items = db.query("SELECT * FROM order_items WHERE order_id = %s", order.id)  # N queries

With 500 pending orders, this executes 501 SQL statements. Each one has network round-trip overhead, query planning overhead, and transaction overhead.

The fix is a single query with a JOIN:

SELECT
    o.order_id,
    o.status,
    o.created_at,
    oi.item_id,
    oi.product_name,
    oi.quantity,
    oi.unit_price
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
WHERE o.status = 'pending';

For cases where you need the data structured as parent-with-children (not a flat join), use LATERAL JOIN:

SELECT
    o.order_id,
    o.status,
    item_summary.total_items,
    item_summary.order_total
FROM orders o
CROSS JOIN LATERAL (
    SELECT
        count(*) AS total_items,
        sum(quantity * unit_price) AS order_total
    FROM order_items oi
    WHERE oi.order_id = o.order_id
) AS item_summary
WHERE o.status = 'pending';

Or use EXISTS when you only need to check for the presence of related rows:

-- Instead of fetching children to check if they exist
SELECT o.order_id, o.status
FROM orders o
WHERE o.status = 'pending'
  AND EXISTS (
    SELECT 1 FROM order_items oi
    WHERE oi.order_id = o.order_id
      AND oi.unit_price > 100
);

EXISTS short-circuits after finding the first match. A JOIN would produce duplicate parent rows for each matching child.

---

6. Use CTEs Wisely

Before PostgreSQL 12, every CTE was materialized -- computed in full and stored in a temporary buffer, acting as an optimization fence. Since PostgreSQL 12, the planner can inline CTEs, treating them like subqueries. But sometimes you want materialization.

Let the planner inline (default in PG 12+):

-- The planner will push the WHERE clause into the CTE
WITH recent_orders AS (
    SELECT order_id, customer_id, total_amount, created_at
    FROM orders
    WHERE created_at > now() - interval '7 days'
)
SELECT ro.order_id, c.customer_name, ro.total_amount
FROM recent_orders ro
JOIN customers c ON c.customer_id = ro.customer_id
WHERE ro.total_amount > 500;

The planner combines both filters (created_at and total_amount) and can use a single index scan.

Force materialization when it helps:

-- Expensive subquery referenced multiple times -- materialize it once
WITH MATERIALIZED monthly_stats AS (
    SELECT
        customer_id,
        count(*) AS order_count,
        sum(total_amount) AS total_spent
    FROM orders
    WHERE created_at > now() - interval '30 days'
    GROUP BY customer_id
)
SELECT ms.customer_id, ms.order_count, ms.total_spent,
       ms.total_spent / ms.order_count AS avg_order_value
FROM monthly_stats ms
WHERE ms.order_count > 5

UNION ALL

SELECT ms.customer_id, ms.order_count, ms.total_spent, 0
FROM monthly_stats ms
WHERE ms.order_count <= 5;

Without MATERIALIZED, the planner might compute the aggregation twice. With it, the CTE runs once and both references read from the materialized result.

Rule of thumb: let the planner decide by default. Use MATERIALIZED when a CTE is referenced multiple times and contains expensive aggregation or sorting.

---

7. Replace OFFSET Pagination with Keyset Pagination

OFFSET is the default pagination approach in most applications, and it scales terribly. To return page 1000 with 20 rows per page, PostgreSQL must scan and discard 19,980 rows before returning the 20 you want.

-- OFFSET pagination: gets worse as page number increases
SELECT order_id, customer_id, total_amount, created_at
FROM orders
WHERE status = 'completed'
ORDER BY created_at DESC
LIMIT 20 OFFSET 19980;

Limit  (cost=48921.45..48922.12 rows=20 width=48) (actual time=287.445..287.461 rows=20 loops=1)
  ->  Sort  (cost=48871.67..49812.34 rows=376267 width=48) (actual time=241.012..287.234 rows=20000 loops=1)
        Sort Key: created_at DESC
        Sort Method: top-N heapsort  Memory: 4218kB

PostgreSQL sorted hundreds of thousands of rows just to return 20. Page 1 takes 2 ms. Page 1000 takes 287 ms. Page 10000 takes seconds.

Keyset pagination uses a WHERE clause instead of OFFSET:

-- Keyset pagination: consistent performance regardless of page depth
SELECT order_id, customer_id, total_amount, created_at
FROM orders
WHERE status = 'completed'
  AND (created_at, order_id) < ('2025-06-15 14:30:00', 984523)
ORDER BY created_at DESC, order_id DESC
LIMIT 20;

Limit  (cost=0.56..12.34 rows=20 width=48) (actual time=0.041..0.067 rows=20 loops=1)
  ->  Index Scan Backward using idx_orders_status_created on orders  (cost=0.56..221456.23 rows=376267 width=48) (actual time=0.039..0.061 rows=20 loops=1)
        Index Cond: (status = 'completed')
        Filter: (ROW(created_at, order_id) < ROW('2025-06-15 14:30:00', 984523))

Constant 0.067 ms regardless of how deep you paginate. The trick is passing the last row's sort values as the cursor for the next page.

The index that makes this work:

CREATE INDEX idx_orders_status_created
    ON orders (status, created_at DESC, order_id DESC);

The trade-off: keyset pagination cannot jump to arbitrary page numbers. You can go forward and backward, but not "go to page 500." For most APIs and infinite-scroll UIs, this is not a limitation.

---

8. Update Statistics When They Are Stale

The PostgreSQL query planner chooses execution strategies based on table statistics -- row counts, value distributions, most common values, null fractions. When statistics are stale, the planner makes bad decisions.

Symptoms of stale statistics:

• Nested loops on large joins (planner thinks one side is tiny)
• Sequential scans on indexed columns (planner thinks the table is small)
• Sort operations spilling to disk (planner underestimates result size)

-- Check when tables were last analyzed
SELECT
    schemaname,
    relname AS table_name,
    last_analyze,
    last_autoanalyze,
    n_live_tup AS estimated_live_rows,
    n_mod_since_analyze AS modifications_since_analyze
FROM pg_stat_user_tables
WHERE n_mod_since_analyze > n_live_tup * 0.1
ORDER BY n_mod_since_analyze DESC;

Tables where modifications exceed 10% of live rows likely have stale statistics.

Manual fix:

-- Analyze a specific table
ANALYZE orders;

-- Analyze specific columns (faster for wide tables)
ANALYZE orders (status, created_at, customer_id);

Tune autoanalyze for high-churn tables:

-- Lower the threshold for tables that change rapidly
ALTER TABLE orders SET (
    autovacuum_analyze_scale_factor = 0.02,  -- default is 0.1
    autovacuum_analyze_threshold = 1000       -- default is 50
);

Extended statistics for correlated columns:

When two columns are correlated (e.g., city and zip_code), PostgreSQL assumes independence and dramatically underestimates the selectivity of combined filters. Extended statistics fix this:

CREATE STATISTICS orders_city_zip (dependencies, ndistinct, mcv)
    ON city, zip_code FROM orders;

ANALYZE orders;

After creating extended statistics, the planner uses the correlation data to produce accurate row estimates for queries filtering on both columns.

myDBA.dev's plan regression detection catches when stale statistics cause a query plan to change -- alerting you before users notice the degradation.

---

9. Tune Parallel Query Execution

PostgreSQL can parallelize sequential scans, hash joins, aggregates, and sorts across multiple workers. But the defaults are conservative, and parallel execution adds overhead that is not always worth it.

Key parameters:

-- Check current settings
SHOW max_parallel_workers_per_gather;  -- default: 2
SHOW min_parallel_table_scan_size;     -- default: 8MB
SHOW parallel_tuple_cost;             -- default: 0.1
SHOW parallel_setup_cost;             -- default: 1000

When parallel queries help:

-- Large aggregation over millions of rows: parallel is 4x faster
EXPLAIN (ANALYZE)
SELECT status, count(*), avg(total_amount)
FROM orders
WHERE created_at > now() - interval '1 year'
GROUP BY status;

Finalize GroupAggregate  (cost=152345.67..152346.12 rows=5 width=44) (actual time=312.456..312.489 rows=5 loops=1)
  ->  Gather Merge  (cost=152345.67..152345.89 rows=10 width=44) (actual time=312.234..312.467 rows=15 loops=1)
        Workers Planned: 2
        Workers Launched: 2
        ->  Sort  (cost=151345.64..151345.65 rows=5 width=44) (actual time=298.123..298.126 rows=5 loops=3)
              ->  Partial HashAggregate  (cost=151345.56..151345.61 rows=5 width=44) (actual time=298.089..298.095 rows=5 loops=3)

Three workers (1 leader + 2 workers) each scan a third of the table and compute partial aggregates, then the leader merges the results.

When parallel queries hurt:

Small result sets with index scans. The setup cost of launching workers (process creation, shared memory allocation) exceeds the time saved. If your query already runs in under 10 ms with an index scan, parallel execution makes it slower.

Tuning for analytical workloads:

-- Allow more workers for large scans
SET max_parallel_workers_per_gather = 4;

-- Reduce the table size threshold for triggering parallel scans
SET min_parallel_table_scan_size = '1MB';

-- Lower the per-tuple cost so the planner favors parallel plans
SET parallel_tuple_cost = 0.01;

Set these at the session level for reporting queries. Do not change them globally unless your workload is primarily analytical.

---

10. Optimize JSONB Queries

JSONB is powerful but easy to misuse. Without proper indexing, every JSONB query is a sequential scan that parses every document in the table.

GIN index for containment queries:

-- Index the entire JSONB column
CREATE INDEX idx_events_data_gin ON events USING gin (event_data);

-- Now containment queries use the index
SELECT event_id, event_data
FROM events
WHERE event_data @> '{"type": "purchase", "region": "us-east"}';

The @> containment operator is the key. It checks if the left JSONB contains the right JSONB, and GIN indexes support it efficiently.

Expression index for specific paths:

When you always filter on the same JSONB path, an expression index is smaller and faster than a full GIN index:

-- Expression index on a specific path
CREATE INDEX idx_events_user_id
    ON events ((event_data->>'user_id'));

-- This query uses the expression index
SELECT event_id, event_data
FROM events
WHERE event_data->>'user_id' = '12345';

Avoid the common mistake:

-- This CANNOT use a GIN index (wrong operator)
SELECT * FROM events WHERE event_data->>'type' = 'purchase';

-- This CAN use a GIN index (containment operator)
SELECT * FROM events WHERE event_data @> '{"type": "purchase"}';

The ->>'key' = 'value' pattern extracts a text value and compares it. GIN indexes do not support this operator. You need either @> for GIN or a dedicated expression index for the path.

Type-aware expression indexes:

-- Cast for numeric comparisons
CREATE INDEX idx_events_amount
    ON events (((event_data->>'amount')::numeric));

SELECT * FROM events
WHERE (event_data->>'amount')::numeric > 100.00;

Without the cast in both the index and the query, PostgreSQL does a string comparison, and "9" is greater than "100" lexicographically.

---

Bonus: Monitor Continuously

The best optimization is one you catch before it reaches production. Individually, each of these techniques is a point-in-time fix. Without continuous monitoring, new queries arrive, data distributions shift, and yesterday's fast query becomes tomorrow's bottleneck.

What continuous monitoring catches:

• Plan regressions: A query that was using an index scan starts doing sequential scans after a data load changes the statistics.
• Missing indexes on new queries: A new feature ships with an unindexed filter column. Sequential scans creep up over days as data grows.
• Index bloat: An index that was 50 MB grows to 2 GB after months of updates without reindexing.
• N+1 patterns: Query sample analysis reveals 500 identical queries per request, differing only in the WHERE value.

Automated EXPLAIN plan capture, query performance trending, and plan regression detection turn reactive firefighting into proactive optimization. Instead of waiting for users to report slowness, you get an alert the moment a query plan degrades.

---

Quick Reference

Technique	When to Use	Expected Impact
Read EXPLAIN ANALYZE	Always, first step	Identifies the real problem
Fix missing indexes	Seq scan / idx_scan ratio > 10:1	10-1000x faster reads
Partial indexes	Skewed columns, 90%+ filtered out	10-50x smaller index
Covering indexes	Frequent lookups returning few columns	2-5x fewer buffer reads
Fix N+1 queries	ORM patterns, loop-based fetching	10-500x fewer round trips
CTEs (MATERIALIZED)	Expensive CTE referenced multiple times	Avoids duplicate computation
Keyset pagination	Deep pagination, infinite scroll	Constant time vs linear degradation
Update statistics	Row estimate mismatches in EXPLAIN	Correct plan selection
Parallel query tuning	Large analytical scans, aggregations	2-4x on multi-core
JSONB optimization	@> queries, path-specific filters	Index scan vs full table scan

Every technique has a concrete signal in EXPLAIN output. Start with the query that matters most, read its plan, identify which pattern applies, and measure the before/after difference. Repeat.