Re: PostgreSQL performance enhancement when query planner fails to

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(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461