A PostgreSQL query is fast without LIMIT but slow with ORDER BY ... LIMIT
SELECT ... ORDER BY created_at DESC LIMIT 10 takes seconds, while the same query without LIMIT is fastShort 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.
Fix it fast
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;2Match what you see, then jump to its fix
- 1
A Sort node sits above a scan that reads every matching rowNo index returns rows in the ORDER BY orderFix - 2
Index 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
Estimated rows are far from actual rows right after a big load or deleteTable statistics are staleFix - 4
Page 1 is fast, deep pages get slower with every stepA large OFFSET makes the server compute rows it throws awayFix
- 1
What does the error look like?
SELECT ... ORDER BY created_at DESC LIMIT 10 takes seconds, while the same query without LIMIT is fastEXPLAIN 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
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
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
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
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 fixDatabases
DBeaver cannot run pg_restore: the utility is not found in the client home
Utility 'pg_restore' not found in client home '/usr/lib' (/usr/lib)
DBeaver runs pg_restore from a local PostgreSQL client folder; it does not bundle one.
3 causes, each with a fixDatabases
DBeaver cannot download the PostgreSQL driver from Maven
The PostgreSQL driver files are missing and the download does not complete
DBeaver downloads the PostgreSQL JDBC driver from a Maven repository the first time you connect.
3 causes, each with a fixDatabases
python manage.py migrate finishes but the tables for my app are missing
relation "myapp_book" does not exist
migrate only applies migration files that exist.
3 causes, each with a fixDatabases
Django save fails on PostgreSQL: null value in a column violates a not-null constraint
django.db.utils.IntegrityError: null value in column "price" of relation "shop_product" violates not-null constraint
A model field is NOT NULL in the database unless it has null=True.
3 causes, each with a fixDatabases
Django migration on PostgreSQL stops with 'pending trigger events'
django.db.utils.OperationalError: cannot ALTER TABLE "mytable" because it has pending trigger events
PostgreSQL runs a Django migration in one transaction.
2 causes, each with a fix