How I made legacy queries 30% faster with EXPLAIN ANALYZE

The codebase was older than most of the team. Two SaaS products on NestJS and PostgreSQL, serving 10,000+ users a month, with a database schema that had grown one feature at a time. Nothing was on fire. Pages just got a little slower every quarter, and "the database is slow" had become something people said in standups the way they talk about the weather.

We made the slow queries 30% faster overall without a rewrite, without a bigger database instance, and without a single production incident from the changes. No clever tricks were involved. The method was boring and repeatable: measure where the time goes, read the plan, change one thing, measure again. This post is that method, with the commands and the plan patterns I look for, so you can do the same kind of PostgreSQL query optimization on your own legacy code.

Part of my guide to building a scalable SaaS backend with NestJS and PostgreSQL.

TL;DR#

• Don't guess which query is slow. Turn on pg_stat_statements and sort by total_exec_time, not by the slowest single run. A 4 ms query called 2 million times a day costs more than a 3-second report.
• Capture real plans from production with auto_explain, because the plan on your laptop's tiny dataset is not the plan production runs.
• Read an EXPLAIN ANALYZE plan from the bottom up, and look for three things first: estimated rows far from actual rows, large Rows Removed by Filter counts, and big loops numbers.
• Most of our wins came from five fixes: composite indexes that match WHERE plus ORDER BY, killing ORM N+1 queries, keyset pagination instead of deep OFFSET, extended statistics for correlated columns, and not wrapping indexed columns in functions.
• Ship every index with CREATE INDEX CONCURRENTLY, check the plan changed, and compare pg_stat_statements before and after.

Step 1: find the queries that actually cost you#

On a legacy system, everyone has a favourite slow query. It's usually the wrong one. The query that hurts is the one that eats the most total database time, and you only see that by measuring.

pg_stat_statements records every normalised query with its call count, timing and buffer usage. It needs to be in shared_preload_libraries, which means a restart. On Amazon RDS it's set in a custom parameter group, and on many RDS versions it's already preloaded, so check first:

SHOW shared_preload_libraries;          -- should include pg_stat_statements
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Let it collect for a few days of normal traffic, then pull the top of the list:

SELECT
  queryid,
  calls,
  round(total_exec_time)                       AS total_ms,
  round(mean_exec_time::numeric, 1)            AS mean_ms,
  rows / greatest(calls, 1)                    AS rows_per_call,
  round(100.0 * shared_blks_hit
        / nullif(shared_blks_hit + shared_blks_read, 0), 1) AS cache_hit_pct,
  left(query, 120)                             AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

When I ran this on our production database, the top 20 rows were more than three quarters of all query time. That's typical. The list also changed what we worked on. The report everyone complained about came in at number 14. Numbers 1 to 3 were an invoice list query that ran on every dashboard load, a lookup the ORM fired once per row of another query, and a count behind a pagination widget.

Things to read from that output:

• High calls with a modest mean_ms is usually an N+1 or a missing cache. You fix it by running it less, not by making it faster.
• High mean_ms with few calls is a heavy report or an admin screen. It's worth fixing, but it rarely moves the overall numbers.
• A low cache_hit_pct means the query reads from disk a lot. Either it scans far more data than it returns, or your working set no longer fits in memory.
• Huge rows_per_call on an endpoint that shows 25 items means the app is fetching rows and throwing them away.

Before you change anything, save a snapshot of this view to a table or a CSV. It's your baseline. Without one you'll never be able to say how much faster things got.

Step 2: capture real plans with auto_explain#

pg_stat_statements tells you which query. It doesn't tell you why. For that you need the plan, and it has to be the plan production actually ran, with production's data distribution and production's parameter values.

auto_explain logs the plan of any statement that runs longer than a threshold. These are the settings I use on a busy database:

shared_preload_libraries = 'pg_stat_statements,auto_explain'
auto_explain.log_min_duration = '500ms'   # only plans for slow statements
auto_explain.log_analyze = on             # real row counts, not just estimates
auto_explain.log_buffers = on
auto_explain.log_timing = off             # per-node timing is the expensive part
auto_explain.log_format = 'json'
auto_explain.sample_rate = 0.25           # instrument a quarter of statements

