How do I find out why one query is slow on my Managed PostgreSQL cluster?

Question

I have an app on a Droplet that uses a DigitalOcean Managed PostgreSQL cluster. One page has become slow as the data grew. The main query filters an orders table with about 2 million rows by customer_id and sorts by created_at. It now takes around 3 seconds.

Someone told me to run EXPLAIN and add an index, but the output looks like a wall of text to me. How do I read it? And is it safe to add an index on a live production database, or will it lock my table?

Bobby · Answer

Hi there, This is a very common problem and it usually has an easy fix. Start by looking at how Postgres runs the query: EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE customer_id = 123 ORDER BY created_at DESC LIMIT 50; Replace that with your real query. ANALYZE runs the query for real. That is fine for a SELECT. For an INSERT, UPDATE or DELETE, wrap it in BEGIN and ROLLBACK. The output looks long, but only four things matter: The scan type. Seq Scan on orders means Postgres reads the whole table. That is the problem when you only want one customer’s rows. Rows Removed by Filter. If this is close to 2 million, Postgres read almost everything and threw it away. The Sort node. Sort Method: external merge Disk means the sort did not fit in memory and spilled to disk. Execution Time at the bottom. This is your “before” number. The fix is one index that matches the filter first and the sort second: CREATE INDEX CONCURRENTLY idx_orders_customer_created ON orders (customer_id, created_at); Postgres can now jump straight to one customer’s rows, already in date order. Postgres can read a B-tree index backwards, so it works for DESC too. The sort step goes away. On safety: use CONCURRENTLY on a live database. A plain CREATE INDEX blocks writes to the table while it builds. CONCURRENTLY does not block writes, but it takes longer and cannot run inside a transaction. Run it at a quiet time, and use a direct connection to the cluster instead of the connection pool. If a concurrent build fails, it leaves an invalid index behind. Check for it, drop it and try again: SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid; DROP INDEX CONCURRENTLY idx_orders_customer_created; When the index is built, refresh the statistics and run the same EXPLAIN again: ANALYZE orders; You want to see Index Scan using idx_orders_customer_created and a much lower execution time. If you still see a Seq Scan, check that your WHERE clause uses the same columns as the index. The Query Statistics section of your cluster in the control panel should also show your slowest queries, so you can confirm this is the one to fix first. If you want to practice reading plans before touching production, this PostgreSQL psql simulator lets you run EXPLAIN, add an index and watch a sequential scan turn into an index scan. Nothing connects to a real server. The official guide to reading plans is here too: Using EXPLAIN

KFSys · Answer

