Re: Early WIP/PoC for inlining CTEs

Gavin Flower <gavinflower@archidevsys.co.nz>

From: Gavin Flower <GavinFlower@archidevsys.co.nz>

To: pgsql-hackers@lists.postgresql.org

Date: 2019-01-22T05:13:45Z

Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

Prevent inlining of multiply-referenced CTEs with outer recursive refs.
- 9476131278c7 12.0 landed
Allow user control of CTE materialization, and change the default behavior.
- 608b167f9f9c 12.0 landed
Split QTW_EXAMINE_RTES flag into QTW_EXAMINE_RTES_BEFORE/_AFTER.
- 18c0da88a5d9 12.0 landed
document when PREPARE uses generic plans
- fab9d1da4a21 9.6.0 cited

On 22/01/2019 02:40, Andreas Karlsson wrote:
> On 1/18/19 9:34 PM, Robert Haas wrote:
>> On Thu, Jan 17, 2019 at 10:48 AM Andreas Karlsson <andreas@proxel.se> 
>> wrote:
>>> On 1/11/19 8:10 PM, Robert Haas wrote:
>>>> WITH cte_name [[NOT] MATERIALIZED] AS (query) main_query...
>>>
>>> Hm, when would one want "NOT MATERIALIZED"? I am not sure I see the
>>> usefulness of forcing inlining other than if we by default do not 
>>> inline
>>> when a CTE is referenced multiple times.
>>
>> When the planner materializes it, but the performance of the resulting
>> plan therefore sucks, I suppose.
>>
>> I don't feel super-strongly about this, and Tom is right that there
>> may be cases where materialization is just not practical due to
>> implementation restrictions.  But it's not crazy to imagine that
>> inlining a multiply-referenced CTE might create opportunities for
>> optimization at each of those places, perhaps not the same ones in
>> each case, whereas materializing it results in doing extra work.
>
> I see.
>
> I have a minor biksheddish question about the syntax.
>
> You proposed:
>
> WITH cte_name [[NOT] MATERIALIZED] AS (query) main_query
>
> While Andrew proposed:
>
> WITH cte_name AS [[NOT] MATERIALIZED] (query) main_query
>
> Do people have any preference between these two?
>
> Andreas
>
+1

For putting the 'AS' earlier, 2nd option,  I think it reads better.


Cheers,
Gavin