Re: PostgreSQL performance enhancement when query planner fails to

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
EXPLAINto 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