Re: Subquery Factoring ?

"Adam Rich" <adam(dot)r(at)sbcglobal(dot)net> writes:

> Are there any plans to support materialized subquery / factoring (sql-99
> WITH) in Postgresql? I am spoiled with this feature in oracle, and find
> myself wishing I had it in postgresql more and more. It *seems* to an
> outsider like a relatively easy addition.

We have the patch to support the syntax but we're a bit unsure whether that's
actually helpful. One thing we're missing is exactly what you could help with.

What I would like to know is what the user expectations are when using this
feature.

Are you using it just to avoid retyping a complex subquery? Or do you expect
that the feature will reduce the execution time by avoiding re-executing the
subquery for each call site in the query?

How disappointing would it be if the WITH clause acted as an optimization
barrier, preventing WHERE clauses from being pushed down and potentially using
indexes? Or if the query ended up not needing the data in the WITH subquery
but the query had to execute it anyways?

Another way of looking at this question is: if you called some volatile
function from the subquery such as one which printed diagnostic messages or
accessed some remote service, how many times would you expect it to be called?
Would you expect the feature to guarantee that the function would only be
called once or would it be ok if it were called 0 times if the subquery data
was never needed or many times if the optimizer thought that would be faster?

> I searched the archives but only found a brief mention of a
> syntax-support-only patch from last winter.

Yeah, it was the first step in implementing recursive queries. It was deemed
uninteresting without actual recursive query support, but there was some more
recent emails where someone else was interested in it so that decision may be
revisited for 8.4. And in any case there's still a good chance (though no
guarantee) that recursive queries will be done for 8.4 which will definitely
imply this feature.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's 24x7 Postgres support!