To: "Andrew Gierth" <andrew@tao11.riddles.org.uk>
Cc: <pgsql-hackers@postgresql.org>
Subject: Re: WITH RECUSIVE patches 0723
In-Reply-To: <87myk1rg4z.fsf@news-spur.riddles.org.uk> (Andrew Gierth's
	message of "Mon\, 28 Jul 2008 19\:57\:16 +0100")
User-Agent: Gnus/5.11 (Gnus v5.11) Emacs/22.1 (gnu/linux)
References: <20080725001513.GF16438@fetter.org>
	<20080725.092652.105178884.t-ishii@sraoss.co.jp>
	<87r69gs967.fsf@news-spur.riddles.org.uk>
	<20080728.162925.23020498.t-ishii@sraoss.co.jp>
	<19882.1217256991@sss.pgh.pa.us>
	<87myk1rg4z.fsf@news-spur.riddles.org.uk>
From: Gregory Stark <stark@enterprisedb.com>
Organization: EnterpriseDB
Date: Mon, 28 Jul 2008 21:30:13 +0100
Message-ID: <87k5f5sqei.fsf@oxford.xeocode.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

"Andrew Gierth" <andrew@tao11.riddles.org.uk> writes:

>>>>>> "Tom" == Tom Lane <tgl@sss.pgh.pa.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!