Re: Optimizer : query rewrite and execution plan ?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

> my+AF8-db=+ACM- explain select +ACo- 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 +ACo-every
query+ACo- to see if clauses could be eliminated.

Is there any way to improve your query generator?

-- 
  Richard Huxton
  Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux