How to Optimize Query Performance in PostgreSQL

Every time I’ve been called in to fix a “PostgreSQL is slow” problem, the actual cause has almost never been PostgreSQL itself — it’s been a missing index, a bad query pattern, stale statistics, or a schema design decision that made a certain query fundamentally expensive no matter how well-tuned the database was. Over the years I’ve developed a fairly consistent process for diagnosing and fixing performance problems, and I want to walk through it here: how to actually read EXPLAIN output, the indexing strategies that matter most, and the habits that keep queries fast as data grows.

Start With EXPLAIN ANALYZE, Always

Before changing anything, I look at what the query planner is actually doing.

EXPLAIN ANALYZE
SELECT * FROM orders
WHERE customer_id = 42 AND status = 'shipped'
ORDER BY created_at DESC
LIMIT 20;

EXPLAIN alone shows the planned execution strategy without running the query; EXPLAIN ANALYZE actually executes it and shows real timing and row counts alongside the plan. I always use ANALYZE when I can afford to actually run the query (be careful with this on write queries — wrap them in a transaction and roll back, or use EXPLAIN without ANALYZE if you can’t safely execute it).

A few things I look for immediately in the output:

• Seq Scan vs. Index Scan. A sequential scan on a large table, where I’d expect an index to be used, is usually the first red flag.
• Estimated rows vs. actual rows. A huge mismatch between the planner’s row estimate and the actual row count is a strong sign that table statistics are stale or a column’s data distribution is throwing off the planner’s assumptions.
• Where the time is actually going. Nested loops with a large number of iterations, sorts that spill to disk, or a slow filter step applied after a broad scan are all worth investigating individually.

Reading the Plan Tree

PostgreSQL’s plan output is a tree, and it executes from the innermost nodes outward. A plan like this:

Limit  (cost=1042.34..1042.39 rows=20 width=120) (actual time=45.221..45.230 rows=20 loops=1)
  ->  Sort  (cost=1042.34..1044.84 rows=1000 width=120) (actual time=45.219..45.222 rows=20 loops=1)
        Sort Key: created_at DESC
        Sort Method: top-N heapsort  Memory: 34kB
        ->  Seq Scan on orders  (cost=0.00..1020.00 rows=1000 width=120) (actual time=0.015..40.112 rows=980 loops=1)
              Filter: (customer_id = 42 AND status = 'shipped')
              Rows Removed by Filter: 49020

tells me a lot: the planner is doing a full sequential scan of orders, filtering out 49,020 rows to find 980 matches, then sorting all of those in memory before applying the limit. That Rows Removed by Filter line is the giveaway — it’s scanning far more data than it needs, which is exactly what a good index on (customer_id, status, created_at) would fix.

Indexing Strategy

The Basics: B-Tree Indexes

Most of the time, a standard B-tree index is what you want:

CREATE INDEX idx_orders_customer_status ON orders (customer_id, status);

For the query above, a composite index covering both filter columns and the sort column does even more work:

CREATE INDEX idx_orders_customer_status_created
ON orders (customer_id, status, created_at DESC);

With this index, PostgreSQL can find matching rows directly via the index, already in the right sort order, avoiding both the sequential scan and the separate sort step entirely.

Column Order Matters in Composite Indexes

A composite index on (a, b, c) is efficient for queries that filter on a alone, a and b, or a, b, and c — but not efficient for a query that filters on b alone, since a B-tree index only helps when you’re using a prefix of its columns in order. When I design a composite index, I put the columns most commonly used with equality filters first, followed by range filters, followed by sort columns — that ordering generally gets the most mileage out of a single index.

Partial Indexes

If a query pattern only ever cares about a subset of rows, a partial index can be dramatically smaller and faster than indexing the whole table:

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

This is smaller, faster to maintain, and faster to query than a full index, as long as the query’s WHERE clause matches (or is a subset of) the partial index’s condition.

Expression Indexes

If you frequently filter or sort on the result of a function or expression, index the expression itself:

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

Then queries like WHERE lower(email) = 'jane@example.com' can use the index — a plain index on email wouldn’t help here, since the expression wrapping the column prevents PostgreSQL from using a normal index on the raw column.

GIN and GiST Indexes

For array columns, JSONB, full-text search vectors, or geospatial data, B-tree indexes aren’t the right tool. GIN indexes suit containment and full-text queries; GiST indexes suit geometric and range-type data, as well as certain full-text and trigram use cases. I cover these in more depth in my articles on JSON data types and full-text search.

Don’t Over-Index

