why does LIMIT 2 take orders of magnitude longer than LIMIT 1 in this query? - Mailing list pgsql-hackers

<div dir="ltr">I'm on PostgreSQL 9.3.  This should reproduce on any table with 100,000+ rows.  The EXPLAIN ANALYZE
showsmany more rows getting scanned with LIMIT 2, but I can't figure out why.<br /><br />Limit 1:<br /><br /><div
style="margin-left:40px">EXPLAINANALYZE WITH base AS (<br />  SELECT *, ROW_NUMBER() OVER () AS rownum FROM
a_big_table<br/>), filter AS (<br />  SELECT rownum, true AS thing FROM base<br />) SELECT * FROM base LEFT JOIN filter
USING(rownum) WHERE filter.thing LIMIT 1<br /></div><br />Result:<br /><br /><div style="margin-left:40px">Limit 
(cost=283512.19..283517.66rows=1 width=2114) (actual time=0.019..0.019 rows=1 loops=1)<br />  CTE base<br />    -> 
WindowAgg (cost=0.00..188702.69 rows=4740475 width=101) (actual time=0.008..0.008 rows=1 loops=1)<br />          -> 
SeqScan on a_big_table  (cost=0.00..129446.75 rows=4740475 width=101) (actual time=0.003..0.003 rows=1 loops=1)<br /> 
CTEfilter<br />    ->  CTE Scan on base base_1  (cost=0.00..94809.50 rows=4740475 width=8) (actual time=0.000..0.000
rows=1loops=1)<br />  ->  Nested Loop  (cost=0.00..307677626611.24 rows=56180269915 width=2114) (actual
time=0.018..0.018rows=1 loops=1)<br />        Join Filter: (base.rownum = filter.rownum)<br />        ->  CTE Scan
onbase  (cost=0.00..94809.50 rows=4740475 width=2113) (actual time=0.011..0.011 rows=1 loops=1)<br />        ->  CTE
Scanon filter  (cost=0.00..94809.50 rows=2370238 width=9) (actual time=0.002..0.002 rows=1 loops=1)<br />             
Filter:thing<br />Total runtime: 0.057 ms<br /></div><br />Limit 2:<br /><br /><div style="margin-left:40px">EXPLAIN
ANALYZEWITH base AS (<br />  SELECT *, ROW_NUMBER() OVER () AS rownum FROM a_big_table<br />), filter AS (<br /> 
SELECTrownum, true AS thing FROM base<br />) SELECT * FROM base LEFT JOIN filter USING (rownum) WHERE filter.thing
LIMIT2<br /></div><br />Result:<br /><br /><div style="margin-left:40px">Limit  (cost=283512.19..283523.14 rows=2
width=2114)(actual time=0.018..14162.283 rows=2 loops=1)<br />  CTE base<br />    ->  WindowAgg 
(cost=0.00..188702.69rows=4740475 width=101) (actual time=0.008..4443.359 rows=4714243 loops=1)<br />          -> 
SeqScan on a_big_table  (cost=0.00..129446.75 rows=4740475 width=101) (actual time=0.002..1421.622 rows=4714243
loops=1)<br/>  CTE filter<br />    ->  CTE Scan on base base_1  (cost=0.00..94809.50 rows=4740475 width=8) (actual
time=0.001..10214.684rows=4714243 loops=1)<br />  ->  Nested Loop  (cost=0.00..307677626611.24 rows=56180269915
width=2114)(actual time=0.018..14162.280 rows=2 loops=1)<br />        Join Filter: (base.rownum = filter.rownum)<br
/>       Rows Removed by Join Filter: 4714243<br />        ->  CTE Scan on base  (cost=0.00..94809.50 rows=4740475
width=2113)(actual time=0.011..0.028 rows=2 loops=1)<br />        ->  CTE Scan on filter  (cost=0.00..94809.50
rows=2370238width=9) (actual time=0.009..6595.770 rows=2357122 loops=2)<br />              Filter: thing<br />Total
runtime:14247.374 ms<br /></div></div>