Re: WITH RECUSIVE patches 0723

"Andrew Gierth" <andrew(at)tao11(dot)riddles(dot)org(dot)uk> writes:

>>>>>> "Tom" == Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
>
> Tom> This isn't going to be a particularly simple fix :-(. The basic
> Tom> implementation clearly ought to be to dump the result of the
> Tom> subquery into a tuplestore and then have the upper level read
> Tom> out from that.
>
> Which will be a serious pessimization in many common cases if you do
> it all the time. Googling for examples of non-recursive WITH queries
> shows that it is very widely used for clarity or convenience, in
> contexts where you _don't_ want materialization.

I just wonder where all these examples of real-world queries were when I
posted this patch and asked for such feedback originally. sigh.

In any case I think we've already made this decision. If we wanted the 80%
solution it was ready for Postgres 8.3. It wouldn't make much sense to skip it
then but put it in now when that there's time to finish it and a lot of the
work's already done.

I think the spec-compliant approach is clearly-superior. If we have the choice
there's no question we should do it properly.

In an ideal world we would then have logic to check if the semantics are
maintained if the subquery is inlined and detect cases where that would be an
advantage. One case that comes to mind would be if there's an indexable qual
that could be pushed down into it such as:

WITH foo(a) as (SELECT a
FROM tab
WHERE long complex condition
you only want to write once)
SELECT a from foo where a = 1
UNION ALL
SELECT a from foo where a = 2
UNION ALL
...

So I disagree with Tom that we should advertise this as the approved way to
disable subquery inlining. I would still suggest using OFFSET 0 for that. But
I also don't agree with you that this is more common than the converse. I
think if we have a choice between always materializing and always inlining
then always materializing is much better.

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