Postgres Query Slow in Production but Fast Locally: Why

A Postgres query that is fast locally and slow in production is almost never a "slow server". It is usually one of five things: the data is different (so the plan is different), the planner statistics are stale, the query waits on locks or a full connection pool, the app runs it differently than your SQL client does, or the network and disk are slower than your laptop. Run EXPLAIN (ANALYZE, BUFFERS) against production, compare estimated and actual rows, and check what the query is waiting on while it is slow.

This is a common complaint. Threads on DigitalOcean, Railway and the PostgreSQL mailing list all describe the same story: same query, same indexes, very different timings. The steps below find which of the five you have.

Step 1: Measure the Query on Production, Not Locally

Do not guess from your laptop. Run the exact query against the production database (or a read replica) and look at the real plan.

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE customer_id = 4821 ORDER BY created_at DESC LIMIT 20;

ANALYZE actually runs the statement, so the PostgreSQL docs advise wrapping data-changing statements in a transaction and rolling it back. Never run EXPLAIN ANALYZE on an UPDATE or DELETE without that.

Look at three things in the output:

• Seq Scan on a big table. The planner is reading every row instead of using an index.
• Estimated rows far from actual rows. If the plan says rows=10 and the node returned rows=2000000, the planner is working from wrong information. Everything after that node is a bad guess.
• Buffers: shared read=... is large. The data came from disk, not memory. That points at cache and disk, not at the query text.

Step 2: Check That Local and Production Hold Similar Data

A local database with a few hundred rows will use a sequential scan and finish instantly, because reading a tiny table is cheaper than using an index. Production has millions of rows and a different data spread. The same SQL gets a different plan.

Common traps:

• A WHERE value that is rare locally and very common in production (one customer owns 40 percent of the rows).
• A table with soft deletes where most rows are deleted_at IS NOT NULL.
• A missing index you never noticed, because nothing was slow with small data.

If you can, restore a recent production dump (with personal data removed) into a staging database and test there. If not, test on production through a read replica. This is the same drift described in API endpoint getting slower as data grows.

Step 3: Fix Stale Statistics

The planner chooses plans from statistics about your tables. When those are old, it picks bad plans, even with the right indexes in place. This is the "estimated vs actual rows" gap from Step 1.

-- when were the statistics last refreshed?
SELECT relname, last_analyze, last_autoanalyze, n_dead_tup
FROM pg_stat_user_tables
WHERE relname = 'orders';

-- refresh them now
ANALYZE orders;

Run EXPLAIN (ANALYZE, BUFFERS) again after ANALYZE. If the plan changes and the query gets fast, statistics were the cause. For a table that changes quickly, you can make autovacuum analyze it more often:

ALTER TABLE orders SET (autovacuum_analyze_scale_factor = 0.02);

Also look at n_dead_tup. A table full of dead rows (bloat) makes scans slower, and a manual VACUUM or tuned autovacuum settings help.

Step 4: Look for Waiting, Not Working

Sometimes the query is fast when it runs, but it spends most of its time waiting. That is why a query can be quick in psql and slow under real traffic, as one mailing list thread describes: the same query ran fast on its own and slow at peak, and the suspect was writes blocking reads.

While the problem is happening, look at what is running and what it waits for:

SELECT pid, state, wait_event_type, wait_event,
       now() - query_start AS running_for, left(query, 80) AS query
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY query_start;

• wait_event_type = 'Lock' means another transaction holds a lock. Find long-open transactions, often a job or script that forgot to commit.
• Many active sessions near your limit means you are queuing for connections. In a serverless app each function instance can open its own pool. See database connection pool exhaustion in Node.js for the fix.

Step 5: Compare How Your App Runs the Query

Your SQL client sends the query as plain text with literal values. Your app usually does not. ORMs and drivers send parameters, and often use prepared statements. Postgres can choose a different plan in that case: after a few runs it may switch to a generic plan that ignores the actual parameter value, which can be bad when the data is uneven. The PREPARE docs describe this behaviour, and the plan_cache_mode setting controls it.

To test it, run the same query as a prepared statement in psql:

PREPARE q(int) AS
  SELECT * FROM orders WHERE customer_id = $1 ORDER BY created_at DESC LIMIT 20;
EXPLAIN (ANALYZE) EXECUTE q(4821);

If the prepared version is slow and the literal version is fast, try SET plan_cache_mode = force_custom_plan; for that session. Other app-side causes are an N+1 pattern (many small queries instead of one) and fetching far more columns or rows than the page uses. Count the queries per request, not only the time per query. If you use Prisma, also see Prisma errors in production.

Step 6: Rule Out the Network and the Disk

If the plan looks the same and nothing is waiting, the machines may just differ. The DigitalOcean thread above compares a laptop with a small cloud server and finds the laptop faster on identical data. A fast local NVMe disk, a socket instead of a network hop, and more memory for caching all help your laptop.

Check these:

• Round trips. If your app runs in one region and the database in another, every query pays that delay. A page making 30 queries pays it 30 times.
• Memory settings. Compare shared_buffers, work_mem and effective_cache_size between the two environments with SHOW work_mem;. A sort that spills to disk shows as Sort Method: external merge in the plan.
• Small plan limits. Free and starter database tiers often throttle CPU or I/O.

Step 7: Find the Worst Queries with pg_stat_statements

Instead of waiting for someone to report a slow page, ask Postgres which queries cost the most. Add pg_stat_statements to shared_preload_libraries (this needs a restart; managed services like RDS use a parameter group), then create the extension. The names below are for PostgreSQL 13 and later, see the pg_stat_statements docs.

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT left(query, 80) AS query, calls,
       round(total_exec_time::numeric, 0) AS total_ms,
       round(mean_exec_time::numeric, 2) AS mean_ms
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Sort by total time, not only by mean. A query that takes a few milliseconds but runs hundreds of thousands of times an hour can cost more than one slow query that runs twice.

To catch slow plans as they happen, set auto_explain.log_min_duration (for example to 500 ms) so Postgres writes the plan of any slower query to the log. The auto_explain docs note it has overhead and its default is off, so turn it on deliberately and set auto_explain.sample_rate if traffic is high.

How Relia Helps

Relia watches the API, the database and the users of a live product together. When a route slows down, it ties the slow requests to the slow query behind them and explains the incident in plain words, including how many users were affected. It also checks after a deploy whether the problem went away. Relia does not change your database or your code. You still run EXPLAIN and write the index or fix. Setup for a Node.js or Python app is npx relia-sdk@latest init, and there is a live demo at app.tryrelia.com/demo.

FAQ

Why is my Postgres query slow only in production?

Usually because production has more and differently spread data, stale statistics, lock waits, a full connection pool, or different settings and hardware. Run EXPLAIN (ANALYZE, BUFFERS) on production to see which.

Does running ANALYZE make queries faster?

It refreshes the statistics the planner uses. If your plan was bad because of old statistics, yes. It does not help if the cause is a missing index or a lock.

Why is a query fast in psql but slow from my app?

The app may use prepared statements with a generic plan, run many small queries instead of one, or wait for a connection. Test the query as a prepared statement and count queries per request.

Is it safe to run EXPLAIN ANALYZE in production?

For a SELECT, yes, but it really executes the query, so a heavy one is still heavy. Wrap INSERT, UPDATE and DELETE in a transaction and roll it back.