How to optimize PostgreSQL query performance

Quick answer: Rank queries by total_exec_time in pg_stat_statements, pull the plan for the worst one with EXPLAIN (ANALYZE, BUFFERS), then apply a fix that returns exactly the same rows, a sargable predicate, the correct join type, keyset pagination carrying the last-seen key, or an index. A rewrite that changes the result set is not an optimization. It is a bug.

What does "optimizing a query" actually mean?

Three steps, in order. Skipping straight to step three is how people optimize the wrong query.

1. Find what is expensive, cumulative time, not the query that felt slow once.
2. Read the plan, find out why it is expensive before changing anything.
3. Apply the smallest correct fix, a rewrite, an index, or better statistics.

And one rule that governs all of step three: every rewrite must return the same rows as the original, or you must say plainly what changed. Most "SQL optimization tips" circulating online fail this test. Several of them are on this page, marked as the traps they are.

Which queries should I optimize first?

The ones with the highest total_exec_time in pg_stat_statements, which is calls × mean_exec_time. That is usually a cheap query running constantly, not the three-second report you already know about.

SELECT calls, total_exec_time, mean_exec_time, rows, query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

On PostgreSQL 12 and earlier these columns are total_time and mean_time; PostgreSQL 13 renamed them to total_exec_time / mean_exec_time when it split planning time out from execution time. That is the only version note for these pg_stat_statements columns, other sections below carry their own (PostgreSQL 18's implicit BUFFERS, and 17/18 index skip scan).

Full detail: how to identify slow queries in PostgreSQL.

How do I find out why a query is slow?

Get the plan. Guessing from the SQL text is how you end up adding an index the planner never uses.

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total, created_at FROM orders WHERE customer_id = 42;

(BUFFERS became implied by ANALYZE in PostgreSQL 18. Writing it explicitly is harmless and still correct on older versions.)

Read estimated rows against actual rows first, a large gap means the planner optimized for a table that does not exist, and no rewrite will fix that until you ANALYZE. Full walkthrough: how to read a PostgreSQL EXPLAIN ANALYZE plan.

A note on sequential scans, because this is the most over-diagnosed thing in Postgres: a Seq Scan is not inherently bad. Reading a whole table is the correct, cheapest plan when the query needs most of the rows, or when the table is small. The signal worth chasing is a Seq Scan that returns few rows, which shows up as a large Rows Removed by Filter beside a small actual rows. That is work thrown away, and it is what an index eliminates.

Which rewrites are actually safe?

Each of these keeps the result set identical, with the conditions for that equivalence spelled out.

Make the predicate sargable

A predicate is sargable when the planner can use it to seek an index. Wrapping the indexed column in a function usually prevents that, because the index stores created_at, not date_trunc('day', created_at).

-- Not sargable: the column is inside a function call
SELECT id FROM orders
WHERE date_trunc('day', created_at) = DATE '2026-03-01';

-- Sargable: a half-open range on the bare column
SELECT id FROM orders
WHERE created_at >= TIMESTAMP '2026-03-01'
  AND created_at <  TIMESTAMP '2026-03-02';

Equivalence: identical for a timestamp column. Note the half-open range.>= and <, never BETWEEN, which is inclusive on both ends and would wrongly include midnight of the 2nd. For a timestamptz column both forms depend on the session TimeZone setting. Pin the zone on the literal side.created_at >= (TIMESTAMP '2026-03-01' AT TIME ZONE 'America/Chicago'), which keeps the column bare and the predicate sargable. Applying AT TIME ZONE to the column wraps it in a function and forfeits the index.

When you genuinely need the function, a case-insensitive lookup, say, do not contort the query. Index the expression instead, which keeps the predicate unchanged:

CREATE INDEX CONCURRENTLY ON customers (lower(email));
-- now WHERE lower(email) = 'a@example.com' can use an index

Select only the columns you use

-- Reads every column, including wide text/jsonb you discard
SELECT * FROM orders WHERE customer_id = 42;

-- Reads three columns; can also enable an index-only scan
SELECT id, total, created_at FROM orders WHERE customer_id = 42;

This one deliberately changes the output. That is the entire point, and it is why it is honest to call out. The rows are the same; the columns are fewer. Do it only where the application truly ignores the rest, and remember that SELECT * in a view or a RETURNING clause may be load-bearing for callers you cannot see.

Push the LIMIT into the database

If the application slices the first 20 results in code, say so in SQL so the planner can stop early. ORDER BY without a deterministic tiebreaker makes LIMIT non-deterministic across ties, so include a unique column.

SELECT id, total FROM orders
ORDER BY created_at DESC, id DESC
LIMIT 20;

Equivalence: the same 20 rows the application would have kept , provided the ordering is total. Adding LIMIT to a query whose results are all consumed downstream changes behaviour and is not an optimization.

Use the join type that preserves your rows

This is the classic trap. A scalar subquery in the select list yields NULL and keeps the outer row when there is no match:

-- Keeps every order, even orphaned ones (name is NULL)
SELECT o.id, o.total,
       (SELECT c.name FROM customers c WHERE c.id = o.customer_id) AS customer_name
FROM orders o;

Rewriting that as an INNER JOIN silently drops orders with no matching customer. The equivalent rewrite is a LEFT JOIN:

SELECT o.id, o.total, c.name AS customer_name
FROM orders o
LEFT JOIN customers c ON c.id = o.customer_id;

Equivalence: holds only when customers.id is unique (it is the primary key here, so it is). If the joined side were not unique, the join would multiply the outer rows, whereas the scalar subquery would have raised more than one row returned by a subquery used as an expression. Check uniqueness on the join key before converting; a scalar subquery quietly encodes an at-most-one assumption that a join does not.

IN vs EXISTS: it is about NULLs, not speed

The widely repeated claim that EXISTS is faster because it "stops at the first match" is pre-8.4 folklore. The PostgreSQL docs describe IN with a subquery as equivalent to = ANY, and note that, as with EXISTS. It is unwise to assume the subquery is evaluated completely. In practice the planner turns both uncorrelated forms into the same semi-join, and you can confirm that in the plan.

-- These two typically produce the same plan
SELECT * FROM orders o
WHERE o.customer_id IN (SELECT c.id FROM customers c WHERE c.region = 'west');

SELECT * FROM orders o
WHERE EXISTS (SELECT 1 FROM customers c
              WHERE c.id = o.customer_id AND c.region = 'west');

Where they are genuinely not interchangeable is the negated form. If the subquery can return NULL, NOT IN yields NULL rather than true for every outer row that has no match, rows that do match evaluate to false, so nothing comes back either way:

-- deleted_by is nullable, most customers were never deleted, so the subquery
-- returns NULLs. That makes this return zero rows. (A PK column like
-- customers.id could not demonstrate this: it can never be NULL.)
SELECT * FROM orders WHERE customer_id NOT IN (SELECT deleted_by FROM customers);

-- This returns the orphaned orders regardless of NULLs, different results
SELECT * FROM orders o
WHERE NOT EXISTS (SELECT 1 FROM customers c WHERE c.id = o.customer_id);

Prefer NOT EXISTS for anti-joins, but understand you are changing the semantics, not just the syntax. Only swap when the NOT EXISTS behaviour is what you actually wanted.

Keyset pagination, done properly

Deep OFFSET is slow because Postgres still reads and discards every skipped row. The fix is real, and the version you will see quoted most often is wrong:

-- Slow at depth
SELECT id, total FROM orders ORDER BY id LIMIT 20 OFFSET 10000;

-- ✗ NOT equivalent. OFFSET skips 10,000 ROWS; this filters on the VALUE of id.
SELECT id, total FROM orders WHERE id > 10000 ORDER BY id LIMIT 20;

Those agree only if ids are gapless, start at 1, and nothing was ever deleted. With gaps, soft deletes, or a UUID key they return different pages.

Real keyset pagination carries the last-seen key from the previous page forward as a cursor. The application returns the last row's key with each page and passes it back in:

-- Page 1
SELECT id, total FROM orders ORDER BY id LIMIT 20;
-- app remembers the id of the last row → :last_seen_id

-- Page N+1
SELECT id, total FROM orders
WHERE id > :last_seen_id
ORDER BY id
LIMIT 20;

Sorting by a non-unique column needs a unique tiebreaker and a row-comparison cursor, plus a composite index that matches the sort:

SELECT id, total FROM orders
WHERE (created_at, id) < (:last_created_at, :last_id)
ORDER BY created_at DESC, id DESC
LIMIT 20;

CREATE INDEX CONCURRENTLY ON orders (created_at, id);

What changes: keyset pagination gives you the same sequence of pages when walking forward, but it cannot jump to an arbitrary page number, because it has no notion of "row 10,000". If your UI has numbered page links, that is a product decision, not a free swap. More ORM-side detail in how to optimize ORM-generated queries.

One more trap: OR is not UNION

Splitting WHERE a = 1 OR b = 2 into two branches can help the planner use two different indexes, but the naive rewrite changes results. UNION ALL duplicates rows that match both conditions; UNION deduplicates, which also collapses genuinely distinct rows that happen to be identical. If you split an OR, add an explicit predicate to the second branch so the branches are disjoint (... WHERE b = 2 AND a IS DISTINCT FROM 1), and verify the row counts match before and after.

When is the fix an index rather than a rewrite?

Often. If the plan shows a Seq Scan returning a small fraction of a big table, or a nested loop re-scanning an inner table per outer row, no amount of SQL tidying will help, the column has no usable index.

• Confirm which tables are being read end-to-end: how to find missing indexes in PostgreSQL.
• Pick the right access method. B-tree is right most of the time, but jsonb, arrays, full-text and spatial want GIN or GiST: which PostgreSQL index type should I use?
• PostgreSQL does not index foreign-key columns for you. It creates indexes for primary keys and unique constraints only. The referencing column stays un-indexed until you add it.
• Indexes are not free. Every index adds work to every INSERT, UPDATE and DELETE on that table, plus disk and vacuum overhead. Adding ten indexes to fix one query is a net loss.
• On column order: through PostgreSQL 17 an index on (a, b) could technically still be used with no predicate on a, but only by scanning the entire index, so the planner usually preferred a sequential scan and the leftmost-prefix rule held in practice. PostgreSQL 18 added B-tree skip scan, which relaxes it when the leading column has few distinct values. Qualify the advice by your server version rather than repeating it as a law , multicolumn indexes and skip scan.

Before concluding an index is missing, run ANALYZE on the table. Stale statistics counterfeit a lot of "missing index" symptoms, and the fix there is free.

Why is aI-generated SQL slow?

Because your coding agent writes SQL that is locally correct against a database it has never seen. It does not know the table has 40 million rows, that customer_id has no index, or that the endpoint gets paged to depth 8,000. It also tends to reproduce the exact folklore above.EXISTS over IN, or pagination that filters on the id value instead of carrying a cursor, because that advice is everywhere in its training data and nowhere is it marked as wrong. Longer version: why AI-generated SQL is slow.

How do I keep queries fast?

• Review the top of the total_exec_time list on a schedule, not after an incident.
• Diff the row counts before and after any rewrite. Run both versions with EXCEPT in both directions, or just compare count(*) on a representative slice. This single habit would have caught every trap on this page.
• Pull a plan before and after, if the plan shape did not change, neither did your performance.
• Re-ANALYZE after bulk loads and large deletes.
• Audit indexes periodically for write cost, not just read benefit.

How DBGorilla helps

DBGorilla connects to your database read-only and works through your AI coding agent (Claude Code, Cursor), giving it the facts it is otherwise guessing at: the real pg_stat_statements ranking, the actual query plan, which columns are indexed, and the true row counts. So instead of proposing a rewrite from memory, your agent can point at the query dominating production and explain what the plan is really doing. It surfaces and explains. It does not rewrite your queries, create indexes, or make changes to production. Get started free →

FAQ

Is EXISTS faster than IN in PostgreSQL? Not as a rule. That is pre-8.4 advice. The docs treat IN with a subquery as equivalent to = ANY, and warn (as with EXISTS) against assuming the subquery is evaluated completely; the planner usually produces the same semi-join for both. The real difference is NULL handling: NOT IN over a list containing NULL yields NULL, excluding the row, where NOT EXISTS keeps it.

Is WHERE id > 10000 the same as OFFSET 10000? No. OFFSET skips 10,000 rows; the predicate filters on the value of id. With gaps, deletes, or a non-sequential key they return different pages. Proper keyset pagination carries the last-seen key forward from the previous page.

Can I replace a scalar subquery with a JOIN? Only with a LEFT JOIN, and only when the joined table is unique on the join key. The subquery returns NULL and keeps the outer row when nothing matches; an INNER JOIN drops it, and a non-unique join key would multiply rows.

Is a sequential scan always a problem? No. It is the correct plan for a small table or a query reading most of the rows. Act on a Seq Scan that returns few rows, which shows up in EXPLAIN ANALYZE as a large Rows Removed by Filter.

Related

• How to identify slow queries in PostgreSQL
• How to read a PostgreSQL EXPLAIN ANALYZE plan
• How to find missing indexes in PostgreSQL
• How to optimize ORM-generated queries
• Product: DBGorilla docs