log_analyze adds instrumentation to every statement, not only the slow ones, because PostgreSQL can't know a query will be slow before it runs. Turning log_timing off and sampling keep that overhead small. On RDS, the plans land in the PostgreSQL log, which you can publish to CloudWatch Logs and search there.

Step 3: run EXPLAIN ANALYZE safely#

Once you have a suspect, reproduce it in a psql session against a copy of production, or against production itself if you're careful:

EXPLAIN (ANALYZE, BUFFERS, SETTINGS)
SELECT id, number, total_cents, status, created_at
FROM invoices
WHERE tenant_id = '4b1e…' AND status <> 'void'
ORDER BY created_at DESC
LIMIT 25;

Three rules keep this from becoming its own incident:

1. EXPLAIN ANALYZE executes the query. For UPDATE, DELETE or INSERT, wrap it in a transaction you roll back: BEGIN; EXPLAIN ANALYZE UPDATE …; ROLLBACK;
2. Use real parameter values, ideally copied from the auto_explain log. A tenant with 200 invoices and a tenant with 2 million get very different plans, and the slow one is the one you care about.
3. Run it twice. The first run reads pages from disk, the second finds them in cache. Both numbers are honest; just know which one you're looking at. Buffers: shared read= tells you.

A note on ORMs. TypeORM and Prisma send parameterised queries (WHERE tenant_id = $1). With node-postgres, each one goes as an unnamed statement, so PostgreSQL plans it with the actual values every time, the same as your psql session. If you use named prepared statements, or a pooler that keeps them, PostgreSQL may switch to a generic plan after five executions. Since PostgreSQL 16 you can see that plan with EXPLAIN (GENERIC_PLAN).

How I read a plan in five minutes#

Here's the invoice list query from the top of our pg_stat_statements list, trimmed and with the names changed:

Limit  (cost=48211.37..48211.43 rows=25 width=72) (actual time=812.402..812.409 rows=25.00 loops=1)
  Buffers: shared hit=1204 read=38877
  ->  Sort  (cost=48211.37..48461.12 rows=99900 width=72) (actual time=812.399..812.403 rows=25.00 loops=1)
        Sort Key: created_at DESC
        Sort Method: top-N heapsort  Memory: 31kB
        ->  Seq Scan on invoices  (cost=0.00..44393.00 rows=99900 width=72) (actual time=0.031..771.950 rows=102341.00 loops=1)
              Filter: ((status <> 'void'::text) AND (tenant_id = '4b1e…'::uuid))
              Rows Removed by Filter: 1897659
Planning Time: 0.214 ms
Execution Time: 812.455 ms

I read it from the most indented line upwards, because that's where the data comes from:

1. The scan. A Seq Scan over the whole table, two million rows, to keep 102,341. Rows Removed by Filter: 1897659 is the giveaway: 95% of the work is thrown away.
2. Buffers. read=38877 is about 300 MB read from disk (8 kB pages) to show 25 rows on a screen.
3. The sort. All 102,341 matching rows get sorted just so the Limit can take the top 25. The top-N heapsort is cheap here; the scan feeding it is not.
4. Estimates against actuals. rows=99900 estimated, rows=102341.00 actual. Close, so the planner understood the data. It simply had no better path, because no index matched.

That last point matters. When estimates are close and the plan is still bad, you're usually missing an index. When estimates are wildly off, the planner is making decisions on bad information, and adding indexes may not help at all. Those are different problems with different fixes.

Other lines worth knowing:

• loops=N: the node ran N times, and its actual time and rows are per loop. Multiply to get the real cost. An index scan at 0.05 ms with loops=40000 is two seconds.
• Sort Method: external merge Disk: …kB: the sort didn't fit in work_mem and spilled to disk.
• Heap Fetches on an Index Only Scan: the visibility map is stale and PostgreSQL had to visit the table anyway. Usually a vacuum problem.
• Index Searches (new in PostgreSQL 18): how many times the index was descended. Useful for spotting skip scans and IN lists.

If you'd rather look at a picture, paste the JSON plan into a visualiser such as explain.dalibo.com. Just don't paste production data into tools you don't trust; plans contain literal values.

