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

Re: Automatic function replanning

From: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
To: Rick Gigger <rick(at)alpinenetworking(dot)com>
Cc: "Jim C (dot) Nasby" <jnasby(at)pervasive(dot)com>, Neil Conway <neilc(at)samurai(dot)com>, Joachim Wieland <joe(at)mcknight(dot)de>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: Automatic function replanning
Date: 2005-12-21 22:43:38
Message-ID: 200512212243.jBLMhcA14548@candle.pha.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackers
Rick Gigger wrote:
> It seems to me like there are two classes of problems here:
> 
> 1) Simply invalidating plans made with out of date statistics.
> 2) Using run-time collected data to update the plan to something more  
> intelligent.
> 
> It also seems like #1 would be fairly straightforward and simple  
> whereas #2 would be much more complex.  #1 would do me a world of  
> good and probably other people as well.  Postgres's query planning  
> has always been fine for me, or at least I have always been able to  
> optimize my queries when I've got a representative data set to work  
> with.  Query plan caching only gets me when the query plan is created  
> before the statistics are present to create a good plan.
> 
> Just one users 2 cents.

Agreed.  I just can't add #2 unless we get more agreement from the
group, because it has been a disputed issue in the past.

---------------------------------------------------------------------------


> 
> - Rick Gigger
> 
> 
> On Dec 19, 2005, at 12:00 PM, Jim C. Nasby wrote:
> 
> > On Sat, Dec 17, 2005 at 01:07:10PM -0500, Bruce Momjian wrote:
> >> Jim C. Nasby wrote:
> >>> Is cardinality the only thing we'd need to worry about? My idea was
> >>> actually to track the amount of work normally required by a  
> >>> stored query
> >>> plan, and if a query uses that plan but requires a very different  
> >>> amount
> >>> of work it's a good indication that we either need to replan or  
> >>> store
> >>> multiple plans for that query. Though if we're certain that  
> >>> cardinality
> >>> is the only thing that could make a cached plan go bad it would
> >>> certainly simplify things greatly.
> >>
> >> This gets into another area of re-optimizing when the executor finds
> >> that the actual tables don't match the optimizer estimates.  I  
> >> think we
> >> decided that was too hard/risky, but invalidating the plan might  
> >> help,
> >> though I am thinking re-planning might just generate the same plan as
> >> before.  I think something would need to have happened since the last
> >> planning, like ANALYZE or something.
> >
> > Well, in the stored plan case, presumably what's changed is one of the
> > bound parameters. And if we want to be sophisticated about it, we  
> > won't
> > just throw out the old plan; rather we'll try and figure out what
> > parameter it is that's wanting a different plan.
> >
> >> Updated TODO:
> >>
> >> 	* Flush cached query plans when the dependent objects change,
> >> 	  when the cardinality of parameters changes dramatically, or
> >> 	  when new ANALYZE statistics are available
> >
> > Probably worth pointing to this therad in the TODO...
> > -- 
> > 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
> >
> > ---------------------------(end of  
> > broadcast)---------------------------
> > TIP 9: In versions below 8.0, the planner will ignore your desire to
> >        choose an index scan if your joining column's datatypes do not
> >        match
> >
> 
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman(at)candle(dot)pha(dot)pa(dot)us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

In response to

Responses

pgsql-hackers by date

Next:From: Hannu KrosingDate: 2005-12-21 23:12:19
Subject: Re: status of concurrent VACUUM patch ...
Previous:From: Greg StarkDate: 2005-12-21 21:59:03
Subject: Re: problem with nasty latin2 sorting

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