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