The fixes that made the difference#

These are the patterns we found, roughly in the order they showed up in the list. I'd bet most legacy SaaS databases have at least three of them.

1. Composite indexes that match the query, not the column#

The invoices table had an index on tenant_id and another on created_at. Neither helps much with "this tenant's newest invoices", because PostgreSQL can't walk one index in the order of another. What the query needs is one index that filters and sorts in the same pass:

CREATE INDEX CONCURRENTLY invoices_tenant_created_idx
  ON invoices (tenant_id, created_at DESC)
  WHERE status <> 'void';

Equality columns go first, then the sort column. The WHERE makes it a partial index: void invoices never appear in this list, so they don't need to be in the index either, which keeps it smaller and faster to keep up to date.

The plan afterwards:

Limit  (cost=0.43..31.87 rows=25 width=72) (actual time=0.041..0.118 rows=25.00 loops=1)
  Buffers: shared hit=29
  ->  Index Scan using invoices_tenant_created_idx on invoices  (cost=0.43..128730.12 rows=102341 width=72) (actual time=0.040..0.112 rows=25.00 loops=1)
        Index Cond: (tenant_id = '4b1e…'::uuid)
        Index Searches: 1
Execution Time: 0.139 ms

No sort, no filter, 29 buffers instead of 40,000. The index is already in the order the query wants, so PostgreSQL reads the first 25 entries and stops.

Two rules for indexes on a live system. Always build them CONCURRENTLY, which doesn't block writes, and set a lock_timeout in the session so a long-running transaction can't leave you queued behind it. And if a concurrent build fails, it leaves an INVALID index behind that you have to drop and build again. My post on running database migrations safely in CI/CD covers how we ship these.

2. The N+1 that no single plan shows#

The second-busiest query in the list ran in 0.3 ms. It was also called more than 400,000 times a day. It was the ORM loading each invoice's customer one at a time:

// What the code looked like
const invoices = await this.invoices.find({ where: { tenantId }, take: 50 });
for (const invoice of invoices) {
  invoice.customer = await this.customers.findOneBy({ id: invoice.customerId }); // 50 more queries
}

You won't catch this with EXPLAIN, because each query is fast. You catch it with calls in pg_stat_statements, or by logging queries per request in development. The fix is to load the relation in one go, either with a join or with one IN query:

const invoices = await this.invoices.find({
  where: { tenantId },
  relations: { customer: true },   // one query with a LEFT JOIN
  take: 50,
});

// Or, when a join would duplicate rows, one query for all of them:
const customers = await this.customers.findBy({ id: In(invoices.map((i) => i.customerId)) });

One page load went from 51 queries to 1. The database barely noticed the difference, but the API did: every one of those round trips cost a millisecond or more of network latency, and the endpoint's p95 dropped by almost half.

3. Deep OFFSET pagination#

The admin list paged with LIMIT 50 OFFSET 40000. PostgreSQL can't jump to row 40,000; it produces and throws away the first 40,000 rows on every request. Page 1 was fast and page 800 took seconds, and a scraper walking the API was hitting page 800 a lot.

Keyset pagination (also called cursor pagination) asks for "the next 50 after the last one I saw" instead:

SELECT id, number, created_at
FROM invoices
WHERE tenant_id = $1
  AND (created_at, id) < ($2, $3)        -- the last row of the previous page
ORDER BY created_at DESC, id DESC
LIMIT 50;

With an index on (tenant_id, created_at DESC, id DESC), every page costs the same as the first. The id breaks ties so rows with the same timestamp are never skipped or repeated. You lose "jump to page 637", which in my experience almost nobody uses on purpose.

4. Bad estimates from correlated columns#

One report joined subscriptions to usage records and took 9 seconds. The plan had a Nested Loop with the inner side running loops=48211, where the planner had estimated 12 rows from the outer side.

The cause was two columns that move together: plan and billing_interval. PostgreSQL assumes columns are independent, so it multiplied the selectivity of plan = 'enterprise' by that of billing_interval = 'year' and arrived at a tiny number. In reality almost every enterprise plan was yearly. With an estimate of 12 rows, a nested loop looks cheap. With 48,000, it's a disaster.