It’s tempting to add an index for every query pattern you see, but every index adds write overhead — every INSERT, UPDATE, or DELETE has to maintain every index on the table, and unused indexes just cost you disk space and write throughput for nothing. I periodically check for unused indexes:

SELECT schemaname, relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

Indexes with zero (or very low) scans, especially large ones, are strong candidates for removal — assuming they’ve been in place long enough to have seen realistic traffic patterns first.

Statistics and ANALYZE

PostgreSQL’s query planner relies on statistics about the data distribution in each table to choose a good plan. If those statistics are stale — say, after a large bulk load — the planner can make bad decisions.

ANALYZE orders;

Autovacuum normally handles this automatically, but after a large bulk import or a schema change, I run ANALYZE manually rather than waiting for autovacuum’s next scheduled pass. You can check when a table was last analyzed:

SELECT relname, last_analyze, last_autoanalyze
FROM pg_stat_user_tables
WHERE relname = 'orders';

For columns with especially skewed or unusual data distributions, increasing the statistics target can help the planner make better decisions:

ALTER TABLE orders ALTER COLUMN status SET STATISTICS 500;
ANALYZE orders;

The default statistics target is 100; bumping it for specific columns that the planner seems to consistently misjudge (visible via a big estimate-vs-actual mismatch in EXPLAIN ANALYZE) is a targeted fix rather than a blanket setting change.

Avoiding Common Query Anti-Patterns

SELECT * when you only need a few columns. Beyond the obvious network and memory overhead, pulling unnecessary columns can also prevent PostgreSQL from using an index-only scan, which is a scan that can satisfy the entire query directly from the index without touching the underlying table at all.

Functions wrapped around indexed columns in WHERE clauses. WHERE date(created_at) = '2026-01-01' prevents index usage on a plain index over created_at, since the planner can’t reason through the function without an expression index. Rewrite as a range: WHERE created_at >= '2026-01-01' AND created_at < '2026-01-02'.

OR conditions across different columns. These can sometimes prevent efficient index usage compared to rewriting as a UNION of two indexed queries, though PostgreSQL’s planner has gotten better at this over the years — worth checking with EXPLAIN rather than assuming.

NOT IN with a subquery that might contain NULL. This is a classic correctness trap, not just a performance one — if the subquery can return a NULL, NOT IN will unexpectedly return no rows at all. Prefer NOT EXISTS for this pattern.

N+1 query patterns from the application layer. Not a PostgreSQL-specific problem, but I see it constantly — a loop in application code issuing one query per row instead of a single batched query or JOIN. No amount of database-side tuning fixes an application making a thousand round trips instead of one.

Index-Only Scans

When a query can be fully satisfied by the columns in an index — including any filter and select columns — PostgreSQL can skip the heap (table) entirely and answer directly from the index. This is significantly faster.

CREATE INDEX idx_orders_covering ON orders (customer_id, status) INCLUDE (created_at, total);

The INCLUDE clause adds columns to the index for retrieval purposes without making them part of the index’s sort/search key — useful for turning a normal index scan into an index-only scan for queries that select those extra columns. Keep in mind index-only scans also depend on how up-to-date the table’s visibility map is, which autovacuum maintains — a table that isn’t being vacuumed regularly won’t get the full benefit even with the right index in place.

Using pg_stat_statements to Find What Actually Matters

Rather than guessing which queries need attention, I let the database tell me:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT query, calls, total_exec_time, mean_exec_time, rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

Sorting by total_exec_time shows me where the cumulative time is actually going — sometimes it’s one slow query, and sometimes it’s a moderately fast query being run an enormous number of times, which is a very different kind of problem to fix (batching or caching, rather than indexing).

Common Use Cases for Deliberate Optimization

• Dashboard and reporting queries that aggregate over large tables and benefit heavily from covering indexes or materialized views.
• High-traffic lookup queries (fetching a user by ID or email) where index-only scans matter most.
• Search and filter UIs with many optional filter combinations, where partial and composite indexes need careful design.
• Bulk data pipelines where statistics need a manual ANALYZE after large loads rather than waiting on autovacuum.

Troubleshooting Tips

A query was fast yesterday and slow today with no code changes. Check for stale statistics (last_analyze), table bloat, or a data distribution shift (e.g., far more rows now match a previously rare filter value, changing which plan is actually optimal).

EXPLAIN shows an index scan but it’s still slow. Check Rows Removed by Filter — the index might be narrowing the search but still leaving a lot of post-filtering work, which usually means a more targeted composite index is needed.

