Re: PostgreSQL performance enhancement when query

On Tue, 2006-08-08 at 12:36, Constantin Teodorescu wrote:
> We have tried PGStatement#setPrepareThreshold with 1 as the threshold
> but it's not a good solution.
> Actually is worst. Considering that you have 5 different query plans,
> you are selecting approx. random one of them, not taking into account
> the statistics.

Wrong, you'll select _the same_ plan, that's what matters. If it's not
the plan you wanted, you have to rewrite the query, and try again, but
once you got the plan you wanted, it's pretty much you'll get always the
same plan. So you only need to test as long as you get the right query
to trigger the right plan... but of course this requires that your
queries are so constructed to always be OK with that plan, regardless
the parameter values. Usually this means a suboptimal plan, but stable
execution times.

If you need to give hints to the DB based on the parameter values and
choose different plans for different parameter values, then you
basically do the job of the planner in your application, and I guess
sooner or later you'll make wrong choices too.

Some hinting mechanism would be good for cases where the developer
really know better how the data is laid out (e.g. forcing the use of a
specific access method for one table in a complex join), but that
forcing a complete plan is probably not good. Even the hinting is only a
workaround for the planner fixes which will cannot make it to the stable
version...

On the daydreaming part, how about a 2 phase planner ?

Modus operandi:

Phase 1: compile and cache plan decision tree:
- collect all reasonable plans without taking into account the
parameter values;
- check the parameter bounds where each plan is the fastest;
- compile a decision tree which based on the parameter values chooses
one plan or the other;
- cache this plan decision tree;
- there's no need to cache plans which will always loose to some other
plan no matter what parameter values you give (to limit the size of the
decision tree);

Phase 2: run the decision tree to chose the best cached plan for the
parameter values;

You could use variables coming from the statistics system in the
decision tree so it doesn't have to be recalculated too often on
statistics changes.

With a system like this, you could at system startup make the decision
tree for all your frequently used queries and have fast planning at
runtime which is optimized for the parameter values (takes the decision
tree from the cache, runs it with the current parameters). Or just store
the whole thing in a system table... or tweak the decision tree
manually...

This is actually not addressing the plan stability issue, but if manual
tweaking would be allowed, it would...

Cheers,
Csaba.