Heya, Both halves of your question have concrete answers, and the second one (“will it lock my table”) has a subtlety that usually gets left out, so I rebuilt your scenario locally to get real numbers: PostgreSQL 18.6, an orders table with 2,000,000 rows and 20,000 customers, 243 MB. Same shape as yours. The absolute milliseconds below will not match your cluster, because mine is a local container with a warm cache. The plan shapes are what transfer , and those are what you are actually diagnosing. 1. First, confirm it is really that query Before tuning anything, check pg_stat_statements. It ranks by total time, which is the right measure, since a 5 ms query called 40,000 times hurts more than a 3 second query called twice. CREATE EXTENSION IF NOT EXISTS pg_stat_statements; SELECT round(mean_exec_time::numeric, 1) AS avg_ms, calls, round(total_exec_time::numeric) AS total_ms, left(regexp_replace(query, '\s+', ' ', 'g'), 62) AS query FROM pg_stat_statements WHERE dbid = (SELECT oid FROM pg_database WHERE datname = current_database()) ORDER BY total_exec_time DESC LIMIT 5; avg_ms | calls | total_ms | query --------+-------+----------+------------------------------------------------ 43.8 | 6 | 263 | SELECT count(*) FROM orders WHERE status=$1 9.0 | 6 | 54 | SELECT * FROM orders WHERE customer_id=$1 OR... If CREATE EXTENSION fails because the library is not loaded, that is a cluster-level setting rather than something you can fix from SQL, so check the extension list for your cluster first. You do not get superuser on a managed cluster, which is normal and not your problem to solve. 2. How to read EXPLAIN without reading all of it Ignore the cost= numbers. They are the planner’s guesses in arbitrary units and they are not milliseconds. Run EXPLAIN (ANALYZE, BUFFERS) so you get real measurements, then look at four things only . Here is your query before any index: EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE customer_id = 4242 ORDER BY created_at DESC LIMIT 20; Limit (actual time=28.636..30.651 rows=20.00 loops=1) Buffers: shared hit=14547 read=11148 -> Gather Merge (actual time=28.634..30.647 rows=20.00 loops=1) Workers Planned: 2 -> Sort (actual time=27.025..27.028 rows=14.67 loops=3) Sort Key: created_at DESC Sort Method: quicksort Memory: 28kB -> Parallel Seq Scan on orders (actual time=2.102..26.962 rows=31.00 loops=3) Filter: (customer_id = 4242) Rows Removed by Filter: 666636 Execution Time: 30.690 ms Read it bottom-up. The deepest node runs first. Seq Scan on a big table with a Filter is the headline. Postgres is reading every row. Rows Removed by Filter: 666636 is the smoking gun. Each of the 3 workers threw away 666,636 rows to find 31. That is ~2M rows read to return 20. actual time=start..end and rows are the truth. Note loops=3: the numbers are per loop , so multiply by 3 for the real total. This trips up everyone at least once. Buffers: shared hit=14547 read=11148 means 11,148 blocks came from disk rather than cache. read being large is what makes a query erratic between runs. The other line worth knowing is Sort Method. quicksort Memory: 28kB is fine. If you ever see external merge Disk: 84000kB , the sort spilled to disk and work_mem is too small for that query. 3. Which index, and why the obvious one may not be enough Adding the obvious index: CREATE INDEX idx_cust ON orders(customer_id); Limit (actual time=0.310..0.312 rows=20.00 loops=1) -> Sort (actual time=0.309..0.310 rows=20.00 loops=1) Sort Key: created_at DESC Sort Method: top-N heapsort Memory: 29kB -> Bitmap Heap Scan on orders (actual time=0.022..0.287 rows=93.00 loops=1) Recheck Cond: (customer_id = 4242) Execution Time: 0.333 ms 30.690 ms to 0.333 ms. Done, for that customer. But look at what is still there: a Sort node. That customer had 93 orders, so sorting 93 rows costs nothing. Your slow page is presumably a customer with a lot of orders, and that is where this index stops being enough. I gave one customer 200,099 orders and ran the same query: Limit (actual time=10.429..12.264 rows=20.00 loops=1) Buffers: shared hit=2785 -> Gather Merge -> Sort (actual time=8.885..8.887 rows=20.00 loops=3) Sort Key: created_at DESC -> Parallel Bitmap Heap Scan on orders (actual time=1.975..5.962 rows=66699.67 loops=3) Recheck Cond: (customer_id = 777) Execution Time: 12.264 ms It fetches 200,099 rows , sorts all of them, and returns 20. The index found the rows but could not help with the ordering. Put the sort column in the index, in the order you sort by: CREATE INDEX idx_cust_created ON orders(customer_id, created_at DESC); Limit (actual time=0.018..0.071 rows=20.00 loops=1) Buffers: shared hit=23 -> Index Scan using idx_cust_created on orders (actual time=0.017..0.069 rows=20.00 loops=1) Index Cond: (customer_id = 777) Execution Time: 0.086 ms The Sort node is gone. The index already holds the rows in the required order, so it reads 20 and stops: 2,785 buffers down to 23, and 12.264 ms down to 0.086 ms. That is the general rule worth taking away: equality columns first, then the ORDER BY column. Once the Sort node disappears from your plan, a LIMIT becomes almost free no matter how large the customer gets. If this index covers the single-column one, drop idx_cust, since a leading-column prefix is already usable. 4. Will it lock the table? Measured, not guessed Short answer: CREATE INDEX does not block reads, but it does block writes for the whole build. CREATE INDEX CONCURRENTLY blocks neither. I built the index both ways while hammering the table with reads and writes from another connection: build method build time worst SELECT during build worst UPDATE during build CREATE INDEX 0.65 s 5.2 ms 311.6 ms CREATE INDEX CONCURRENTLY 0.88 s 1.7 ms 37.4 ms So for a live production table: CREATE INDEX CONCURRENTLY idx_cust_created ON orders(customer_id, created_at DESC); Three things to know about CONCURRENTLY before you run it: It is slower (here about 35%) because it scans the table twice. On a 2M row table that is seconds; budget for minutes on a big one. It cannot run inside a transaction. If your migration tool wraps everything in BEGIN, you get: ERROR: CREATE INDEX CONCURRENTLY cannot run inside a transaction block Most tools have a flag for this (disable_ddl_transaction! in Rails, atomic = False in Django). If it fails partway it leaves an invalid index behind that still costs you writes. Check afterwards and drop it if needed: SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid; 5. The part that actually causes the outages This is the bit I would most want to know in your position, because it is not about the index at all. Postgres grants table locks in arrival order , and a statement that cannot get its lock waits at the head of the queue . Everything arriving after it waits too, even queries whose locks would have been perfectly compatible with whatever is actually holding the table. I measured this yesterday. Four sessions: a slow query running, then an ALTER TABLE, then two identical single-row primary key lookups 700 ms apart. arrives t+0.0s A long SELECT waited 3838.8 ms arrives t+0.3s C1 reader BEFORE the ALTER waited 1.2 ms arrives t+0.5s B ALTER TABLE waited 3340.8 ms arrives t+1.0s C2 reader AFTER the ALTER waited 2841.2 ms Same query, 1.2 ms versus 2,841 ms, and the ALTER TABLE itself runs in 1.3 ms once it has the lock. pg_blocking_pids() confirms C2 is blocked by the ALTER, not by the long query. CREATE INDEX CONCURRENTLY avoids this because it never asks for the strong lock. But any ordinary schema change you run later on this table can do it, so set a lock timeout on your migration session: SET lock_timeout = '5s'; ALTER TABLE orders ADD COLUMN whatever text; The migration then fails with ERROR: canceling statement due to lock timeout and you retry it, instead of your site stalling for as long as your slowest query takes. Do not set this globally, only on the session doing the DDL. When something does hang, this tells you who to blame in one query: SELECT pid, state, wait_event_type, pg_blocking_pids(pid) AS blocked_by, left(regexp_replace(query, '\s+', ' ', 'g'), 60) AS query FROM pg_stat_activity WHERE datname = current_database() AND state <> 'idle' ORDER BY backend_start; Watch especially for sessions in state idle in transaction . Those hold their locks while doing nothing at all, and one of them is enough to make a schema change hang forever with your whole application queued behind it. The short version EXPLAIN (ANALYZE, BUFFERS), read bottom-up, look for Seq Scan plus a large Rows Removed by Filter. Index equality columns first, ORDER BY column second: (customer_id, created_at DESC). Check the Sort node disappears. Use CREATE INDEX CONCURRENTLY on live data, outside a transaction, and check for an invalid index if it fails. Set lock_timeout on any session that runs schema changes, and keep an eye on idle in transaction. If you paste your actual EXPLAIN (ANALYZE, BUFFERS) output I am happy to read it with you.

