Re: 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 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