On 09/01/2016 09:58 PM, Andres Freund wrote:
> On 2016-09-01 15:46:45 -0400, Tom Lane wrote:
>> Tomas Vondra <tomas.vondra@2ndquadrant.com> writes:
>>> While investigating a CTE-related query, I've noticed that we don't
>>> really remove all unreachable CTEs.
>>
>> We expend a grand total of three lines of code on making that happen.
>> I'm pretty much -1 on adding a great deal more code or complexity
>> to make it happen recursively;
>
> Agreed. And the consequences are pretty much harmless.
>
I'm not sure I agree with that. The trouble is that we have customers,
and they don't always write "reasonably well written queries",
particularly when those queries are a bit more complex. And I have to
debug issues with those queries from time to time.
I'd actually be much happier if we haven't removed any CTEs at all,
instead of removing just some of them.
This incomplete CTE removal actually confused the hell out of me today,
which is why I started this thread. I only realized some of the CTEs are
useless after the EXPLAIN ANALYZE completed (as this was a performance
issue, it took a very long time).
>
>> the case simply doesn't arise in reasonably well written queries.
>
> Well, it might, when the CTE reference can be removed due to some other
> part of the query (e.g. plan time evaluation of immutable function).
>
We also never remove CTEs with INSERT/UPDATE/DELETE commands, which
makes it a bit more complicated (but those are easy to spot).
FWIW, I'm not planning to hack on this, but I was thinking that this
might be a nice topic for a first patch for new PostgreSQL hackers
(something like EasyHacks from LibreOffice).
regards
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services