From: Gregory Stark <stark@enterprisedb.com>
To: Andrew Gierth <andrew@tao11.riddles.org.uk>
Cc: pgsql-hackers@postgresql.org
Subject: Re: WITH RECUSIVE patches 0723
Date: Mon, 28 Jul 2008 21:30:13 +0100
Message-ID: <87k5f5sqei.fsf@oxford.xeocode.com> (raw)
In-Reply-To: <87myk1rg4z.fsf@news-spur.riddles.org.uk>
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>
"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!
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-patches@postgresql.org
Cc: stark@enterprisedb.com, andrew@tao11.riddles.org.uk, pgsql-hackers@postgresql.org
Subject: Re: WITH RECUSIVE patches 0723
In-Reply-To: <87k5f5sqei.fsf@oxford.xeocode.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox