Hello, dear hackers!

There is task in todo list about optional CTE optimization fence 

I am not interested at this point in disabling mechanism 
implementation, but I would like to discuss the optimization 
mechanism, that should work when the fence is disabled.

It seems, that we can replace CTE with subquery, so the optimizer 
can do all available optimizations. This idea is quite 
straightforward, but I could not find a discussion of it. 
(Maybe it is so, because everyone knows that the idea is bad and it is 
not worth to discuss. But I hope it is not, so I start this thread. =))

First of all, to such replacement to be valid, the CTE must be 
    1. non-writable (e.g. be of form: SELECT ...),
    2. do not use VOLATILE or STABLE functions,
    3. ... (maybe there must be more restrictions?) 

Also, before inlining, we should check that some optimization 
can be applied, using functions from 
'pull_up_subqueries_recurse' and 'subquery_push_qual'.

If it is true, and there only one reference to CTE, 
we can inline it immediately.

What it is not clear is how we should estimate whether it is worth 
to inline, when there is multiple references. Here are my preliminary

Let consider "pull up subquery" and "push down qualifiers" cases 

For "push down qualifiers", if `subquery_push_qual` is `true`, 
we can do the following: 
    1. copy CTE subquery,
    2. push down quals,
    3. find paths,
    3. inline if cost of 
        (CTE scan) > (cheapest_path(subquery) + subquery scan) 

Probably, this approach is not feasible, because it involves subquery 
replaning, and we should consider a more "lightweight" heuristic.

For "pull up subquery" similar approach may lead to duplicate planning 
of the whole query, that almost sure is too expensive.
So I wonder, is it possible to estimate a join predicate selectivity 
against CTE subquery result and inline it if selectivity is "high" enough?
(If it is possible the same can be applied to the first case.)

I would be glad to hear feedback on described approach.

Ilya Shkuratov

Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:

Reply via email to