Subject: Re: Optimizer : query rewrite and execution plan ?
From: Simon Riggs <simon@2ndquadrant.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: theo@flame.co.za, pgsql-performance@postgresql.org
In-Reply-To: <9924.1202313626@sss.pgh.pa.us>
References: 
 <60F4687513E90748AE8933DEA5D054E79ACBDE@SLAM0018.match-supermarket.com>
	<1202298826.4252.972.camel@ebony.site>
	<1202299962.4274.22.camel@localhost6.localdomain6>
	<9924.1202313626@sss.pgh.pa.us>
Content-Type: text/plain
Date: Mon, 11 Feb 2008 09:44:47 +0000
Message-Id: <1202723087.4247.150.camel@ebony.site>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

On Wed, 2008-02-06 at 11:00 -0500, Tom Lane wrote:
> Theo Kramer <theo@flame.co.za> writes:
> > On Wed, 2008-02-06 at 11:53 +0000, Simon Riggs wrote:
> >> Since the SQL is not your fault and difficult to control, it is an
> >> argument in favour of an optional planner mode that would perform
> >> additional checks for redundant clauses of various kinds. The default
> >> for that would be "off" since most people don't suffer from this
> >> problem. BO isn't the only SQL generating-client out there, so I think
> >> this is a fairly wide problem.

> We used to have code that removed duplicate WHERE clauses (check the
> revision history for prepqual.c).  It was taken out because it consumed
> excessive amounts of planning time without accomplishing a darn thing
> for most queries.  There is no chance that it will be put back in as the
> only behavior, or even the default behavior, but I can see the reasoning
> for offering an option as Simon suggests.

I was wondering if we might do that automatically? It seems easy to
imagine a switch, but I wonder if we'd be able to set it correctly in
enough situations to make it worthwhile.

Say if cost of best plan >= N then recheck query for strangeness. If
anything found, re-plan query.

That way we only pay the cost of checking for longer queries and we only
actually re-plan for queries that will benefit.

-- 
  Simon Riggs
  2ndQuadrant  http://www.2ndQuadrant.com