# 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.

```sql
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](https://www.dbgorilla.com/learn/postgres/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.

```sql
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](https://www.dbgorilla.com/learn/postgres/how-to-read-a-postgres-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)`.

```sql
-- 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:

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

### Select only the columns you use

```sql
-- 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.

```sql
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:

```sql
-- 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`:

```sql
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.

```sql
-- 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:

```sql
-- 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:

```sql
-- 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:

```sql
-- 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:

```sql
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](https://www.dbgorilla.com/learn/postgres/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](https://www.dbgorilla.com/learn/postgres/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?](https://www.dbgorilla.com/learn/postgres/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](https://www.dbgorilla.com/learn/postgres/do-i-still-need-the-leading-column-multicolumn-indexes/).

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](https://www.dbgorilla.com/learn/postgres/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 →](https://app.dbgorilla.com/signup)

## 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](https://www.dbgorilla.com/learn/postgres/how-to-identify-slow-queries-in-postgresql/)
- [How to read a PostgreSQL EXPLAIN ANALYZE plan](https://www.dbgorilla.com/learn/postgres/how-to-read-a-postgres-explain-analyze-plan/)
- [How to find missing indexes in PostgreSQL](https://www.dbgorilla.com/learn/postgres/how-to-find-missing-indexes-in-postgresql/)
- [How to optimize ORM-generated queries](https://www.dbgorilla.com/learn/postgres/how-to-optimize-orm-generated-queries/)
- Product: [DBGorilla docs](https://www.dbgorilla.com/docs/)