Message-ID: <47A9822A.1000800@archonet.com>
Date: Wed, 06 Feb 2008 09:47:22 +0000
From: Richard Huxton <dev@archonet.com>
User-Agent: Thunderbird 2.0.0.9 (X11/20071031)
MIME-Version: 1.0
To: =?UTF-7?B?U1VSQU5UWU4gSmVhbiBGcmFuK0FPYy1vaXM=?=
	<jfsurant@supermarchesmatch.fr>
CC: pgsql-performance@postgresql.org
Subject: Re: Optimizer : query rewrite and execution plan ?
References: 
 <60F4687513E90748AE8933DEA5D054E79ACBDE@SLAM0018.match-supermarket.com>
In-Reply-To: 
 <60F4687513E90748AE8933DEA5D054E79ACBDE@SLAM0018.match-supermarket.com>
Content-Type: text/plain; charset=UTF-7
Content-Transfer-Encoding: quoted-printable

SURANTYN Jean Fran+AOc-ois wrote:
> my+AF8-db=3D+ACM- explain select +ACo- from test where n =3D 1;

> my+AF8-db=3D+ACM- explain select +ACo- from test where n =3D 1 and n =3D=
 1;

> In the first SELECT query (with "where n=3D1"), the estimated number of
> returned rows is correct (10), whereas in the second SELECT query
> (with "where n=3D1 and n=3D1"), the estimated number of returned rows i=
s
> 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=3D1 and n=3D1" for example), and as the optimizer i=
s
> 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 e=
nd.

> 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 ?=20

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 ?=20

It does, just not this one. It spots things like a=3Db and b=3Dc implies =
a=3Dc
(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=3D1) 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=3D2 AND n=3D(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?

--=20
  Richard Huxton
  Archonet Ltd