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. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
