Your PostgreSQL Query Is Slow. Allow Me to Assist.

Good evening. Let us begin with what you are experiencing.

Your application was fast once. Queries returned in single-digit milliseconds. Pages loaded crisply. Nobody complained.

Then — gradually, then suddenly — something changed. An API endpoint that responded in 15ms now takes 800ms. A dashboard that loaded instantly now shows a spinner. Your users have begun to notice, and I can assure you, they are not impressed.

This is not a bug in your code. It is a fundamental property of how PostgreSQL behaves as data grows. The query planner makes cost-based decisions, and those costs shift beneath you as tables grow from thousands of rows to millions. What was once a perfectly acceptable query plan becomes, if you'll forgive the observation, rather catastrophic.

I should be direct about something before we proceed. This article is long. It is long because the subject warrants thoroughness, and because vague advice about database performance is worse than no advice at all. A guest who arrives with a 487ms query does not need platitudes about "adding an index." They need to know which index, why it helps, how to verify it worked, and what else might be contributing. That is what I intend to provide.

I have attended to a great many slow queries in my time. They nearly always trace back to one of five causes, and the diagnostic process is the same every time. If you'll follow me, I shall walk you through it step by step — from finding the offending query, to reading its execution plan, to applying the correct remedy, to verifying the improvement. No guessing. No speculation. Evidence, then action.

First, the instrument: enabling pg_stat_statements

Before we can diagnose anything, we need visibility. PostgreSQL tracks query performance automatically through the pg_stat_statements extension, but it requires explicit activation. It ships with PostgreSQL — you need not install anything — but it does not enable itself.

-- Check if pg_stat_statements is available:
SELECT * FROM pg_available_extensions WHERE name = 'pg_stat_statements';

-- Enable it (requires a restart, not just a reload):
-- In postgresql.conf:
--   shared_preload_libraries = 'pg_stat_statements'
-- Then restart PostgreSQL once. After that:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Verify it is active:
SELECT calls, query FROM pg_stat_statements LIMIT 1;

The extension records every query that passes through your database: how many times it ran, how long each execution took, how many rows it returned, how many buffer hits and reads it performed. It normalizes queries by replacing literal values with parameters, so WHERE id = 42 and WHERE id = 7 are tracked as the same query pattern.

I mention the setup step because I encounter, with discouraging regularity, production databases that have been running for years without this extension enabled. This is the database equivalent of running a household without an inventory of the silver. You cannot manage what you cannot measure, and pg_stat_statements is how PostgreSQL lets you measure.

If your database is managed (RDS, Cloud SQL, Supabase, Neon), the extension is typically available but may need to be enabled through the provider's console. Check your provider's documentation — most require only a parameter group change and a reboot.

I have prepared a PostgreSQL monitoring stack guide that walks through the complete setup: pg_stat_statements, pg_qualstats, and auto_explain, configured to work together.

Which query is slow? Finding the culprit.

With pg_stat_statements active, we can now ask the question that matters: where is the database actually spending its time?

One does not simply guess at these things. The query you think is slow and the query that is actually slow are, in my experience, different queries about 40% of the time. The endpoint that feels sluggish may be slow because of a completely different query than the one you suspect. So we begin with data, not hunches.

SELECT query, calls,
  mean_exec_time::numeric(10,2)  AS avg_ms,
  total_exec_time::numeric(10,2) AS total_ms,
  rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

You will see something like this:

 query                          | calls  | avg_ms |   total_ms   | rows
