Limit

A Limit passes through the first N rows from the node below it and then stops asking. Everything underneath stops with it. SELECT * FROM commerce.orders WHERE customer_id = $1 ORDER BY placed_at DESC LIMIT 20 is a Limit over an Index Scan on orders_customer_placed_idx: the index delivers that customer's orders newest first, the Limit takes 20, and the scan stops at 20.

Why it matters

A Limit is only cheap when the node below produces rows one at a time in the order you asked for. Above a Sort of the whole table it saves nothing but output: the Sort has to see every row before it knows which 20 come first. Compare the rows the Limit emitted with the rows its child produced. 20 and 20: it did its job. 20 and 12,000,000: the work happened anyway. OFFSET counts too. LIMIT 20 OFFSET 100000 reads and discards 100,000 rows each time.

What to do

1. Give the Limit an ordered source: an index on the ORDER BY columns, with the WHERE columns in front.
2. Replace deep OFFSET paging with keyset paging: WHERE (placed_at, id) < ($1, $2) ORDER BY placed_at DESC, id DESC LIMIT 20. Each page then starts from the index, not from row one.
3. Check the estimate on the node below. The planner costs a Limit assuming matches turn up early. When they are rare and sit at the end, that reads most of the table before the first row. Estimated rows explains the assumption.

In the Plan tab

The band narrows to the Limit's row count. Compare its width with the band coming in, then look at the time strip. When the segment for the node under the Limit is the wide one, the Limit arrived too late to help.