Re: PostgreSQL performance enhancement when query planner

Csaba Nagy wrote:
> Constantin,
>
> What binding are you using ? We here use Java+JDBC, and we were able to
> get stable query plans by forcing server side prepared statements (using
> PGStatement#setPrepareThreshold with 1 as the threshold), where the
> query is prepared without knowing the parameter values. This can
> backfire too, but for our purposes it was the right thing (probably
> sacrificing some performance, but getting a stable system). The plans in
> this case are made to work with guessed mean values for the estimates,
> and that's usually resulting in a stable plan, so once you got it right
> it will stay like that.
>
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.

The situation is simpler than it's at the first view.

Guessing what is the best plan, based on statistics and costs, IS NOT A
EASY THING TO DO.
Tweaking costs and statistics CAN TAKE A VERY LONG TIME and need strong
knowledge about database architecture, hardware performances and many
other things.
Not every average user of PostgreSQL can do that!

Experimenting the first 3 or 4 query plans in the descending order of
their estimated cost, IS SIMPLER and it can take less than an hour and
can be done by less experimented people.
Choosing the "proved" better query plan IS SIMPLER and that means
"PERFORMANCE EVEN FOR THE AVERAGE USER".

We are talking about open-source, free-source and the freedom of choice,
isn't it? So, why not give the user the freedom of choosing a different
query plan that will give a better performances.

Maybe I'm not interested in developing WHY the query planner is choosing
wrong. Of course , the developers will enhance it but until then, let's
give the user the power of manually selecting the right query plan.
The final result may be something like that : "I heard that PostgreSQL
has a very handy tool that gives you a better performance in queries. It
gives you the ability to make fine adjustments".
Sound good, isn't it ?
:-)

Teo