Re: Planner features, discussion

On Wed, Jul 14, 2010 at 08:47:35AM +0800, Craig Ringer wrote:
> On 13/07/2010 10:52 PM, Greg Smith wrote:
>
> >I heard a scholarly treatment of that topic from Jim Nasby recently,
> >where he proposed a boolean GUC to toggle the expanded search behavior
> >to be named plan_the_shit_out_of_it.
>
> I was thinking that something like "duplicate subquery/function
> elimitation" might be handy, though an extension to WITH would
> eliminate the need for it (see below). Consider code like this:
>
> SELECT (SELECT somequery) FROM ...
> WHERE (SELECT SOMEQUERY) > somevalue
> ORDER BY (SELECT somequery)
>
> that invokes some non-trivial "somequery" several times. I often
> wanted to simplify it, and it wasn't always practical to convert it
> to add (SELECT somequery) to the join list.
>
> I expected that with 8.4 I'd be able to write something more along
> the lines of:
>
> WITH result = (SELECT somequery)
> SELECT result FROM ...
> WHERE result > somevalue
> ORDER BY result;
>
> which makes such an optimization less than necessary. Why complicate
> the planner when you can fix your SQL?
>
> However, in the case above the subquery needs to be referenced from
> a scalar context not as a join, and WITH expressions don't seem to
> be useful for scalar results. The names defined by WITH are only
> visible as FROM targets. So this doesn't work:
>
> => WITH aconstant(constval) AS (VALUES(1)) SELECT x.*, constval FROM
> generate_series(1,10) AS x;
> ERROR: column "constval" does not exist
> LINE 1: ...TH aconstant(constval) AS (VALUES(1)) SELECT x.*, constval F...

You missed the CROSS JOIN, which you could make implicit, even though
implicit CROSS JOINs are bad coding style:

WITH aconstant(constval) AS (VALUES(1))
SELECT x.*, constval
FROM
generate_series(1,10) AS x
CROSS JOIN
aconstant;
x | constval
----+----------
1 | 1
2 | 1
3 | 1
4 | 1
5 | 1
6 | 1
7 | 1
8 | 1
9 | 1
10 | 1
(10 rows)

> ... so you're forced to fall back on adding it as an additional join
> expression - which isn't always reasonable or possible.

Why not?

> Extending WITH to be useful for defining constants and
> single-evaluation variables like the above would be really, really
> nice, and would avoid some ugly SQL mangling and any need for
> compliated planner features that try to match up and combine
> subquery trees.

I'm all for extending WITH, as are some others. See this thread for
the latest:
<http://archives.postgresql.org/pgsql-hackers/2010-07/msg00463.php>

Cheers,
David (who's not mentioning extending WITH to include DCL or DDL yet...oops! ;)
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate