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