Re: Optimizer : query rewrite and execution plan ?

On Wed, 2008-02-06 at 11:00 -0500, Tom Lane wrote:
> Theo Kramer <theo(at)flame(dot)co(dot)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