Limit in PostgreSQL EXPLAIN

What it does

Limit implements LIMIT and OFFSET (and FETCH FIRST … ROWS ONLY). It pulls rows from its child one at a time, skips the first OFFSET rows, passes on the next LIMIT rows, then stops. Because PostgreSQL’s executor pulls rows on demand, stopping the Limit stops everything below it too: an index scan that could return a million rows returns ten and quits.

Some children can’t stop early: a Sort must read all its input before returning the first row (though it switches to a cheaper top-N heapsort), and so must a HashAggregate.

When the planner picks it

Whenever the query has LIMIT, OFFSET or FETCH FIRST. The interesting decision is what the planner puts under it. With a limit, a plan with a low startup cost (first row fast) can beat one with a lower total cost:

• ORDER BY created_at DESC LIMIT 10 with an index on created_at: read the index backwards and stop after ten rows. No sort.
• Without a matching index: scan everything and keep the top ten in a heap.

The planner estimates how far it has to read before finding enough rows, assuming matching rows are spread evenly through the order it reads in. That assumption is where things go wrong.

Reading its numbers

Limit  (cost=0.42..0.77 rows=10 width=34) (actual time=0.080..0.082 rows=10.00 loops=1)
  ->  Index Scan Backward using orders_created_at_idx on orders  (cost=0.42..34317.43 rows=1000000 width=34) (actual time=0.079..0.080 rows=10.00 loops=1)

• The child’s estimate is for a full run (rows=1000000, cost up to 34,317), while its actual rows are what the Limit asked for (10). That gap isn’t a bad estimate. The PostgreSQL docs list it as a normal case where estimates and actuals differ.
• The Limit’s cost (0.42..0.77) is the child’s startup cost plus the fraction of its run cost needed for 10 of 1,000,000 rows. This is how the planner decides that an index scan is cheap under a limit.
• (never executed) on a node below a Limit means the limit was satisfied before that node was needed, for example later partitions of an ordered scan.
• Rows Removed by Filter on the child is the warning sign: it shows how many rows were read and discarded before enough matches were found.

When it’s a problem

The planner bets on finding matches early

SELECT … WHERE status = 'open' ORDER BY id DESC LIMIT 10: with no index on status, the planner can walk the primary key backwards and filter. It estimates 2,367 open tickets out of 500,000 (close to the real 2,000), assumes they’re spread evenly, and expects ten after about 2,100 rows. But every open ticket is among the oldest, at the far end of the index, so it reads 498,000 rows first. See the example.

Fixes:

1. An index that matches the filter and the order: CREATE INDEX ON tickets (status, id). The scan jumps straight to the open tickets, already in id order. (On a live table, use CREATE INDEX CONCURRENTLY.)
2. A partial index if the condition is fixed: CREATE INDEX ON tickets (id) WHERE status = 'open'. Small, and only maintained for open tickets.

Better statistics won’t help with this one: PostgreSQL’s statistics can’t express “the matches are all at one end of the table”. The index is the fix.

Large OFFSET

OFFSET 500000 LIMIT 10 still reads and discards 500,000 rows; there’s no way to jump to row 500,001. Page 1 is fast and page 50,000 is slow. Use keyset pagination instead: remember the last value shown and ask for the rows after it:

SELECT * FROM orders
WHERE created_at > '<last created_at on the previous page>'
ORDER BY created_at
LIMIT 10;

With an index on the sort column, every page costs the same. Add a unique column (such as id) to the sort and the comparison if the sort column can repeat.

A LIMIT without ORDER BY

Returns some rows, not the first or newest. Which ones can change between runs or after a vacuum. Always pair LIMIT with an ORDER BY that makes the result well defined.

Example

PostgreSQL 18.6, default settings, with the orders table from the other examples on this site (one row per minute from 1 January 2024, 1,000,000 rows, index on created_at) and a tickets table:

CREATE SCHEMA seo_explain;
SET search_path = seo_explain;