--------------------------------+--------+--------+--------------+----------
 SELECT c.name, COUNT(o.id)...  | 48,291 | 487.23 | 23,530,841   | 9,658,200
 SELECT p.*, c.name FROM pr...  | 12,040 | 234.11 |  2,818,683   | 1,204,000
 SELECT u.email, COUNT(s.id...  |  8,422 | 612.05 |  5,154,117   |   842,200
 UPDATE orders SET status =...  | 94,210 |   1.84 |    173,346   |    94,210

The column that matters most is total_exec_time. Not avg_ms — total. A query averaging 2ms that runs 500,000 times per day is a far greater burden on your database than a query averaging 600ms that runs twice. The total tells you where your database is actually spending its time.

I would draw your attention to a subtle detail in this output. The UPDATE orders SET status query averages 1.84ms — fast by any measure. But it runs 94,210 times per day. That is 173 seconds of cumulative database time. Not nothing, but not the priority either. The SELECT c.name, COUNT(o.id) at the top averages 487ms across 48,291 executions — that is 23,530 seconds, or roughly 6.5 hours of cumulative database time. Per day. That is where we direct our attention.

Take the top three queries from this list. Those are the matters requiring our attention this evening.

-- Reset stats after a deployment or optimization pass:
SELECT pg_stat_statements_reset();

-- This lets you see the new baseline clearly.
-- I recommend resetting after each round of optimization
-- so you can measure the effect of your changes in isolation.

After each round of optimization, I recommend resetting the statistics. This gives you a clean baseline to measure whether your changes had the intended effect. Without a reset, the historical data from before your fix continues to dominate the averages, and you cannot tell whether the situation has improved.

What is the planner actually doing? Reading EXPLAIN ANALYZE.

EXPLAIN ANALYZE is the single most valuable diagnostic tool in PostgreSQL. It executes your query and shows you exactly what the planner did — which tables it scanned, which indexes it used (or declined to use), how many rows it expected versus how many it actually found, and where the time went.

Take your slowest query and prepend EXPLAIN (ANALYZE, BUFFERS):

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT c.name, COUNT(o.id), SUM(o.total)
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.created_at > NOW() - INTERVAL '30 days'
GROUP BY c.name
ORDER BY SUM(o.total) DESC;

The output is verbose. That is because PostgreSQL is being thorough, and I respect that. Here is what ours produced:

Sort  (cost=45892..45893 rows=200 width=48) (actual time=487.2..487.3 rows=200 loops=1)
  Sort Key: (sum(o.total)) DESC
  Sort Method: quicksort  Memory: 41kB
  ->  HashAggregate  (cost=45882..45884 rows=200 width=48) (actual time=486.9..487.0 rows=200 loops=1)
        Group Key: c.name
        Batches: 1  Memory Usage: 61kB
        ->  Hash Join  (cost=12.50..43382 rows=500000 width=20) (actual time=0.3..312.6 rows=487293 loops=1)
              Hash Cond: (o.customer_id = c.id)
              ->  Seq Scan on orders o  (cost=0.00..38472 rows=500000 width=16) (actual time=0.0..198.4 rows=487293 loops=1)
                    Filter: (created_at > (now() - '30 days'::interval))
                    Rows Removed by Filter: 9512707
                    Buffers: shared hit=12841 read=40982
              ->  Hash  (cost=7.00..7.00 rows=200 width=12) (actual time=0.2..0.2 rows=200 loops=1)
                    Buckets: 1024  Memory Usage: 17kB
                    ->  Seq Scan on customers c  (cost=0.00..7.00 rows=200 width=12) (actual time=0.0..0.1 rows=200 loops=1)
Planning Time: 0.284 ms
Execution Time: 487.452 ms

If you'll direct your attention to the line that reads Seq Scan on orders o — there it is. That is your problem. PostgreSQL is reading every single row in the orders table — all 10 million of them — to find the roughly 487,000 that match our 30-day filter. It then discards 9.5 million rows it didn't need.

The Buffers: shared hit=12841 read=40982 line confirms the damage. Over 40,000 pages read from disk. For a single query execution. Multiply that by 48,000 daily calls and I trust you can see why the database is feeling rather strained.

A brief guide to reading EXPLAIN output

I recognize that EXPLAIN output can appear impenetrable at first encounter. Allow me to clarify the notation, because you will need to read many of these before the evening is through.

-- A guide to reading EXPLAIN ANALYZE output:
--
-- "cost=0.00..38472"
--   Estimated startup cost..total cost (in arbitrary units)
--   The planner uses these to choose between plans.
--
-- "actual time=0.0..198.4"
--   Real wall-clock time in milliseconds (startup..total)
--   This is what you actually experienced.
--
-- "rows=500000" (in cost) vs "rows=487293" (in actual)
--   Estimated vs actual row count. Large discrepancies
--   indicate stale statistics.
--
-- "Rows Removed by Filter: 9512707"
--   Rows that were read but did not match the filter.
--   This is wasted work. The higher this number, the
--   more urgently you need an index.
--
-- "Buffers: shared hit=12841 read=40982"
--   shared hit = pages found in PostgreSQL's buffer cache
--   read = pages fetched from the OS (disk or OS cache)
--   Higher read counts mean more I/O pressure.

The most telling indicator of trouble is the gap between estimated and actual row counts. When the planner estimates 500,000 rows and the actual count is 487,293, the estimate is reasonable — the planner is making sound decisions. When the planner estimates 100 rows and the actual count is 487,293, you have a planning catastrophe. The planner chose a strategy appropriate for 100 rows and applied it to half a million. That discrepancy is almost always caused by stale statistics, and the fix is straightforward: ANALYZE your_table;.

The other red flag is Rows Removed by Filter. If a node reads 10 million rows and removes 9.5 million by filter, the database performed 9.5 million units of work that produced no value whatsoever. That is not a minor inefficiency. That is a query that should be reading 487,000 rows and is instead reading 10 million. The ratio of useful work to wasted work is 1:20. An index resolves this by letting the database read only the rows it needs.

The five usual suspects

In my experience, slow PostgreSQL queries trace back to one of five causes. I shall present them in order of frequency, because your time is valuable and I would not wish to waste it.

1. Missing indexes

This is the cause in roughly 60% of the cases I encounter. The query filters or joins on a column that has no index, forcing PostgreSQL into a sequential scan. The dedicated Fix PostgreSQL Sequential Scans guide covers stale statistics, cost parameters, expression mismatches, and the six most common root causes. The immediate fix is straightforward:

-- The filtered column first, if you please:
CREATE INDEX idx_orders_created_at ON orders (created_at);

-- And the join column, while we are attending to matters:
CREATE INDEX idx_orders_customer_id ON orders (customer_id);

After adding the index, the same EXPLAIN shows a rather different picture:

Sort  (cost=892..893 rows=200 width=48) (actual time=2.1..2.2 rows=200 loops=1)
  Sort Key: (sum(o.total)) DESC
  Sort Method: quicksort  Memory: 41kB
  ->  HashAggregate  (cost=882..884 rows=200 width=48) (actual time=1.9..2.0 rows=200 loops=1)
        Group Key: c.name
        ->  Hash Join  (cost=12.50..582 rows=48729 width=20) (actual time=0.2..1.4 rows=48729 loops=1)
              Hash Cond: (o.customer_id = c.id)
              ->  Index Scan using idx_orders_created_at on orders o  (cost=0.43..412 rows=48729 width=16) (actual time=0.0..0.8 rows=48729 loops=1)
                    Index Cond: (created_at > (now() - '30 days'::interval))
                    Buffers: shared hit=1247
              ->  Hash  (cost=7.00..7.00 rows=200 width=12) (actual time=0.1..0.1 rows=200 loops=1)
                    ->  Seq Scan on customers c  (cost=0.00..7.00 rows=200 width=12) (actual time=0.0..0.0 rows=200 loops=1)
Planning Time: 0.312 ms
Execution Time: 2.341 ms

2.3ms. Down from 487ms. The sequential scan is gone, replaced by an Index Scan that reads only the rows it needs. The buffer reads dropped from 53,000 to 1,247. That is a 208x improvement from a single CREATE INDEX statement.

I should note something about the mechanics of this improvement, because the number alone does not convey what actually changed. Without the index, PostgreSQL read every page of the orders table — all 53,000 8kB pages, totaling roughly 414MB of data — to find the matching rows. With the index, it read 1,247 pages. The database went from scanning the entire library to consulting the card catalog and walking directly to the correct shelf. The work that was eliminated is not "unnecessary overhead." It was the overwhelming majority of the work.

A note on CREATE INDEX in production

If your table has active traffic, a standard CREATE INDEX will lock the table for the duration of the index build. On a 10 million row table, that could be several minutes — during which every INSERT, UPDATE, and DELETE will block. In production, this is unacceptable.

-- On a production table with active traffic, use CONCURRENTLY:
CREATE INDEX CONCURRENTLY idx_orders_created_at ON orders (created_at);

-- This takes longer, but does not lock the table for writes.
-- A standard CREATE INDEX acquires a ShareLock on the table —
-- which blocks writes for the duration of the build. Reads continue
-- unaffected. On a 10 million row table, that could be minutes without writes.
-- CONCURRENTLY avoids this at the cost of two table scans
-- instead of one, and the inability to run inside a transaction.

The CONCURRENTLY option builds the index without holding a write lock. It takes longer and performs two scans instead of one, but your application continues to operate normally during the build. This is not optional advice for production systems. This is how you add indexes in production. Full stop.

When a single-column index is not enough

The single-column index on created_at solved our immediate problem, but there are cases where a composite index — an index on two or more columns — is the more precise tool.

-- When your query filters AND joins, a composite index
-- can serve both conditions from a single B-tree traversal:
CREATE INDEX idx_orders_created_customer
  ON orders (created_at, customer_id);

-- The equality-first rule applies: if you have equality
-- and range conditions, put equality columns first.
-- Here, customer_id = ? AND created_at > ? would prefer:
CREATE INDEX idx_orders_customer_created
  ON orders (customer_id, created_at);

A composite index on (customer_id, created_at) serves queries that filter on both columns in a single B-tree traversal, rather than using one index and then filtering. The difference can be 10x-50x on queries that combine an equality filter with a range filter. I have written at length about composite index column order if you wish to explore this further — the column order is not a suggestion, and getting it wrong can produce an index that is slower than no index at all.

2. Expensive aggregations without materialized views

Even with proper indexes, aggregation queries — COUNT, SUM, AVG with GROUP BY — must still read every matching row and compute the result from scratch on every execution. If your dashboard runs the same aggregation 48,000 times a day, that is 48,000 recomputations of the same numbers that change, at most, every few minutes.

A materialized view computes the result once and stores it:

CREATE MATERIALIZED VIEW mv_customer_revenue_30d AS
SELECT c.name, COUNT(o.id) AS order_count, SUM(o.total) AS revenue
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.created_at > NOW() - INTERVAL '30 days'
GROUP BY c.name;

CREATE UNIQUE INDEX ON mv_customer_revenue_30d (name);

-- Now query the view instead. 1.2ms. Much more presentable.
SELECT * FROM mv_customer_revenue_30d ORDER BY revenue DESC;

The query against the materialized view returns in 1.2ms because it is reading pre-computed results from a small table, not joining and aggregating millions of rows. The original query performed a hash join across two tables, aggregated 487,000 rows, and sorted the results. The materialized view did all of that work once, stored the answer, and now serves it as a simple table scan on 200 rows.

-- Manual refresh (blocks reads during refresh on pre-9.4):
REFRESH MATERIALIZED VIEW mv_customer_revenue_30d;

-- Concurrent refresh (requires a unique index, allows reads during refresh):
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_customer_revenue_30d;

-- The CONCURRENTLY form is what you want in production.
-- Without it, queries against the view block during the refresh.
-- With it, the old data remains readable while the new data
-- is being computed. The swap is atomic.

-- Schedule via pg_cron or your application's task scheduler:
-- Every 5 minutes is a common interval for dashboards.
-- Every 60 seconds for near-real-time. Every hour for reports.

The traditional trade-off is freshness for speed — you accept slightly stale data in exchange for sub-millisecond reads. For many dashboards, this is a perfectly acceptable arrangement. A revenue dashboard that updates every 5 minutes is not dishonest; it is practical. But I should be forthcoming: if your use case requires real-time accuracy — a financial ledger, a live inventory count, a trading dashboard — materialized views may introduce a staleness window that is not acceptable. Know your freshness requirements before committing to this approach.

3. N+1 query patterns from ORMs

This is the one that hides. Your ORM generates what appears to be a simple query, but it is actually generating dozens or hundreds of queries — one for each row in the parent result set.

-- What your ORM is doing behind your back:
SELECT * FROM orders WHERE id = 1;
SELECT * FROM customers WHERE id = 42;
SELECT * FROM orders WHERE id = 2;
SELECT * FROM customers WHERE id = 17;
-- ... one shudders to think how long this continues

-- What it ought to be doing:
SELECT o.*, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.id IN (1, 2, 3, ...);

At 50 rows, you barely notice the extra round trips. At 5,000 rows, you are making 5,001 database calls instead of 1. Each call carries network latency, connection overhead, and planner time. The cumulative effect is devastating. I have seen endpoints where a single page render generates 400 database round trips. The application developer saw one line of ORM code. PostgreSQL saw four hundred queries.

Detecting N+1 patterns

N+1 patterns are invisible in EXPLAIN ANALYZE because each individual query is fast. The problem is not the query — it is the quantity of queries. pg_stat_statements reveals them by their call counts.

-- Detecting N+1 from pg_stat_statements:
-- Look for queries with extremely high call counts
-- and low individual execution time:
SELECT query, calls, mean_exec_time::numeric(10,2) AS avg_ms
FROM pg_stat_statements
WHERE calls > 10000
  AND mean_exec_time < 5
ORDER BY calls DESC
LIMIT 10;

-- If you see a SELECT with 50,000 calls averaging 0.3ms each,
-- that is 15 seconds of cumulative time — and almost certainly
-- an N+1 pattern. The individual execution is fast.
-- The problem is that there are fifty thousand of them.

Fixing N+1 across frameworks

The fix depends on your framework, but every major ORM provides the mechanism. The difficulty is knowing where the N+1 patterns are hiding.

-- Django:
orders = Order.objects.select_related('customer').all()
# Generates: SELECT ... FROM orders JOIN customers ...

-- Django with prefetch (for reverse/many-to-many):
customers = Customer.objects.prefetch_related('orders').all()
# Generates: SELECT ... FROM customers; SELECT ... FROM orders WHERE customer_id IN (...)

-- SQLAlchemy:
stmt = select(Order).options(joinedload(Order.customer))
# Generates: SELECT ... FROM orders JOIN customers ...

-- Rails:
orders = Order.includes(:customer).all
# Generates: SELECT ... FROM orders; SELECT ... FROM customers WHERE id IN (...)

-- Laravel (Eloquent):
$orders = Order::with('customer')->get();
# Generates: SELECT ... FROM orders; SELECT ... FROM customers WHERE id IN (...)

I would note, in the interest of honesty, that eager loading is not without its own costs. A joinedload that pulls in a large related table can produce a Cartesian product if the relationship is one-to-many, multiplying the result set size and transferring more data than necessary. prefetch_related (Django) and includes (Rails) avoid this by executing a separate IN query, which is generally the safer default. The right choice depends on the relationship cardinality. One-to-one: join. One-to-many: separate query with IN. Many-to-many: definitely separate query with IN.

4. Stale table statistics

PostgreSQL's query planner relies on table statistics — row counts, value distributions, null fractions, most common values — to choose the best execution plan. When those statistics are stale, the planner makes poor decisions. It might estimate that a filter matches 100 rows when it actually matches 500,000, and choose a nested loop join that performs 500,000 index lookups instead of the hash join that would have been 100x faster.

-- Check for stale statistics by comparing estimated vs actual rows:
-- In your EXPLAIN ANALYZE output, look for lines like:
--   (cost=... rows=100) (actual ... rows=487293)
-- A 4,873x discrepancy means the planner is flying blind.

-- Fix: run ANALYZE on the specific table:
ANALYZE orders;

-- Check when ANALYZE last ran:
SELECT relname, last_analyze, last_autoanalyze, n_live_tup, n_dead_tup
FROM pg_stat_user_tables
WHERE relname = 'orders';

-- If n_dead_tup is a large fraction of n_live_tup,
-- autovacuum is falling behind. Consider tuning:
ALTER TABLE orders SET (
  autovacuum_analyze_threshold = 1000,
  autovacuum_analyze_scale_factor = 0.02
);
-- This triggers ANALYZE after 1000 + 2% of rows change,
-- rather than the default 50 + 10%.

The fix is simple: ANALYZE your_table;. PostgreSQL's autovacuum process does this automatically, but it can fall behind on tables with heavy write activity. The default trigger is 50 rows plus 10% of the table — on a 10 million row table, that means autovacuum waits until 1,000,050 rows have changed before re-analyzing. If your table receives bursts of writes that change the data distribution significantly but do not cross the threshold, the planner may be operating on outdated information for extended periods.

If you see large discrepancies between estimated rows and actual rows in your EXPLAIN output — say, the planner expects 100 rows and the reality is 500,000 — stale statistics are the most likely cause. The remedy is immediate (ANALYZE) and the prevention is straightforward (lower the autovacuum thresholds for high-churn tables).

5. Lock contention and connection exhaustion

Sometimes the query itself is fast, but it spends most of its wall-clock time waiting — waiting for a lock held by another transaction, or waiting for a connection from an exhausted pool. This is a different class of problem from the previous four. The query plan is fine; the infrastructure around it is the bottleneck.

-- Find queries waiting for locks:
SELECT pid, state, wait_event_type, wait_event,
       query, age(clock_timestamp(), query_start) AS duration
FROM pg_stat_activity
WHERE state != 'idle'
  AND wait_event_type = 'Lock'
ORDER BY duration DESC;

-- Find blocking and blocked pairs:
SELECT blocked.pid AS blocked_pid,
       blocked.query AS blocked_query,
       blocking.pid AS blocking_pid,
       blocking.query AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_locks bl ON bl.pid = blocked.pid
JOIN pg_locks kl ON kl.locktype = bl.locktype
  AND kl.database IS NOT DISTINCT FROM bl.database
  AND kl.relation IS NOT DISTINCT FROM bl.relation
  AND kl.page IS NOT DISTINCT FROM bl.page
  AND kl.tuple IS NOT DISTINCT FROM bl.tuple
  AND kl.transactionid IS NOT DISTINCT FROM bl.transactionid
  AND kl.pid != bl.pid
  AND kl.granted
  AND NOT bl.granted
JOIN pg_stat_activity blocking ON blocking.pid = kl.pid;

Lock contention typically manifests as queries that are sometimes fast and sometimes inexplicably slow, depending on what else is running concurrently. The pg_stat_activity view shows you exactly which queries are waiting, what they are waiting for, and which other query holds the lock they need.

-- Check current connection usage:
SELECT count(*) AS total,
       count(*) FILTER (WHERE state = 'active') AS active,
       count(*) FILTER (WHERE state = 'idle') AS idle,
       count(*) FILTER (WHERE state = 'idle in transaction') AS idle_in_txn
FROM pg_stat_activity
WHERE backend_type = 'client backend';

-- "idle in transaction" is the one to watch.
-- These connections hold locks, consume memory, and prevent
-- autovacuum from cleaning up dead tuples — all while doing
-- nothing. They are the infrastructural equivalent of a guest
-- who has finished dining but refuses to leave the table.

Connection exhaustion presents differently. Your application logs show connection timeout errors. New queries cannot start because all available connections are occupied. The usual cause is a combination of too many concurrent connections and transactions that hold connections longer than necessary — particularly the idle in transaction state, which indicates an application that opened a transaction, performed some work, and then... forgot to commit. Or wandered off to make an HTTP call while still holding the transaction open.

Connection pooling (via PgBouncer or similar) addresses the connection side. Lock contention typically requires restructuring how your application handles concurrent writes — shorter transactions, advisory locks where appropriate, and occasionally rethinking the data model to reduce contention on hot rows.

The configuration layer: postgresql.conf and the defaults that lie to your planner

I have saved this for after the five suspects because configuration tuning is genuinely less impactful than fixing a missing index or an N+1 pattern. An index can deliver a 200x improvement. Configuration tuning typically delivers 2x-5x. But those factors compound, and there are default settings that are actively misleading your query planner. If your investigation points to missing indexes as the cause, the missing index detection guide covers the complete workflow from identification through validation.

-- Check current work_mem:
SHOW work_mem;
-- Default: 4MB. This is per-operation, not per-connection.

-- A single complex query can use work_mem multiple times:
-- once for each sort, hash join, or hash aggregate node.
-- Five hash joins = 5 x work_mem.

-- If EXPLAIN shows "Sort Method: external merge Disk: 156MB",
-- the sort exceeded work_mem and spilled to disk.
-- Increasing work_mem can eliminate the disk spill:
SET work_mem = '64MB';

-- Set it per-session or per-transaction, not globally,
-- unless you have modeled the memory impact:
-- 200 connections x 64MB x N operations = a lot of RAM.

-- For specific expensive queries, set it just for that query:
BEGIN;
SET LOCAL work_mem = '256MB';
-- run your expensive query here
COMMIT;
-- work_mem reverts to the default after COMMIT.

The work_mem setting controls how much memory PostgreSQL allocates per sort or hash operation before spilling to disk. The default is 4MB — a conservative figure chosen for safety, not performance. If your EXPLAIN output shows Sort Method: external merge Disk: 156MB, the sort exceeded work_mem and wrote intermediate results to temporary files on disk. Increasing work_mem to 64MB or 128MB can eliminate the disk spill entirely, turning a multi-second sort into a sub-second one.

But I must counsel caution. work_mem is allocated per operation, not per query and not per connection. A single complex query with five hash joins uses 5 times work_mem. Two hundred concurrent connections running such queries use 200 times 5 times work_mem. Setting work_mem to 256MB globally on a server with 200 connections is a formula for running out of RAM. The SET LOCAL approach — setting work_mem within a transaction for a specific expensive query — is the safer pattern.

-- Key postgresql.conf settings for query performance:

-- shared_buffers: how much RAM PostgreSQL uses for caching.
-- Default: 128MB. Recommended: 25% of total RAM.
-- On a 32GB server: shared_buffers = '8GB'

-- effective_cache_size: tells the planner how much total cache
-- (shared_buffers + OS cache) is available. Does NOT allocate memory.
-- Default: 4GB. Recommended: 75% of total RAM.
-- On a 32GB server: effective_cache_size = '24GB'

-- random_page_cost: how expensive the planner thinks random I/O is.
-- Default: 4.0 (tuned for spinning disks).
-- On SSDs: random_page_cost = 1.1
-- This makes the planner more willing to use indexes.

-- effective_io_concurrency: how many concurrent I/O requests.
-- Default: 1 (spinning disk). On SSDs: 200.

The setting that produces the most dramatic change for the least effort is random_page_cost. The default value of 4.0 was calibrated for spinning magnetic disks, where random I/O is genuinely 4x more expensive than sequential I/O. On modern SSDs, random and sequential reads are nearly identical in cost. With random_page_cost = 4.0 on an SSD, the planner artificially penalizes index scans — because it believes each random page fetch is expensive — and chooses sequential scans more often than it should. Setting it to 1.1 tells the planner the truth about your storage, and the planner rewards the truth by choosing better plans.

If you are running PostgreSQL on an SSD with the default random_page_cost, you are asking your planner to make decisions based on hardware you do not have. I find this behaviour — running modern software with settings calibrated for 2004-era hardware — to be the infrastructural equivalent of a waiter who sets the table for a banquet that happened last Tuesday.

A systematic approach, if you'll permit me

Allow me to suggest a procedure. I have found it to be reliable across a wide range of situations, and it has the virtue of being methodical rather than speculative. I have seen engineers spend days "optimizing" queries by trial and error — adding random indexes, adjusting configuration knobs, rewriting queries in a different style — when a 30-minute diagnostic process would have identified the actual cause and the specific fix.

I recommend addressing the fixes in this order: indexes first (highest impact, lowest effort), then N+1 patterns (moderate effort, high impact), then materialized views (moderate effort, transformative impact for repeated aggregations), then configuration tuning, then statistics and autovacuum adjustment. This ordering is not arbitrary — it follows the principle of addressing the largest sources of waste first. A missing index on a hot query is typically responsible for more wasted database time than all configuration issues combined.

After each fix, re-run EXPLAIN ANALYZE and compare the results to the original. Do not assume the fix worked. Verify it. I have seen well-intentioned indexes that the planner ignored because the column order was wrong, or because random_page_cost was set so high that the planner preferred the sequential scan even with the index available. Trust, but verify.

How much faster can it actually get?

Allow me to share some numbers from actual optimization work, because I find that vague promises of "improved performance" are rather beneath us both.

These are not theoretical figures. These are the kinds of improvements that result from identifying the correct cause and applying the appropriate remedy. The diagnostic process I described above is how you get there.

I should note that the improvements do not always stack multiplicatively. Fixing a missing index on a query that also suffers from stale statistics will not give you 208x times 61x. The index fix may resolve both problems simultaneously, because the index scan is less sensitive to row-count estimates than the sequential scan it replaced. Conversely, some improvements do compound: adding an index and using a materialized view and fixing the work_mem setting on a complex aggregation query can produce a combined improvement that exceeds any individual fix. The point is not the exact multiplier. The point is that these are not 10% improvements requiring careful benchmarking to detect. These are orders-of-magnitude changes visible to the naked eye.

The honest counterpoint: when the database is genuinely the wrong tool

A waiter who overstates his case is no waiter at all, so I should be forthcoming about the situations where the techniques in this article will not save you.

Sub-millisecond latency requirements. If your application requires responses in under 1ms for a high-throughput hot path — a real-time bidding system, a rate limiter serving 100,000 requests per second, a session cache — PostgreSQL, even perfectly optimized, is unlikely to deliver consistently. The overhead of parsing SQL, consulting the planner, and traversing a B-tree index is irreducible below a certain floor. Redis, Memcached, or in-process caching exist for this use case, and they are the right tool. I have no quarrel with Redis deployed for the problems Redis was designed to solve.

Analytical queries over billions of rows. PostgreSQL is a row-oriented database. It stores data row by row and reads entire rows even when you only need two columns. For analytical workloads that scan billions of rows and aggregate a handful of columns, a columnar store — ClickHouse, DuckDB, BigQuery — will outperform PostgreSQL by 10-100x on those specific queries because it reads only the columns it needs and compresses them aggressively. If your "slow query" is a full-table analytical scan over 500 million rows, the answer may not be a better index. It may be a different database for that specific workload.

Queries that are fundamentally expensive. Some queries are slow because the work they describe is genuinely large. A query that joins six tables, applies eight filters, aggregates across three dimensions, and sorts the result — that query may be doing exactly what it was asked to do, and doing it as efficiently as possible. The solution is not to make it faster. The solution is to ask whether the question needs to be answered in real time, or whether a materialized view, a background job, or a denormalized summary table would serve the user equally well without the real-time computation.

These are not failures of PostgreSQL. They are boundaries of what any single tool should be asked to do. The database was not slow. It was being asked to solve a problem that belongs to a different part of the architecture.

When the volume of queries makes manual optimization impractical

The procedure I've outlined works beautifully when you have a handful of slow queries. You identify them, you diagnose them, you fix them. Very satisfying work.

But production databases are not static. New features introduce new query patterns. Traffic shifts. Data distributions change. The query you optimized last month has been replaced by three new queries you haven't looked at yet. It is rather like maintaining a large estate — by the time you've finished polishing the silver, the brass needs attention.

This is the problem Gold Lapel was built to resolve. It sits between your application and PostgreSQL as a transparent proxy, observes the actual query patterns flowing through, and applies the optimizations continuously:

• Missing indexes — Gold Lapel detects unindexed filters and joins, then creates the appropriate indexes automatically. B-tree, composite, partial — whatever the query requires. Created CONCURRENTLY, always.
• Expensive aggregations — Repeated GROUP BY queries are materialized automatically, and here is the part I mentioned earlier: Gold Lapel uses write-aware refresh to keep those views fresh without fixed-interval polling. The data stays current and the reads stay fast. No trade-off required.
• N+1 patterns — The proxy detects the telltale signature of N+1 queries — dozens of identical single-row fetches in rapid succession — and rewrites them into batched operations at the wire level. Your ORM never knows the difference.
• Stale statistics — Gold Lapel monitors table churn and triggers ANALYZE before the planner starts making poor decisions, rather than waiting for autovacuum to get around to it.
• Connection management — Built-in connection pooling ensures your application never waits for a connection, even under peak concurrency.

No query changes. No ongoing manual effort. The diagnostic process I described above is how you understand what is happening; Gold Lapel is how you ensure it keeps being handled.

# If I may — installation takes but a moment:
curl -fsSL https://goldlapel.com/install.sh | sh

# Direct it to your Postgres instance:
goldlapel --upstream 'postgresql://user:pass@localhost:5432/mydb'

# Point your application to port 7932 instead of 5432.
# Gold Lapel shall see to the rest.

Should you wish to manage it yourself, the procedure above will serve you well — it is, after all, the same procedure Gold Lapel follows internally. Every technique in this article works without Gold Lapel, and I have written it to be useful whether or not you ever use the product. That is the standard I hold myself to. Should you prefer someone else attend to the matter on your behalf — well. That is precisely what we are here for.

A final observation

The database was not slow. It was being asked poorly.

This is not a hardware problem. It is not a budget problem. It is a knowledge problem. And knowledge problems, if you'll permit me, are the very best kind — because they are solved by learning, not by spending. The techniques in this article cost nothing. The indexes are free. The EXPLAIN ANALYZE is free. The configuration changes are free. What was required was the diagnosis, and now you have the method.

Your query arrived at 487ms. We leave it at 2.3ms. That is a 208x improvement, accomplished without upgrading hardware, without adding caching layers, without rewriting the application, and without introducing a single new dependency. One index. One line of SQL. One rather different set of buffer reads.

If you find yourself in this situation again — and you will, because data grows and query patterns evolve — the procedure is the same. pg_stat_statements to find the culprit. EXPLAIN ANALYZE to read the plan. The five suspects to identify the cause. The appropriate remedy. Verify the improvement. In infrastructure, boring is the highest compliment available. May your queries be boring.