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
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