Extended statistics teach the planner about that relationship:

CREATE STATISTICS subscriptions_plan_interval (dependencies, mcv)
  ON plan, billing_interval FROM subscriptions;
ANALYZE subscriptions;

After ANALYZE, the estimate came out at about 47,000, the planner chose a hash join, and the report ran in under a second. No index, no code change.

When estimates are off on a single column instead, try raising its statistics target first (ALTER TABLE … ALTER COLUMN … SET STATISTICS 1000, then ANALYZE). And check pg_stat_user_tables.last_autoanalyze: on a table that grows fast, autovacuum's defaults can leave statistics days old.

5. Functions on indexed columns#

Two small ones that come up in every legacy codebase:

-- Can't use an index on email
SELECT * FROM users WHERE lower(email) = lower($1);

-- Can't use an index on created_at
SELECT count(*) FROM orders WHERE date(created_at) = '2026-09-30';

PostgreSQL can only use an index when the indexed expression matches what's in the query. For the first, either index the expression or store emails normalised (or use the citext type):

CREATE UNIQUE INDEX CONCURRENTLY users_email_lower_idx ON users (lower(email));

For the second, rewrite it as a range so the plain index on created_at works:

WHERE created_at >= '2026-09-30' AND created_at < '2026-10-01'

Also watch for implicit casts: comparing a uuid column to a text parameter, or an integer to a numeric, can quietly turn an index scan into a sequential one. The Filter: line in the plan shows the cast.

6. Counting everything, every time#

The pagination widget ran SELECT count(*) over the tenant's whole invoice table on every page load, to print "Page 3 of 1,204". A count in PostgreSQL has to visit every matching row (or every index entry, with an index-only scan). There's no stored total.

We changed the UI to "Next" and "Previous" for long lists, which matched the keyset pagination anyway. For dashboard totals that really did need numbers, we kept counters in a small table updated in the same transaction as the writes. If an approximate number is good enough, the planner's estimate in pg_class.reltuples is free.

7. Sorts and hashes spilling to disk#

A few reports showed Sort Method: external merge Disk: 184320kB. That's a sort that didn't fit in work_mem, which defaults to 4 MB.

Don't raise work_mem globally. It's per sort or hash operation, per query, per connection, so a generous global value multiplies across a few hundred connections and runs the server out of memory. Raise it where it's needed instead, for the reporting role or inside the transaction that runs the report:

BEGIN;
SET LOCAL work_mem = '64MB';
-- the report query
COMMIT;

What changed, and how we checked it#

We changed one thing at a time and compared before and after in pg_stat_statements over the same days of the week. That's the 30%: total execution time for the slow queries we targeted, on comparable traffic.

Zero incidents came from process, not luck:

• Every index went out through a migration with CONCURRENTLY and a lock_timeout, during normal hours, with someone watching.
• Every query change was checked with EXPLAIN ANALYZE against a staging database restored from a recent production backup, so the data was production-sized.
• New indexes have a write cost. After a month we checked pg_stat_user_indexes and dropped indexes that were never used:

SELECT relname AS table, indexrelname AS index, idx_scan,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

Check this on every replica before dropping anything: the counters are per server, and an index unused on the primary might be what your read replica's reports depend on. Indexes that back a unique constraint stay regardless.

What we deliberately didn't do#

• Upsize the database. A bigger instance would have hidden the problems for six months and cost more every month after that. We did that kind of rightsizing after the fixes, and went down a size, not up. More on that in how we cut infrastructure costs by 35%.
• Add a cache first. Redis in front of a query that scans two million rows gives you a fast hit and a terrible miss, plus a cache invalidation problem. Fix the query, then cache if you still need to.
• Rewrite the ORM layer in raw SQL. Most fixes were indexes and statistics, and the code changes were small: relations instead of a loop, a cursor instead of an offset. We dropped to raw SQL for two reports and nothing else.
• Turn planner settings off. SET enable_seqscan = off is a diagnostic tool for a psql session, not a fix. If the planner picks a bad plan, find out why it thinks it's a good one.

PostgreSQL 18 and 19 notes#

