Re: Automatic function replanning
От | Rick Gigger |
---|---|
Тема | Re: Automatic function replanning |
Дата | |
Msg-id | 2CB9D673-FA70-4571-A1EE-F075CF72A384@alpinenetworking.com обсуждение исходный текст |
Ответ на | Re: Automatic function replanning ("Jim C. Nasby" <jnasby@pervasive.com>) |
Ответы |
Re: Automatic function replanning
(Bruce Momjian <pgman@candle.pha.pa.us>)
|
Список | pgsql-hackers |
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. - 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@pervasive.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 >
В списке pgsql-hackers по дате отправления: