Re: Common Table Expressions applied; some issues remain

[ 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