Version 18 (current, at 18.6 as I write) made plan reading a little easier: BUFFERS is now included automatically with EXPLAIN ANALYZE, index nodes report Index Searches, and actual row counts are printed with two decimals. It also added B-tree skip scans, which let a multi-column index help even when the query doesn't filter on its first column, so some indexes you'd have added for that case are no longer needed. pg_upgrade now keeps planner statistics too, so a major upgrade no longer starts with a slow hour while ANALYZE catches up.

PostgreSQL 19 is at release candidate 1, with general availability planned for 19 November 2026. Its release notes add an IO option to EXPLAIN ANALYZE for asynchronous I/O, more cases where NOT IN and LEFT JOIN become anti-joins, and JIT compilation disabled by default. If you've seen JIT: sections eating hundreds of milliseconds in plans for medium-sized queries, that last change is worth knowing about, and you can set jit = off today.

The checklist#

• pg_stat_statements is installed and you've saved a baseline
• You've sorted by total_exec_time and picked the top queries from that, not from complaints
• auto_explain logs plans for slow statements, with timing off and sampling on
• You run EXPLAIN (ANALYZE, BUFFERS) with real parameters, and roll back writes
• Estimated rows are compared with actual rows on every node you suspect
• Composite indexes put equality columns first and the sort column last
• N+1 queries are found by call counts and replaced with joins or IN queries
• Deep OFFSET pagination is replaced with keyset pagination
• Correlated columns have extended statistics
• Indexed columns aren't wrapped in functions or casts
• Every index ships CONCURRENTLY with a lock_timeout, and unused ones get dropped

Need a hand?#

Making a slow PostgreSQL database fast again, without a rewrite or a bigger bill, is work I enjoy. I'll find where the time goes, fix the queries and indexes, and leave you with the monitoring to keep it that way. See my full-stack and backend engineering services, or email mhuzaifaa.dev@gmail.com with what's slow.

FAQ#

How do I find slow queries in PostgreSQL?#

Install the pg_stat_statements extension, let it collect a few days of normal traffic, and sort it by total_exec_time. That shows which queries use the most database time in total, which is usually more useful than the slowest single query. Add auto_explain with a threshold such as 500 ms to log the plans of slow statements in production.

What is the difference between EXPLAIN and EXPLAIN ANALYZE?#

EXPLAIN shows the plan PostgreSQL would use, with estimated costs and row counts, without running the query. EXPLAIN ANALYZE runs the query and adds the actual time, actual rows and number of loops for each node. Because it executes the statement, wrap UPDATE, INSERT and DELETE in a transaction and roll it back.

Why is PostgreSQL doing a sequential scan when I have an index?#

Either the index doesn't match the query (for example, the column is wrapped in a function, there's a type cast, or the index columns are in the wrong order), or the planner estimates that the query returns a large share of the table, where a sequential scan really is cheaper. Compare estimated and actual rows in EXPLAIN ANALYZE; if they're far apart, run ANALYZE or add extended statistics.

How do I read EXPLAIN ANALYZE output?#

Start at the most indented node and work upwards, since that's where rows are produced. For each node, compare estimated rows with actual rows, multiply actual time by loops to get its total cost, and look for large Rows Removed by Filter counts, sorts that spill to disk, and Buffers read counts far higher than the rows returned.

Does adding an index always make queries faster?#

No. An index speeds up reads that match it but slows down every insert and update on the table, takes disk space, and can be ignored by the planner if it doesn't fit the query. Add indexes for specific slow queries, confirm the plan uses them, and drop indexes whose idx_scan count stays at zero.

How do I fix N+1 queries in TypeORM?#

Load related entities in the same query with relations (a join), or collect the IDs and load them with one In() query. Find N+1 problems by looking for queries with very high calls counts in pg_stat_statements, or by logging the number of queries per request in development.

Related reading#

• Building a scalable SaaS backend with NestJS and PostgreSQL
• NestJS project structure for large SaaS apps
• Multi-tenant SaaS in PostgreSQL: schema vs row-level
• Running database migrations safely in CI/CD, for shipping indexes without locking tables
• Bash scripts to automate Postgres backups to S3, for the restored copy you test plans against