sql - why does LIMIT 2 take orders of magnitude longer than LIMIT 1 in this query? -

sql - why does LIMIT 2 take orders of magnitude longer than LIMIT 1 in this query? -

i'm on postgresql 9.3. should reproduce on table 100,000+ rows. explain analyze shows many more rows getting scanned limit 2, can't figure out why.

limit 1:

explain analyze base of operations ( select *, row_number() on () rownum a_big_table ), filter ( select rownum, true thing base of operations ) select * base of operations left bring together filter using (rownum) filter.thing limit 1

result:

limit (cost=283512.19..283517.66 rows=1 width=2114) (actual time=0.019..0.019 rows=1 loops=1) cte base of operations -> windowagg (cost=0.00..188702.69 rows=4740475 width=101) (actual time=0.008..0.008 rows=1 loops=1) -> seq scan on a_big_table (cost=0.00..129446.75 rows=4740475 width=101) (actual time=0.003..0.003 rows=1 loops=1) cte filter -> cte scan on base of operations base_1 (cost=0.00..94809.50 rows=4740475 width=8) (actual time=0.000..0.000 rows=1 loops=1) -> nested loop (cost=0.00..307677626611.24 rows=56180269915 width=2114) (actual time=0.018..0.018 rows=1 loops=1) bring together filter: (base.rownum = filter.rownum) -> cte scan on base of operations (cost=0.00..94809.50 rows=4740475 width=2113) (actual time=0.011..0.011 rows=1 loops=1) -> cte scan on filter (cost=0.00..94809.50 rows=2370238 width=9) (actual time=0.002..0.002 rows=1 loops=1) filter: thing total runtime: 0.057 ms

limit 2:

explain analyze base of operations ( select *, row_number() on () rownum a_big_table ), filter ( select rownum, true thing base of operations ) select * base of operations left bring together filter using (rownum) filter.thing limit 2

result:

limit (cost=283512.19..283523.14 rows=2 width=2114) (actual time=0.018..14162.283 rows=2 loops=1) cte base of operations -> windowagg (cost=0.00..188702.69 rows=4740475 width=101) (actual time=0.008..4443.359 rows=4714243 loops=1) -> seq scan on a_big_table (cost=0.00..129446.75 rows=4740475 width=101) (actual time=0.002..1421.622 rows=4714243 loops=1) cte filter -> cte scan on base of operations base_1 (cost=0.00..94809.50 rows=4740475 width=8) (actual time=0.001..10214.684 rows=4714243 loops=1) -> nested loop (cost=0.00..307677626611.24 rows=56180269915 width=2114) (actual time=0.018..14162.280 rows=2 loops=1) bring together filter: (base.rownum = filter.rownum) rows removed bring together filter: 4714243 -> cte scan on base of operations (cost=0.00..94809.50 rows=4740475 width=2113) (actual time=0.011..0.028 rows=2 loops=1) -> cte scan on filter (cost=0.00..94809.50 rows=2370238 width=9) (actual time=0.009..6595.770 rows=2357122 loops=2) filter: thing total runtime: 14247.374 ms

engine first runs, limits. so, can see much more rows.

sql postgresql join common-table-expression window-functions