From: Simon Riggs <simon@2ndquadrant.com>
To: SURANTYN Jean François <jfsurant@supermarchesmatch.fr>
Cc: pgsql-performance@postgresql.org
Subject: Re: Optimizer : query rewrite and execution plan ?
Date: Wed, 06 Feb 2008 11:53:46 +0000
Message-ID: <1202298826.4252.972.camel@ebony.site> (raw)
In-Reply-To: <60F4687513E90748AE8933DEA5D054E79ACBDE@SLAM0018.match-supermarket.com>
References: <60F4687513E90748AE8933DEA5D054E79ACBDE@SLAM0018.match-supermarket.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: simon@2ndquadrant.com, jfsurant@supermarchesmatch.fr
Subject: Re: Optimizer : query rewrite and execution plan ?
In-Reply-To: <1202298826.4252.972.camel@ebony.site>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox