Re: [HACKERS] CTE optimization fence on the todo list?
I need this feature a lot. Can anyone point me to a place in the code where I can hack together a quick-and-dirty, compatibility-breaking implementation? Thanks! On Sun, May 3, 2015 at 10:03 PM, Jim Nasby wrote: > On 5/3/15 11:59 AM, Andrew Dunstan wrote: > >> >> On 05/03/2015 11:49 AM, Tom Lane wrote: >> >>> Andrew Dunstan writes: >>> On 05/01/2015 07:24 PM, Josh Berkus wrote: > (A possible compromise position would be to offer a new GUC to >> enable/disable the optimization globally; that would add only a >> reasonably >> small amount of control code, and people who were afraid of the change >> breaking their apps would probably want a global disable anyway.) >> > This could be a very bad, almost impossible to catch, behaviour break. Even if we add the GUC, we're probably going to be imposing very significant code audit costs on some users. >>> On what grounds do you claim it'd be a behavior break? It's possible >>> that the subquery flattening would result in less-desirable plans not >>> more-desirable ones, but the results should still be correct. >>> >> >> I meant w.r.t. performance. Sorry if that wasn't clear. >> > > To put this in perspective... I've seen things like this take query > runtime from minutes to multiple hours or worse; bad enough that "behavior > break" becomes a valid description. > > We definitely need to highlight this in the release notes, and I think the > GUC would be mandatory. > -- > Jim Nasby, Data Architect, Blue Treble Consulting > Data in Trouble? Get it in Treble! http://BlueTreble.com > > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers >
Re: [HACKERS] CTE optimization fence on the todo list?
Has there been any movement on this in the last couple years? I could really use the ability to optimize across CTE boundaries, and it seems like a lot of other people could too.
[HACKERS] why does LIMIT 2 take orders of magnitude longer than LIMIT 1 in this query?
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. Limit 1: 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 Result: Limit (cost=283512.19..283517.66 rows=1 width=2114) (actual time=0.019..0.019 rows=1 loops=1) CTE base -> 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 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) Join Filter: (base.rownum = filter.rownum) -> CTE Scan on base (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 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 2 Result: Limit (cost=283512.19..283523.14 rows=2 width=2114) (actual time=0.018..14162.283 rows=2 loops=1) CTE base -> 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 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) Join Filter: (base.rownum = filter.rownum) Rows Removed by Join Filter: 4714243 -> CTE Scan on base (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
