From: Andrew Gierth <andrew@tao11.riddles.org.uk>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Michael Paquier <michael@paquier.xyz>
Cc: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
Cc: Robert Haas <robertmhaas@gmail.com>
Cc: Andreas Karlsson <andreas@proxel.se>
Cc: Andres Freund <andres@anarazel.de>
Cc: Thomas Munro <thomas.munro@enterprisedb.com>
Cc: David Fetter <david@fetter.org>
Cc: Pg Hackers <pgsql-hackers@postgresql.org>
Subject: Re: Early WIP/PoC for inlining CTEs
Date: Sun, 03 Feb 2019 08:14:44 +0000
Message-ID: <87d0o970th.fsf@news-spur.riddles.org.uk> (raw)
In-Reply-To: <4591.1549134061@sss.pgh.pa.us>
References: <20190111190403.77ye7m5mxypxsf4l@alap3.anarazel.de>
<CA+TgmobVMPX1wJxPaRQ=FZBzp-AQ=JWK=bzxztqwj=MsOsrnrA@mail.gmail.com>
<00818be9-2f63-f584-efcd-d97ee0662905@proxel.se>
<CA+TgmoapASa3RQi=DuSikPoPF4363czSD8KxbOxsfW5XmO3WpQ@mail.gmail.com>
<5715d93e-6ebc-be17-c2c3-56a873100a38@proxel.se>
<29576.1548084476@sss.pgh.pa.us>
<CA+TgmoYZYj8eLdV9AcROtmPW4AJA7ANh3xQh7BcafaPfCnmv+g@mail.gmail.com>
<12408.1548707732@sss.pgh.pa.us>
<c16909ed-0789-5199-f315-!5433d83a0026@2ndquadrant.com>
<32184.1548713132@sss.pgh.pa.us>
<20190129045158.GD3121@paquier.xyz>
<17787.1548791564@sss.pgh.pa.us>
<4591.1549134061@sss.pgh.pa.us>
>>>>> "Tom" == Tom Lane <tgl@sss.pgh.pa.us> writes:
Tom> After further reflection I really don't like Andrew's suggestion
Tom> that we not document the rule that multiply-referenced CTEs won't
Tom> be inlined by default. That would be giving up the principle that
Tom> WITH calculations are not done multiple times by default, and I
Tom> draw the line at that. It's an often-useful behavior as well as
Tom> one that's been documented from day one, so I do not accept the
Tom> argument that we might someday override it on the basis of nothing
Tom> but planner cost estimates.
The case that springs to mind is when a CTE with grouping is then joined
multiple times in the main query with different conditions. If the
planner is able to deduce (e.g. via ECs) that restrictions on grouped
columns can be pushed into the CTE, then inlining the CTE multiple times
might be a significant win. But if that isn't possible, then inlining
multiple times might be a significant loss.
If such a query is made into a view, then (given your position) the
decision of whether to inline has to be made at view creation time,
which doesn't seem desirable (even if we have to put up with it for the
present).
--
Andrew (irc:RhodiumToad)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: andrew@tao11.riddles.org.uk, tgl@sss.pgh.pa.us, michael@paquier.xyz, peter.eisentraut@2ndquadrant.com, robertmhaas@gmail.com, andreas@proxel.se, andres@anarazel.de, thomas.munro@enterprisedb.com, david@fetter.org
Subject: Re: Early WIP/PoC for inlining CTEs
In-Reply-To: <87d0o970th.fsf@news-spur.riddles.org.uk>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox