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 optimized by the past (example: where n=1 and n=1), Oracle was able to rewrite them and to "hide" the bad queries". But now that 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@xxxxxxxxxxxx] Envoy+AOk : mercredi 6 f+AOk-vrier 2008 10:47 +AMA : SURANTYN Jean Fran+AOc-ois Cc : pgsql-performance@xxxxxxxxxxxxxx Objet : Re: Optimizer : query rewrite and execution plan ? SURANTYN Jean Fran+AOc-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 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 *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+AOk-s MATCH, Soci+AOk-t+AOk Par Actions Simplifi+AOk-e au capital de 10 420 100 +IKw, immatricul+AOk-e au RCS de LILLE sous le Num+AOk-ro B 785 480 351 Si+AOg-ge : 250, rue du G+AOk-n+AOk-ral de Gaulle - BP 201 - 59 561 LA MADELEINE Cedex ********************************************************************** ---------------------------(end of broadcast)--------------------------- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your message can get through to the mailing list cleanly
