From: Richard Huxton <dev@archonet.com>
To: SURANTYN Jean Fran+AOc-ois <jfsurant@supermarchesmatch.fr>
Cc: pgsql-performance@postgresql.org
Subject: Re: Optimizer : query rewrite and execution plan ?
Date: Wed, 06 Feb 2008 09:47:22 +0000
Message-ID: <47A9822A.1000800@archonet.com> (raw)
In-Reply-To: <60F4687513E90748AE8933DEA5D054E79ACBDE@SLAM0018.match-supermarket.com>
References: <60F4687513E90748AE8933DEA5D054E79ACBDE@SLAM0018.match-supermarket.com>
SURANTYN Jean Fran+AOc-ois wrote:
> my+AF8-db=+ACM- explain select +ACo- from test where n = 1;
> my+AF8-db=+ACM- explain select +ACo- from test where n = 1 and n = 1;
> In the first SELECT query (with "where n=1"), the estimated number of
> returned rows is correct (10), whereas in the second SELECT query
> (with "where n=1 and n=1"), the estimated number of returned rows is
> 5 (instead of 10 !) So the optimizer has under-estimated the number
> of rows returned
That's because it's a badly composed query. The planner is guessing how
much overlap there would be between the two clauses. It's not exploring
the option that they are the same clause repeated.
> 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)
Sounds like your query-generator needs a bit of an improvement, from my end.
> Is the estimated number of returned rows directly linked to the
> decision of the optimizer to chose Hash Joins or Nested Loops in join
> queries ?
Yes, well the cost determines a plan and obviously number of rows
affects the cost.
> Is there a way for the Postgresql optimizer to be able to
> simplify and rewrite the SQL statements before running them ?
It does, just not this one. It spots things like a=b and b=c implies a=c
(for joins etc).
> Are
> there some parameters that could change the execution plans ?
Not really in this case.
The root of your problem is that you have a query with an irrelevant
clause (AND n=1) and you'd like the planner to notice that it's
irrelevant and remove it from the query. There are two problems with this:
1. It's only going to be possible in simple cases. It's unlikely the
planner would ever spot "n=2 AND n=(10/5)"
2. Even in the simple case you're going to waste time checking +ACo-every
query+ACo- to see if clauses could be eliminated.
Is there any way to improve your query generator?
--
Richard Huxton
Archonet Ltd
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: dev@archonet.com, jfsurant@supermarchesmatch.fr
Subject: Re: Optimizer : query rewrite and execution plan ?
In-Reply-To: <47A9822A.1000800@archonet.com>
* 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