Re: Optimizer : query rewrite and execution plan ?

Many thanks for your quick reply

In fact, that issue comes from a recent migration from Oracle to Postgresql, and even if some queries were not
optimizedby the past (example: where n=1 and n=1), Oracle was able to rewrite them and to "hide" the bad queries". But
nowthat we have migrated to Postgresql, we have discovered that some queries were indeed badly wroten 
I will tell to the developpers to try to optimize their queries for them to work efficiently on Postgresql

Thanks again for your help

Regards

Jean-Francois SURANTYN


-----Message d'origine-----
De : Richard Huxton [mailto:dev@archonet.com]
Envoyé : mercredi 6 février 2008 10:47
À : SURANTYN Jean François
Cc : pgsql-performance@postgresql.org
Objet : Re: [PERFORM] Optimizer : query rewrite and execution plan ?

SURANTYN Jean François wrote:
> my_db=# explain select * from test where n = 1;

> my_db=# explain select * 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
noticethat 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 *every
query* to see if clauses could be eliminated.

Is there any way to improve your query generator?

--
  Richard Huxton
  Archonet Ltd

**********************************************************************
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
the system manager.

Supermarchés MATCH, Société Par Actions Simplifiée au capital de 10 420 100 €, immatriculée au RCS de LILLE sous le
NuméroB 785 480 351 
Siège : 250, rue du Général de Gaulle - BP 201 - 59 561 LA MADELEINE Cedex
**********************************************************************