Subject: Re: Optimizer : query rewrite and execution plan ?
From: Simon Riggs <simon@2ndquadrant.com>
To: SURANTYN Jean =?ISO-8859-1?Q?Fran=E7ois?= <jfsurant@supermarchesmatch.fr>
Cc: pgsql-performance@postgresql.org
In-Reply-To: 
 <60F4687513E90748AE8933DEA5D054E79ACBDE@SLAM0018.match-supermarket.com>
References: 
 <60F4687513E90748AE8933DEA5D054E79ACBDE@SLAM0018.match-supermarket.com>
Content-Type: text/plain; charset=utf-8
Date: Wed, 06 Feb 2008 11:53:46 +0000
Message-Id: <1202298826.4252.972.camel@ebony.site>
Mime-Version: 1.0
Content-Transfer-Encoding: 8bit

On Wed, 2008-02-06 at 09:42 +0100, SURANTYN Jean François wrote:

> That issue is very annoying because with generated SQL queries (from
> Business Objects for example) on big tables, it is possible that some
> queries have several times the same "where" condition ("where n=1 and
> n=1" for example), and as the optimizer is under-estimating the number
> of returned rows, some bad execution plans can be chosen (nested loops
> instead of hash joins for example)

I can see the annoyance there.

There's a balance in the planner between time spent to optimize the
query and time spent to correct mistakes. If we looked continually for
mistakes then planning time would increase for everybody that didn't
suffer from this problem.

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.

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