Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

A PostgreSQL query is fast without LIMIT but slow with ORDER BY ... LIMIT

ErrorSELECT ... ORDER BY created_at DESC LIMIT 10 takes seconds, while the same query without LIMIT is fast

Short answer

With LIMIT, the planner bets that a plan can stop early. If no index gives the ORDER BY order, it sorts every matching row. If the bet is wrong because of bad row estimates, it scans a long index and filters most rows away. Check EXPLAIN ANALYZE, then add a matching index, fix statistics, or page by key.

  • Applies to PostgreSQL 16, PostgreSQL 17, PostgreSQL 18
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    Run the query with EXPLAIN (ANALYZE, BUFFERS) and compare estimated rows with actual rows on each node. It runs the query, so wrap data-changing statements in a transaction you roll back.

    EXPLAIN (ANALYZE, BUFFERS)
    SELECT * FROM events
    WHERE account_id = 42
    ORDER BY created_at DESC
    LIMIT 10;
  2. 2Match what you see, then jump to its fix

    1. 1A Sort node sits above a scan that reads every matching rowNo index returns rows in the ORDER BY orderFix
    2. 2Index Scan on the sort column alone, with a large Rows Removed by Filter, because the matching rows are far apart in that orderThe planner walks the ORDER BY index and filters away almost everythingFix
    3. 3Estimated rows are far from actual rows right after a big load or deleteTable statistics are staleFix
    4. 4Page 1 is fast, deep pages get slower with every stepA large OFFSET makes the server compute rows it throws awayFix

What does the error look like?

  • SELECT ... ORDER BY created_at DESC LIMIT 10 takes seconds, while the same query without LIMIT is fast
  • EXPLAIN ANALYZE shows Rows Removed by Filter in the millions under a Limit node

Cause 1 of 4

No index returns rows in the ORDER BY order

1Check

Only a B-tree index can return sorted output. Without one that matches both the filter and the sort, PostgreSQL has to read all matching rows and sort them before it can apply LIMIT.

SELECT indexdef FROM pg_indexes WHERE tablename = 'events';

2Fix

Create a multicolumn B-tree index with the equality column first and the sort column next. A backward scan serves ORDER BY ... DESC, so DESC in the index is only needed when you mix directions.

CREATE INDEX events_account_created_idx ON events (account_id, created_at);

3Verify

EXPLAIN ANALYZE now shows an Index Scan under Limit and the sort node is gone.

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM events WHERE account_id = 42 ORDER BY created_at DESC LIMIT 10;

Confirmed by postgresql.org

Did this fix it?

Cause 2 of 4

The planner walks the ORDER BY index and filters away almost everything

1Check

With LIMIT, a plan that reads an index in sort order and stops after n rows looks cheap. If the filter matches few rows or they sit at the far end of the index, it reads far more than expected. Compare estimated rows with actual rows.

SELECT attname, n_distinct FROM pg_stats WHERE tablename = 'events';

2Fix

Give the planner a better index (the multicolumn one from the first cause). If the filter uses several correlated columns, create extended statistics and analyze, so the row estimate stops assuming independence.

CREATE STATISTICS events_acc_type (dependencies) ON account_id, type FROM events;
ANALYZE events;

3Verify

Estimated and actual rows are now close, and the plan changed.

EXPLAIN ANALYZE
SELECT * FROM events WHERE account_id = 42 AND type = 'login' ORDER BY created_at DESC LIMIT 10;

Confirmed by postgresql.org

Did this fix it?

Cause 3 of 4

Table statistics are stale

1Check

The planner reads row counts and value distributions that VACUUM and ANALYZE update. They are not updated on the fly.

SELECT relname, reltuples, relpages FROM pg_class WHERE relname = 'events';

2Fix

Run ANALYZE on the table. For a column with an irregular spread of values, raise its statistics target.

ANALYZE events;
ALTER TABLE events ALTER COLUMN account_id SET STATISTICS 500;
ANALYZE events;

3Verify

Run EXPLAIN ANALYZE again and compare the row estimates.

EXPLAIN ANALYZE SELECT * FROM events WHERE account_id = 42 ORDER BY created_at DESC LIMIT 10;

Confirmed by postgresql.org

Did this fix it?

Cause 4 of 4

A large OFFSET makes the server compute rows it throws away

1Check

The manual says rows skipped by OFFSET still have to be computed inside the server.

EXPLAIN ANALYZE SELECT * FROM events ORDER BY id LIMIT 20 OFFSET 200000;

2Fix

Page by key instead of offset: remember the last id of the previous page and ask for rows after it. Keep a unique ordering, so pages are stable.

SELECT * FROM events
WHERE id > 200123
ORDER BY id
LIMIT 20;

3Verify

The deep page now takes about as long as the first one.

EXPLAIN ANALYZE SELECT * FROM events WHERE id > 200123 ORDER BY id LIMIT 20;

Confirmed by postgresql.org

Did this fix it?

How do I stop it coming back?

  • Design the index from the query: equality columns first, then the ORDER BY column.
  • Always give ORDER BY a unique tie-breaker, such as the primary key, when you page.
  • Add extended statistics only for column groups that you filter on together and that give bad estimates.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Related fixes

Databases

A jsonb query is slow and the GIN index is never used

WHERE doc -> 'tags' ? 'qui' takes seconds on a large table

A GIN index on a jsonb column only helps queries whose operator it supports and whose left side is exactly the indexed expression.

3 causes, each with a fix

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.