Skip site navigation (1) Skip section navigation (2)

Re: Planner producing 100% duplicate subplans when unneeded

From: Robert Haas <robertmhaas(at)gmail(dot)com>
To: Daniel Grace <dgrace(at)wingsnw(dot)com>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: Planner producing 100% duplicate subplans when unneeded
Date: 2010-10-04 21:50:47
Message-ID: AANLkTinMrQ7gB0wEs3SKM-R4UnrN-gLuOAAW0tOH_sv4@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-bugs
On Mon, Oct 4, 2010 at 4:15 PM, Daniel Grace <dgrace(at)wingsnw(dot)com> wrote:
> As a theoretical question (I'm not savvy on Postgres's code but might
> be intrigued enough to beat on it anyways), is it feasible to do an
> additional pass on the query plan that essentially goes:
>
> - Are these two subplans identical?
> - Are they at the same part of the tree?
>
> and if both of these conditions are true, discard one subplan and
> rewrite all references to point to the other one?
>
> Assuming it IS possible, are there any particular situations where it
> wouldn't work?

Well, volatile functions would be a problem, but I don't think that's
really the way to go anyway.  I think that deciding whether or not to
collapse subqueries into the main query (which is what's happening
here) seems like it could be done by counting the number of times any
given subexpression is referenced, which seems like it would be a lot
cheaper than checking things against each other pairwise to see if
they match up.  Slowing down the planner to detect cases like this
wouldn't be very appealing:

SELECT (SELECT SUM(a) FROM bob, SELECT SUM(b) FROM bob, SELECT SUM(c) FROM bob);

...because very few people are going to write that query that way
anyway, and unless it's almost free to notice the duplication, it
doesn't make sense to spend planning time on it.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

In response to

pgsql-bugs by date

Next:From: Robert HaasDate: 2010-10-04 21:54:28
Subject: Re: BUG #5684: pg_restore does not restore schemas
Previous:From: Daniel GraceDate: 2010-10-04 20:15:48
Subject: Re: Planner producing 100% duplicate subplans when unneeded

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group