Query is fast in isolation but slow under load. This often points to lock contention, connection pool exhaustion, or CPU/IO contention from other concurrent queries rather than the query plan itself — check pg_stat_activity and system-level resource usage alongside the query plan.

Planner picks a “bad” plan despite a seemingly good index existing. Compare the planner’s row estimates to actual counts in EXPLAIN ANALYZE. If they’re wildly off, it’s almost always a statistics problem — try ANALYZE and, if needed, bump STATISTICS on the relevant column.

Best Practices

1. Always start with EXPLAIN ANALYZE rather than guessing at the fix.
2. Design composite indexes around actual query patterns, with equality columns first, then range/sort columns.
3. Use partial and expression indexes when a query pattern only cares about a subset of rows or a transformed value.
4. Periodically audit for unused indexes and remove them — every index has a write-side cost.
5. Keep statistics fresh, especially after bulk loads, and consider raising STATISTICS on columns the planner consistently misjudges.
6. Use pg_stat_statements to prioritize optimization work by actual cumulative impact, not guesswork.
7. Rewrite anti-patterns — functions wrapping indexed columns, NOT IN with nullable subqueries, unnecessary SELECT * — as you find them.
8. Consider index-only scans for hot, frequently-run queries by using INCLUDE columns where it makes sense.

A Real-World Example: Chasing Down a Slow Dashboard Query

Let me walk through an actual optimization I did recently, since it shows the whole process in sequence rather than as isolated tips. A dashboard query aggregating monthly revenue per customer had grown to take over eight seconds as the orders table passed ten million rows.

The original query:

SELECT customer_id, date_trunc('month', created_at) AS month, sum(total)
FROM orders
WHERE status = 'completed'
GROUP BY customer_id, date_trunc('month', created_at);

EXPLAIN ANALYZE showed a sequential scan over the entire ten-million-row table, filtering down to completed orders, then a hash aggregate. The status = 'completed' filter was doing real work — only about 30% of orders were completed — but there was no index supporting it at all.

First attempt: a straightforward index on the filter column.

CREATE INDEX idx_orders_status ON orders (status);

This helped, but only modestly — down to about five seconds — because the query still needed to touch every matching row’s created_at and total columns from the table itself after using the index to find them, and there were still millions of matching rows to aggregate.

Second attempt: a partial, covering index tailored specifically to this query pattern.

CREATE INDEX idx_orders_completed_covering
ON orders (customer_id, created_at)
INCLUDE (total)
WHERE status = 'completed';

This did three things at once: the partial condition (WHERE status = 'completed') meant the index only contains the 30% of rows that ever matter for this query, it’s ordered by customer_id which helps the grouping, and the INCLUDE (total) meant the query could be satisfied entirely from the index without touching the table at all — a genuine index-only scan. That brought the query down to around 300 milliseconds.

The lesson I keep relearning from cases like this: the first index that “helps” is rarely the best index available, and it’s worth iterating with EXPLAIN ANALYZE after each change rather than stopping at the first improvement.

When to Consider a Materialized View Instead

Sometimes, no amount of indexing gets an aggregation query fast enough for a dashboard’s needs, especially when it’s aggregating over the entire history of a large table on every page load. In that case, I stop trying to make the live query faster and instead precompute it.

CREATE MATERIALIZED VIEW monthly_revenue AS
SELECT customer_id, date_trunc('month', created_at) AS month, sum(total) AS revenue
FROM orders
WHERE status = 'completed'
GROUP BY customer_id, date_trunc('month', created_at);

CREATE UNIQUE INDEX idx_monthly_revenue_pk ON monthly_revenue (customer_id, month);

Refreshed on a schedule (or triggered after batch jobs):

REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_revenue;

The CONCURRENTLY option (which requires that unique index) lets the refresh happen without locking out readers of the materialized view while it’s being recomputed. I reach for this when the underlying data doesn’t need to be reflected in the dashboard in real time — a few minutes or hours of staleness is often a perfectly acceptable trade for a dashboard that loads instantly instead of taking several seconds on every single view.

Wrapping Up

Query optimization in PostgreSQL isn’t really about memorizing a list of tricks — it’s about developing the habit of actually looking at what the planner is doing instead of guessing. EXPLAIN ANALYZE tells you the truth about where time is going, and from there, the fix is almost always one of a small set of well-understood tools: a better index, fresher statistics, or a rewritten query. I still run EXPLAIN ANALYZE as my very first step on any performance complaint, and it’s rarely steered me wrong.