PostgreSQL performance enhancement when query planner fails to guess the right plan
Hello all, hope you are remembering me, some years ago I've designed the
PgAccess , the Tcl/Tk visual interface to PostgreSQL.
Thought you haven't received any news from me, I continued working with
PostgreSQL, being involved in very big projects in Romania.
Right now, the national identification of the cows, sheep, goats and
pigs in Romania runs on PostgreSQL on a very big database.
Once again , I had to thank you all for keeping up maintaining and
improving PostgreSQL.
My message to all of you is related to this big project (a government
sustained project) and some performance issues.
Very few words about the database: approx. 60 tables, 30 of them
containing 10 millions to 50 millions records , the whole database is
approx 40 Gb size !
In order to get a good performance, the database is operated on a dual
XEON with 6 Gb RAM IBM x235 server, the database with tables and indexes
carefully distributed on 6 different SCSI disks, in different
tablespaces in such a manner to allow parallelizing reads and HDD head
movements on different devices when joining those big tables.
We have tuned every possible parameter in config file, we have
reorganized queries, analyzing explains in order to get the best results
for all big queries and we succeeded most of the time.
But we have encountered some problems. Due to constant updates and
inserts into the database, it's size is growing continuously.
Of course we are doing DAILY the needed maintaince, vacuums, analyzes
and backups.
Due to permanent changes in database size and statistics there are
queries that sometimes change their execution plan, badly choosing
another plan and executing those queries in 2,3 minutes instead of 10
seconds, the usual execution time since the query plan is "switched". We
have done any effort in changing subselects and the query sentence in
order to "force" using some indexes, continuously watching the explain
results.
We have faced yesterday with such a problem with a query that "switched"
the query plan to a very bad one, almost putting the whole system down.
The only way that we have succeeded to make it work again was by using
the "SET ENABLE_MERGE_JOIN to OFF".
For the moment it works but in our opinion this is NOT the best approach
to guide the planner to a better query-plan variant.
Our suggestion would be : extending the EXPLAIN and SELECT commands like
that:
EXPLAIN VARIANTS SELECT ...... (and so on) that will display the
different query plans analyzed by the planner and their "estimated time
values" , not just the "best guess" .
assuming that the EXPLAIN VARIANTS will show 3 or 4 different query
plans, the database manager will be able to experiment, to test, and to
decide by himself what is "THE BEST PLAN FOR ME", instead of letting
postgresql planner to to that. Doing this, we would be able to clearly
specify then in the SELECT statement the "version" of the query-plan
that would be used in execution like in the following example:
SELECT .... (very big and complex query) ... USING PLAN 3;
Specifying the desired plan could be of course, different.
I realise that it would be probably better that the query-plan will
guess the right and optimal plan. I agree that this can be done be
tweaking parameters and costs BUT THIS CAN TAKE A LOT OF TIME, much more
than a couple of tests on the real database. An experimented database
admin can detect much easier the appropriate plan and "force" the
executor to select that one that he desires.
In our opinion, this would be the simplest and the most non-intrusive
method of "manual choosing" another query plan rather than indirectly
setting ON or OFFS various parameters that could affect badly other
queries.
First of all, it's assumed that the query planner HAS ALREADY evaluated
different variants and it decides to use one based upon the statistics
informations of the involved tables and "costs" for various types of
access.
Unfortunately, due to a very difficult adjustment of those costs and
timings of the HDD performance, IO transfer speeds, PostgreSQL is
choosing sometimes a wrong plan.
If we would have the power of choosing and experimenting different plans
with "SELECT .... USING PLAN <that-one>" we can select than the right
one in our real world.
The "... USING PLAN 9" extension to the language I hope that it's the
most delicate and innocent :-) that I hope that it can be accepted and
it will give a extremely powerful way of controlling the execution
performance.
Hope that you know the old joke with someone who is receiving an email
message : "Hi , I'm the Albanian virus. Due to our poor technologies, I
cannot do much so please delete some of your files and pretend to be
scared" :-)
I admit that this approach might be called "the Albanian way of choosing
the best query plan" :-) but you must admit also that it does not place
a big burden on the developers, it does not change anything in what have
been done since now and it allows the developers and database
administrators to dive into the query plan ocean and to get out of there
the best of the quickest of the fastest query plan. :-)
Hope that this long message have not disturb you so much ... so I'm
waiting for your comments and suggestions.
Best regards,
Constantin Teodorescu
Braila, ROMANIA
Constantin Teodorescu wrote:
EXPLAIN VARIANTS SELECT ...... (and so on) that will display the
different query plans analyzed by the planner and their "estimated time
values" , not just the "best guess" .assuming that the EXPLAIN VARIANTS will show 3 or 4 different query
plans, the database manager will be able to experiment, to test, and to
decide by himself what is "THE BEST PLAN FOR ME", instead of letting
postgresql planner to to that. Doing this, we would be able to clearly
specify then in the SELECT statement the "version" of the query-plan
that would be used in execution like in the following example:SELECT .... (very big and complex query) ... USING PLAN 3;
I think something like what Sybase has would be better. A friend told me
that in Sybase you can dump a query plan into a meta structure you can
handtweak (leaving out non relevant aspects) and force the usage of that
handtweaked query plan, where the missing pieces are filled in at run
time. It might still be helpful to get a dump of alternative
query plans via EXPLAIN to get
started.
I guess generally the idea must be to improve the planner to not even
need things like this, but it seems like a very useful fallback for the
time between finding a planner limitation and getting the improvement
into production.
An alternative approach that is already possible today is to handtweak
the table stats in order to generate a stable query plan. But this seems
like a very indirect way to get something you explicitly know you want.
regards,
Lukas
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.
Cheers,
Csaba.
Show quoted text
On Mon, 2006-08-07 at 22:02, Constantin Teodorescu wrote:
Hello all, hope you are remembering me, some years ago I've designed the
PgAccess , the Tcl/Tk visual interface to PostgreSQL.Thought you haven't received any news from me, I continued working with
PostgreSQL, being involved in very big projects in Romania.
Right now, the national identification of the cows, sheep, goats and
pigs in Romania runs on PostgreSQL on a very big database.
Once again , I had to thank you all for keeping up maintaining and
improving PostgreSQL.My message to all of you is related to this big project (a government
sustained project) and some performance issues.Very few words about the database: approx. 60 tables, 30 of them
containing 10 millions to 50 millions records , the whole database is
approx 40 Gb size !In order to get a good performance, the database is operated on a dual
XEON with 6 Gb RAM IBM x235 server, the database with tables and indexes
carefully distributed on 6 different SCSI disks, in different
tablespaces in such a manner to allow parallelizing reads and HDD head
movements on different devices when joining those big tables.We have tuned every possible parameter in config file, we have
reorganized queries, analyzing explains in order to get the best results
for all big queries and we succeeded most of the time.
But we have encountered some problems. Due to constant updates and
inserts into the database, it's size is growing continuously.
Of course we are doing DAILY the needed maintaince, vacuums, analyzes
and backups.
Due to permanent changes in database size and statistics there are
queries that sometimes change their execution plan, badly choosing
another plan and executing those queries in 2,3 minutes instead of 10
seconds, the usual execution time since the query plan is "switched". We
have done any effort in changing subselects and the query sentence in
order to "force" using some indexes, continuously watching the explain
results.We have faced yesterday with such a problem with a query that "switched"
the query plan to a very bad one, almost putting the whole system down.
The only way that we have succeeded to make it work again was by using
the "SET ENABLE_MERGE_JOIN to OFF".
For the moment it works but in our opinion this is NOT the best approach
to guide the planner to a better query-plan variant.Our suggestion would be : extending the EXPLAIN and SELECT commands like
that:EXPLAIN VARIANTS SELECT ...... (and so on) that will display the
different query plans analyzed by the planner and their "estimated time
values" , not just the "best guess" .assuming that the EXPLAIN VARIANTS will show 3 or 4 different query
plans, the database manager will be able to experiment, to test, and to
decide by himself what is "THE BEST PLAN FOR ME", instead of letting
postgresql planner to to that. Doing this, we would be able to clearly
specify then in the SELECT statement the "version" of the query-plan
that would be used in execution like in the following example:SELECT .... (very big and complex query) ... USING PLAN 3;
Specifying the desired plan could be of course, different.
I realise that it would be probably better that the query-plan will
guess the right and optimal plan. I agree that this can be done be
tweaking parameters and costs BUT THIS CAN TAKE A LOT OF TIME, much more
than a couple of tests on the real database. An experimented database
admin can detect much easier the appropriate plan and "force" the
executor to select that one that he desires.In our opinion, this would be the simplest and the most non-intrusive
method of "manual choosing" another query plan rather than indirectly
setting ON or OFFS various parameters that could affect badly other
queries.
First of all, it's assumed that the query planner HAS ALREADY evaluated
different variants and it decides to use one based upon the statistics
informations of the involved tables and "costs" for various types of
access.
Unfortunately, due to a very difficult adjustment of those costs and
timings of the HDD performance, IO transfer speeds, PostgreSQL is
choosing sometimes a wrong plan.
If we would have the power of choosing and experimenting different plans
with "SELECT .... USING PLAN <that-one>" we can select than the right
one in our real world.The "... USING PLAN 9" extension to the language I hope that it's the
most delicate and innocent :-) that I hope that it can be accepted and
it will give a extremely powerful way of controlling the execution
performance.Hope that you know the old joke with someone who is receiving an email
message : "Hi , I'm the Albanian virus. Due to our poor technologies, I
cannot do much so please delete some of your files and pretend to be
scared" :-)
I admit that this approach might be called "the Albanian way of choosing
the best query plan" :-) but you must admit also that it does not place
a big burden on the developers, it does not change anything in what have
been done since now and it allows the developers and database
administrators to dive into the query plan ocean and to get out of there
the best of the quickest of the fastest query plan. :-)Hope that this long message have not disturb you so much ... so I'm
waiting for your comments and suggestions.Best regards,
Constantin Teodorescu
Braila, ROMANIA---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
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
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.
On Mon, Aug 07, 2006 at 10:47:39PM +0200, Lukas Smith wrote:
Constantin Teodorescu wrote:
EXPLAIN VARIANTS SELECT ...... (and so on) that will display the
different query plans analyzed by the planner and their "estimated time
values" , not just the "best guess" .assuming that the EXPLAIN VARIANTS will show 3 or 4 different query
plans, the database manager will be able to experiment, to test, and to
decide by himself what is "THE BEST PLAN FOR ME", instead of letting
postgresql planner to to that. Doing this, we would be able to clearly
specify then in the SELECT statement the "version" of the query-plan
that would be used in execution like in the following example:SELECT .... (very big and complex query) ... USING PLAN 3;
I think something like what Sybase has would be better. A friend told me
that in Sybase you can dump a query plan into a meta structure you can
handtweak (leaving out non relevant aspects) and force the usage of that
handtweaked query plan, where the missing pieces are filled in at run
time. It might still be helpful to get a dump of alternative
query plans via EXPLAIN to get
started.
Another option would be adding planner hints.
I guess generally the idea must be to improve the planner to not even
need things like this, but it seems like a very useful fallback for the
time between finding a planner limitation and getting the improvement
into production.
Yeah, this comes up at least twice a year and every time the answer is
"We won't add planner hints because we should just fix the planner".
Fact is, all of the 'big 3' have planner hints, even though they've
invested litterally millions (if not billions) of dollars in their
planners. If they can't get it right I think it's hubris for us to think
we'll magically be able to, especially when there are known
deficiencies in our statistics system.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Csaba Nagy wrote:
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.
Well it should usually be possible to find a query that gives a stable
query plan. However in some cases stable query plan means varying
performance which is also not ideal. So you have to actually find a
query that will give you stable performance (which often means finding a
query that is a good compromise and that producses a stable plan).
But if you have changing data, very different selectivity for values etc
this can become very hard, maybe even impossible. For these kinds of
queries it might just be easier to put in the effort to specify (parts
of) the query plan explicitly. Especially as an interim solution until a
new stable release comes around that fixes the underlying planner issue
(which will usually be atleast 6-12 months).
regards,
Lukas