Re: Early WIP/PoC for inlining CTEs

From: Andrew Gierth <andrew(at)tao11(dot)riddles(dot)org(dot)uk>
To: David Fetter <david(at)fetter(dot)org>
Cc: Andreas Karlsson <andreas(at)proxel(dot)se>, Thomas Munro <thomas(dot)munro(at)enterprisedb(dot)com>, Pg Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Early WIP/PoC for inlining CTEs
Date: 2018-10-06 02:48:02
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

>>>>> "David" == David Fetter <david(at)fetter(dot)org> writes:

>> Consider the difference between (in the absence of CTE inlining):
>> -- inline subquery with no optimization barrier (qual may be pushed down)
>> select * from (select x from y) s where x=1;

David> ...and doesn't need to materialize all of y,

>> -- inline subquery with optimization barrier (qual not pushed down)
>> select * from (select x from y offset 0) s where x=1;
>> -- CTE with materialization
>> with s as (select x from y) select * from s where x=1;

David> while both of these do.

The non-CTE one has to _evaluate_ the whole of the "s" subquery, but it
doesn't have to actually store the result, whereas the CTE version needs
to put it all in a tuplestore and read it back.

Andrew (irc:RhodiumToad)

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2018-10-06 02:58:37 Re: Performance improvements for src/port/snprintf.c
Previous Message Alvaro Herrera 2018-10-06 02:27:59 Re: Assertion failure with ALTER TABLE ATTACH PARTITION with log_min_messages >= DEBUG1