Skip site navigation (1) Skip section navigation (2)

Re: Optimizer : query rewrite and execution plan ?

From: Richard Huxton <dev(at)archonet(dot)com>
To: SURANTYN Jean François <jfsurant(at)supermarchesmatch(dot)fr>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Optimizer : query rewrite and execution plan ?
Date: 2008-02-06 09:47:22
Message-ID: 47A9822A.1000800@archonet.com (view raw or flat)
Thread:
Lists: pgsql-performance
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 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=(105)"
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

In response to

pgsql-performance by date

Next:From: SURANTYN Jean FrançoisDate: 2008-02-06 10:02:45
Subject: Re: Optimizer : query rewrite and execution plan ?
Previous:From: SURANTYN Jean FrançoisDate: 2008-02-06 08:42:18
Subject: Optimizer : query rewrite and execution plan ?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group