Re: generalizing the planner knobs - Mailing list pgsql-hackers

From Simon Riggs
Subject Re: generalizing the planner knobs
Date
Msg-id 1133772295.2906.873.camel@localhost.localdomain
Whole thread Raw
In response to Re: generalizing the planner knobs  (Greg Stark <gsstark@mit.edu>)
List pgsql-hackers
On Mon, 2005-12-05 at 01:53 -0500, Greg Stark wrote:
> Tom Lane <tgl@sss.pgh.pa.us> writes:

> > There is no such thing as a plan
> > that is good for every case --- outlying data values can make a
> > usually-good plan blow out your performance guarantee anyway. 
> 
> But outlying data is something the user has control over. 

Unfortunately, the DBA cannot choose the data distribution in his
database. So the appearance of control is somewhat illusory.

> The user when
> approving plans needs to be aware not just that the plan is experimentally
> good, but that it will perform reliably within the constraints based on his
> knowledge of the application and the data.

Greg's idea to have a plan comparator is a good one, for most
situations.

What you'll see if you run it though is no matter what you do, there
will be a few queries that are resistant to tuning. Their stored plans
will flip from SeqScan to IndexScan and back depending upon the
parameters used; neither will be suitable all the time and either
setting will cause very variable response times.

For those queries only, I seek a solution.

["Priming" the cache by executing IndexScan causing queries does not
work for all cases, so again the appearance of control is illusory.]

My solution is to replan the queries each time, rather than just once on
first parameter bind. By some mechanism; the GUC is just one of those.

Best Regards, Simon Riggs



pgsql-hackers by date:

Previous
From: Simon Riggs
Date:
Subject: Re: generalizing the planner knobs
Next
From: Hans-Juergen Schoenig
Date:
Subject: Re: generalizing the planner knobs