Re: why does LIMIT 2 take orders of magnitude longer than LIMIT 1 in this query?

Chris Rogers wrote
> I'm on PostgreSQL 9.3.  This should reproduce on any table with 100,000+
> rows.  The EXPLAIN ANALYZE shows many more rows getting scanned with LIMIT
> 2, but I can't figure out why.
> 
> EXPLAIN ANALYZE WITH base AS (
>   SELECT *, ROW_NUMBER() OVER () AS rownum FROM a_big_table
> ), filter AS (
>   SELECT rownum, true AS thing FROM base
> ) SELECT * FROM base LEFT JOIN filter USING (rownum) WHERE filter.thing
> LIMIT 1

The LIMIT 1 case has been optimized (special cased) while all others end up
using a normal plan.

Two things make your example query particularly unrealistic:

1. The presence of a ROW_NUMBER() window aggregate on an unsorted input
2. A LEFT JOIN condition matched with a WHERE clause with a right-side
column being non-NULL 

David J.



--
View this message in context:
http://postgresql.1045698.n5.nabble.com/why-does-LIMIT-2-take-orders-of-magnitude-longer-than-LIMIT-1-in-this-query-tp5825209p5825212.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.