Skip to main content

Databases · Advanced · 16 min

How to Find and Fix Slow PostgreSQL Queries with EXPLAIN ANALYZE

By Padding · Updated 15 Nov 2026
Ubuntu 26.04.1 LTS · PostgreSQL 18

You will read a PostgreSQL 18 query plan, add an index that turns a 40 ms table scan into a 0.2 ms lookup, and use pg_stat_statements to find which queries deserve that effort.

On this page

A slow query is a query that does more work than it needs to. PostgreSQL will tell you exactly what work it did, if you ask. EXPLAIN shows the plan it chose. EXPLAIN ANALYZE runs the query and adds what actually happened. Reading those two outputs is the core skill of query tuning.

In this tutorial you will build a table with a million rows, read the plan for a slow query, fix it with an index, and learn the three ways an index fails to get used. At the end you will turn on pg_stat_statements, which tells you which queries are worth fixing first. The plans shown were produced by PostgreSQL 18, so your numbers will differ, but the shapes will match.

The tutorial assumes PostgreSQL 18 is installed as in /tutorials/install-postgresql-18-on-ubuntu-26-04.

Step 1 · Create a table large enough to be slow

Create a database called tuning, a customers table with 50,000 rows and an orders table with a million.

Bash
sudo -u postgres createdb tuning
sudo -u postgres psql -d tuning <<'SQL'
CREATE TABLE customers (
  id serial PRIMARY KEY,
  email text NOT NULL,
  name text NOT NULL
);
INSERT INTO customers (email, name)
  SELECT 'User' || g || '@Example.com', 'Customer ' || g
  FROM generate_series(1, 50000) AS g;

CREATE TABLE orders (
  id bigserial PRIMARY KEY,
  customer_id integer NOT NULL,
  status text NOT NULL,
  created_at timestamptz NOT NULL,
  total_cents integer NOT NULL
);
INSERT INTO orders (customer_id, status, created_at, total_cents)
  SELECT 1 + (random() * 49999)::int,
         (ARRAY['new', 'paid', 'shipped', 'refunded'])[1 + (random() * 3)::int],
         now() - random() * interval '365 days',
         500 + (random() * 20000)::int
  FROM generate_series(1, 1000000);

ANALYZE;
SQL

Loading the rows takes a few seconds. The ANALYZE at the end gathers the column statistics the planner uses to guess how many rows a query will return. A freshly loaded table without it gets worse plans. Autovacuum does this for you on a running system, but only after a delay.

Every command below runs one statement with psql -c. If you prefer to work in an interactive session, start one with sudo -u postgres psql -d tuning and type the SQL inside the quotes at the tuning=# prompt. The plans are the same either way.

Check it worked

Bash
sudo -u postgres psql -d tuning -c '\dt+'

The listing has two tables, customers and orders, and orders is the larger by far, tens of megabytes.

Step 2 · Read your first plan with EXPLAIN

Put EXPLAIN in front of a query to see the plan without running it. Ask for one customer's orders:

Bash
sudo -u postgres psql -d tuning -c "EXPLAIN SELECT * FROM orders WHERE customer_id = 4242;"

The output looks like this:

Text
 Gather  (cost=1000.00..14404.43 rows=21 width=30)
   Workers Planned: 2
   ->  Parallel Seq Scan on orders  (cost=0.00..13402.33 rows=9 width=30)
         Filter: (customer_id = 4242)

Read a plan from the innermost, most indented line outward. Each line is a node, and a node hands its rows to the node above it.

  • Seq Scan reads every row of the table. Parallel means several worker processes share that job, and Gather collects their results.
  • cost=0.00..13402.33 is the planner's estimate, in arbitrary units. The first number is the cost to produce the first row, the second the cost to produce all of them. Compare costs between plans of the same query. Do not read them as milliseconds.
  • rows=21 is the planner's guess at how many rows come out, and width=30 is the average row size in bytes.

A sequential scan to find about 20 rows in a million is the pattern to look for. EXPLAIN alone cannot tell you how long it took, only what the planner expects.

Step 3 · Measure it with EXPLAIN ANALYZE

EXPLAIN ANALYZE executes the query and reports the real timings. Add BUFFERS to see how many 8 kB pages it touched, which is a good measure of the work.