CREATE TABLE orders (
  id          bigint PRIMARY KEY,
  customer_id int NOT NULL,
  status      text NOT NULL,
  total       numeric(10,2) NOT NULL,
  created_at  timestamptz NOT NULL
);
INSERT INTO orders
SELECT i,
       1 + (i::bigint * 7919) % 50000,
       CASE WHEN i % 100 < 90 THEN 'shipped'
            WHEN i % 100 < 97 THEN 'pending'
            ELSE 'refunded' END,
       round(((i * 37) % 100000) / 100.0, 2),
       timestamptz '2024-01-01' + i * interval '1 minute'
FROM generate_series(1, 1000000) AS i;
CREATE INDEX orders_created_at_idx ON orders (created_at);

-- the oldest 2,000 tickets are still open; everything newer is closed
CREATE TABLE tickets (
  id         bigint PRIMARY KEY,
  status     text NOT NULL,
  subject    text NOT NULL,
  created_at timestamptz NOT NULL
);
INSERT INTO tickets
SELECT i, CASE WHEN i <= 2000 THEN 'open' ELSE 'closed' END, 'Ticket ' || i,
       timestamptz '2025-01-01' + i * interval '2 minutes'
FROM generate_series(1, 500000) AS i;
VACUUM ANALYZE orders, tickets;

(Our test orders table also had a foreign key and an index on customer_id; neither is used here.)

The ten newest orders:

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders ORDER BY created_at DESC LIMIT 10;

Limit  (cost=0.42..0.77 rows=10 width=34) (actual time=0.080..0.082 rows=10.00 loops=1)
  Buffers: shared hit=2 read=2
  ->  Index Scan Backward using orders_created_at_idx on orders  (cost=0.42..34317.43 rows=1000000 width=34) (actual time=0.079..0.080 rows=10.00 loops=1)
        Index Searches: 1
        Buffers: shared hit=2 read=2
Planning:
  Buffers: shared hit=11
Planning Time: 0.126 ms
Execution Time: 0.091 ms

The ten newest open tickets:

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM tickets WHERE status = 'open' ORDER BY id DESC LIMIT 10;

Limit  (cost=0.42..78.22 rows=10 width=35) (actual time=40.672..40.675 rows=10.00 loops=1)
  Buffers: shared hit=5515
  ->  Index Scan Backward using tickets_pkey on tickets  (cost=0.42..18415.42 rows=2367 width=35) (actual time=40.671..40.672 rows=10.00 loops=1)
        Filter: (status = 'open'::text)
        Rows Removed by Filter: 498000
        Index Searches: 1
        Buffers: shared hit=5515
Planning Time: 0.065 ms
Execution Time: 40.692 ms

The Limit’s cost (78) says the planner expected to read about 0.4% of the index. Rows Removed by Filter: 498000 says it read 99.6%. On a table a hundred times bigger, that’s seconds.

With an index on (status, id):

CREATE INDEX tickets_status_id_idx ON tickets (status, id);

Limit  (cost=0.42..15.43 rows=10 width=35) (actual time=0.024..0.026 rows=10.00 loops=1)
  Buffers: shared hit=1 read=3
  ->  Index Scan Backward using tickets_status_id_idx on tickets  (cost=0.42..3552.29 rows=2367 width=35) (actual time=0.024..0.024 rows=10.00 loops=1)
        Index Cond: (status = 'open'::text)
        Index Searches: 1
        Buffers: shared hit=1 read=3
Planning:
  Buffers: shared hit=25 read=1
Planning Time: 0.178 ms
Execution Time: 0.035 ms

Four pages, 0.035 ms.

A deep page with OFFSET:

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders ORDER BY created_at LIMIT 10 OFFSET 500000;

Limit  (cost=17254.42..17254.77 rows=10 width=34) (actual time=148.237..148.239 rows=10.00 loops=1)
  Buffers: shared read=5536
  ->  Index Scan using orders_created_at_idx on orders  (cost=0.42..34508.43 rows=1000000 width=34) (actual time=0.119..134.387 rows=500010.00 loops=1)
        Index Searches: 1
        Buffers: shared read=5536
Planning:
  Buffers: shared hit=85 read=3 dirtied=2
Planning Time: 0.399 ms
Execution Time: 148.289 ms

rows=500010.00 under a LIMIT 10: half the table read to show ten rows.

In Inlet

Inlet draws EXPLAIN ANALYZE as a tree and highlights the slowest step and badly misestimated row counts. The grid pages through tables for you, with filters and sorting run on the server.