Re: Common Table Expressions applied; some issues remain

Is this a TODO?

---------------------------------------------------------------------------

Tom Lane wrote:
> [ back to the when-to-inline-WITHs discussion ]
>
> Gregory Stark <stark(at)enterprisedb(dot)com> writes:
> >> Tom Lane wrote:
> >>> Any thoughts on what to do? One possibility is to flatten only
> >>> if the subquery doesn't contain any volatile functions.
>
> > I think we should always inline the view if there's a single call site. If
> > people want to control the subsequent flattening they can do it the same way
> > they can do today for inline views using OFFSET 0.
>
> That's certainly a defensible choice, and in fact was what I had
> intended to do at one point (that's why CommonTableExpr.cterefcount
> is in there). However, the extent to which you can prevent duplicate
> evaluation in an inline view is actually pretty limited. As an example
> consider
>
> select ... from table1,
> (select expensive_function(...) from table2 offset 0) ss
> where table1.key = table2.key;
>
> If the planner chooses to do this as a nestloop with table2 on the
> inside, then expensive_function() can get evaluated multiple times on
> the same row of table2. We really don't make very many guarantees about
> what will happen with functions inside inlined views, even with "offset
> 0" as an optimization fence. So I was thinking that taking a strong
> reading of the spec's wording about single evaluation of WITH clauses
> might provide useful leverage for people who need to control evaluation
> of expensive or volatile functions better than they can now.
>
> Another possibility that we could think about is: if a CTE is only
> referenced once, then push down any restriction clauses that are
> available at the single call site, but still execute it using the
> CteScan materialization logic. The evaluation guarantee would then
> look like "no row of the CTE's result is evaluated twice, but some rows
> might not be evaluated at all". What we'd pay for this is that the CTE
> could not be the inside of a nestloop with inner indexscan using a join
> condition, since we don't have any way to keep track of which rows were
> already fetched in that case.
>
> regards, tom lane
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +