Skip site navigation (1) Skip section navigation (2)

Re: generalizing the planner knobs

From: Martijn van Oosterhout <kleptog(at)svana(dot)org>
To: Csaba Nagy <nagy(at)ecircle-ag(dot)com>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Greg Stark <gsstark(at)mit(dot)edu>,"Jonah H(dot) Harris" <jonah(dot)harris(at)gmail(dot)com>,Neil Conway <neilc(at)samurai(dot)com>,pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: generalizing the planner knobs
Date: 2005-12-02 10:53:25
Message-ID: 20051202105325.GB3057@svana.org (view raw or flat)
Thread:
Lists: pgsql-hackers
On Fri, Dec 02, 2005 at 11:07:06AM +0100, Csaba Nagy wrote:
> So for me the "hint" mechanism is good for telling the server that I'm
> not interested at all in the BEST plan but which risks getting very bad
> on occasions, but in a good enough plan which is safe.

I'm wondering if long term another approach might be to have another
parameter in the planner, cost_error or selectivity_error which is an
indication of how accurate we think it is.

So for example you have an index scan might cost x but with a possible
error of 15% and the seqscan might cost y but with an error of 1%.

The "error" for nested loop would be the product of the two inputs,
whereas a merge join whould be much less sensetive to error. A sort or
hash join would react badly to large variations of input.

So in cases where there is a choice between two indexscans with one
slightly more expensive and more accurate but can result in a mergejoin
would be a better choice than a possibly highly selective index but
without accurate info that needs to be fed into a nested loop. Even
though the latter might look better, the former is the "safer" option.

I think this would solve the problem where people see sudden flip-flops
between good and bad plans. The downside is that it's yet another
parameter for the planner to get wrong.

Unfortunatly, this is the kind of thing people write thesises on and I
don't think many people have the grounding in statistics to make it all
work.

Have a ncie day,
-- 
Martijn van Oosterhout   <kleptog(at)svana(dot)org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

In response to

Responses

pgsql-hackers by date

Next:From: Martijn van OosterhoutDate: 2005-12-02 11:06:01
Subject: Re: Graphics in postgress using GTK
Previous:From: Anuj TripathiDate: 2005-12-02 10:45:55
Subject: Graphics in postgress using GTK

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group