Bash
sudo -u postgres psql -d tuning -c "EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE customer_id = 4242;"
Text
 Gather  (cost=1000.00..14404.43 rows=21 width=30) (actual time=24.202..41.469 rows=23.00 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   Buffers: shared hit=8194
   ->  Parallel Seq Scan on orders  (cost=0.00..13402.33 rows=9 width=30) (actual time=8.780..36.846 rows=7.67 loops=3)
         Filter: (customer_id = 4242)
         Rows Removed by Filter: 333326
         Buffers: shared hit=8194
 Planning Time: 0.147 ms
 Execution Time: 41.498 ms

Every node now has a second set of numbers, actual time=...:

  • actual time=8.780..36.846 is milliseconds to the first row and to the last row, for one loop.
  • rows=7.67 loops=3 means each of the three processes returned 7.67 rows on average. PostgreSQL 18 prints a fractional average here, where older versions rounded it. Multiply rows by loops to get the total.
  • Rows Removed by Filter: 333326 is the line to notice. The scan read and threw away about a million rows to keep 23.
  • Buffers: shared hit=8194 means 8,194 pages came from PostgreSQL's cache. A read= figure would mean pages fetched from the operating system or disk.
  • Execution Time is the total, and it is the figure to compare before and after a change.

Compare the estimate with the actual number of rows. The planner guessed 21 and 23 came back, which is close. When the two differ by a factor of ten or more, the statistics are stale or the planner cannot model the condition, and plans built on that guess are often poor. Running ANALYZE on the table is the first fix to try.

EXPLAIN ANALYZE really runs the statement. Wrapping a SELECT in it is harmless, but an UPDATE, DELETE or INSERT changes your data. When you need the plan of a write, run it inside a transaction and roll back: BEGIN; EXPLAIN ANALYZE UPDATE ...; ROLLBACK;.

Step 4 · Fix it with an index

An index lets PostgreSQL find rows by value without reading the whole table. The default type is a B-tree. Index the column the query filters on.

Bash
sudo -u postgres psql -d tuning -c "CREATE INDEX orders_customer_id_idx ON orders (customer_id);"

On a table with live traffic, add CONCURRENTLY (CREATE INDEX CONCURRENTLY ...). It builds the index without blocking writes, takes longer, and cannot run inside a transaction block.

Run the same query again:

Bash
sudo -u postgres psql -d tuning -c "EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE customer_id = 4242;"
Text
 Bitmap Heap Scan on orders  (cost=4.59..85.66 rows=21 width=30) (actual time=0.049..0.150 rows=23.00 loops=1)
   Recheck Cond: (customer_id = 4242)
   Heap Blocks: exact=23
   Buffers: shared hit=23 read=3
   ->  Bitmap Index Scan on orders_customer_id_idx  (cost=0.00..4.58 rows=21 width=0) (actual time=0.035..0.035 rows=23.00 loops=1)
         Index Cond: (customer_id = 4242)
         Index Searches: 1
         Buffers: shared read=3
 Execution Time: 0.194 ms

The scan now reads 26 pages instead of 8,194, and the time drops from about 41 ms to about 0.2 ms. The plan has two nodes. A Bitmap Index Scan looks up the matching row locations in the index, and a Bitmap Heap Scan visits only those table pages. For a query that returns very few rows you may instead see a plain Index Scan. Both are good.

An index is not free. Each one takes disk space and slows every INSERT, UPDATE and DELETE on the table a little. Add indexes for queries that matter, not for every column.

Check it worked

The Execution Time is now below 1 ms, and the plan names orders_customer_id_idx. If the plan still shows Seq Scan, see the troubleshooting list below.

Troubleshooting

  • The plan still shows Seq Scan on a small table: that is correct. For a table that fits in a few pages, reading everything is cheaper than using an index.
  • The plan still shows Seq Scan after you created an index: run ANALYZE orders; and try again. Check that the query's condition matches the index column's type, such as comparing an integer column with a number rather than a quoted string of another type.
  • The query returns a large share of the table, for example status = 'paid' with a quarter of the rows. A sequential scan is the right plan there, and the planner is not wrong to choose it.

Step 5 · Spot the three reasons an index is ignored

Three things commonly stop PostgreSQL from using an index: a function wrapped around the column, an index whose columns are in the wrong order for the query, and a LIKE pattern that starts with a wildcard. The first is the most common, so start there. Customers type their email in any case, so the application compares lower-cased values:

Bash
sudo -u postgres psql -d tuning -c "EXPLAIN (ANALYZE) SELECT * FROM customers WHERE lower(email) = 'user77@example.com';"
Text
 Seq Scan on customers  (cost=0.00..1208.00 rows=250 width=39) (actual time=0.047..21.104 rows=1.00 loops=1)
   Filter: (lower(email) = 'user77@example.com'::text)
   Rows Removed by Filter: 49999
 Execution Time: 21.127 ms

An index on email could not help here, because the query asks about lower(email), not email. Index the expression itself:

Bash
sudo -u postgres psql -d tuning -c "CREATE INDEX customers_lower_email_idx ON customers (lower(email));"
sudo -u postgres psql -d tuning -c "EXPLAIN (ANALYZE) SELECT * FROM customers WHERE lower(email) = 'user77@example.com';"

The plan switches to something like a Bitmap Index Scan on customers_lower_email_idx, and the time falls from about 21 ms to well under 1 ms.

The same rule covers other wrappers on the column: casts, date_trunc(...) and arithmetic. Where you can, move the work to the other side of the comparison (created_at >= '2026-01-01' instead of date(created_at) = ...), or index the expression. The other two causes are covered next, for column order, and by a short rule for LIKE: a B-tree index cannot search a pattern such as LIKE '%text', because it starts with a wildcard.

Step 6 · Make an index match sort order

Queries with ORDER BY ... LIMIT are where a multi-column index pays off. Fetch the 20 newest paid orders:

Bash
sudo -u postgres psql -d tuning -c "EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE status = 'paid' ORDER BY created_at DESC LIMIT 20;"

Expect a plan something like this: it reads about 8,000 pages, filters out the rows that are not paid, and has a Sort node with Sort Method: top-N heapsort. It takes tens of milliseconds, all to return 20 rows. Create an index that matches the filter column first, then the sort column:

Bash
sudo -u postgres psql -d tuning -c "CREATE INDEX orders_status_created_idx ON orders (status, created_at DESC);"
sudo -u postgres psql -d tuning -c "EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE status = 'paid' ORDER BY created_at DESC LIMIT 20;"

Something like the following appears: the Sort node is gone, an Index Scan using orders_status_created_idx reads the index in order, and the Limit node stops after 20 rows. The buffer count drops from thousands to a few dozen. The index works because the entries for one status are already stored sorted by created_at. Put the equality column first and the sort column second.

Step 7 · Find which queries to fix with pg_stat_statements

You rarely know which query to tune. The pg_stat_statements extension records the time spent in every query shape. It has to be loaded when the server starts, so it needs a restart. First check the setting, because the next command replaces whatever is in it.

Bash
sudo -u postgres psql -Atc "SHOW shared_preload_libraries;"

If the output is empty, go ahead. If it lists libraries, include them in the new value, separated by commas, for example 'pg_stat_statements,other_lib'.

Bash
sudo -u postgres psql -c "ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements';"

ALTER SYSTEM writes the setting to postgresql.auto.conf in the data directory. Restart PostgreSQL so it takes effect. Active connections are dropped, so pick a quiet moment on a live system.

Bash
sudo systemctl restart postgresql

Create the extension in the database you want to watch, then run something so there is data:

Bash
sudo -u postgres psql -d tuning -c "CREATE EXTENSION pg_stat_statements;"
sudo -u postgres psql -d tuning -c "SELECT * FROM orders WHERE customer_id = 77;" > /dev/null

Now list the statements that used the most total time:

Bash
sudo -u postgres psql -d tuning -c "SELECT calls, round(total_exec_time::numeric, 1) AS total_ms, round(mean_exec_time::numeric, 2) AS mean_ms, left(query, 60) AS query FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 5;"

The output has one row per distinct query, with the literal values replaced by $1, $2, and so on. Sort by total_exec_time to find the queries that cost the most overall, which is often a cheap query that runs a million times. Sort by mean_exec_time to find individual slow queries. Take the top one, run it under EXPLAIN (ANALYZE, BUFFERS) with real values, and apply what you learned above.

To start measuring from a clean slate after a change, reset the counters:

Bash
sudo -u postgres psql -d tuning -c "SELECT pg_stat_statements_reset();"

Next steps

  • Make sure you can recover when a change goes wrong: /tutorials/back-up-and-restore-postgresql-with-pg-dump.
  • Keep the database off the public internet: /tutorials/set-up-ufw-firewall-ubuntu-26-04.
  • The reference for plan output and every EXPLAIN option is the PostgreSQL manual, chapter "Performance Tips", at postgresql.org/docs/18.

Need this done for you?

Padding does this work for growing companies, from the first diagnosis to a system that keeps working.

Platform Engineering

Get new tutorials

New tutorials, about twice a month.