Become a contributor for community

DigitalOcean Documentation

Resources for startups and AI-native businesses

The developer cloud

Start building today

Report this

How do I find out why one query is slow on my Managed PostgreSQL cluster?

Related questions

Why does my app fail to connect to my Managed PostgreSQL cluster with "no pg_hba.conf entry"?

Should I use a Managed Database or run PostgreSQL on a Droplet?

Should I use Managed PostgreSQL or just run Postgres on my Droplet?

App Platform - update DB pool without detach

Upgrading to postgres 18 with postgis installed

Postgres 18 availability?

How to create a schema in a Dev Database?

When can we expect Postgres 15.14 Managed Database support to be available?

Postgres - Database extension pg_stat_statement is activ but table doesn't exists

How do I automatically back up a DigitalOcean Managed PostgreSQL database to Spaces?

Trying to connect to postgresql on droplet programmatically via python and pg8000/psycopg2

Further PostgreSQL Extension Support Roadmap

Report this

How do I find out why one query is slow on my Managed PostgreSQL cluster?

Related questions

Why does my app fail to connect to my Managed PostgreSQL cluster with "no pg_hba.conf entry"?

Should I use a Managed Database or run PostgreSQL on a Droplet?

Should I use Managed PostgreSQL or just run Postgres on my Droplet?

App Platform - update DB pool without detach

Upgrading to postgres 18 with postgis installed

Postgres 18 availability?

How to create a schema in a Dev Database?

When can we expect Postgres 15.14 Managed Database support to be available?

Postgres - Database extension pg_stat_statement is activ but table doesn't exists

How do I automatically back up a DigitalOcean Managed PostgreSQL database to Spaces?

Trying to connect to postgresql on droplet programmatically via python and pg8000/psycopg2

Further PostgreSQL Extension Support Roadmap