From: | David G Johnston <david(dot)g(dot)johnston(at)gmail(dot)com> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: why does LIMIT 2 take orders of magnitude longer than LIMIT 1 in this query? |
Date: | 2014-10-31 16:38:36 |
Message-ID: | 1414773516879-5825212.post@n5.nabble.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2014-10-31 16:42:15 | Re: why does LIMIT 2 take orders of magnitude longer than LIMIT 1 in this query? |
Previous Message | Simon Riggs | 2014-10-31 16:15:12 | Re: tracking commit timestamps |