Обсуждение: Automatic function replanning
Hi, there's a topic that comes up from time to time on the lists, the problem that pgsql functions get planned only once and thereafter the same query plan is used until server shutdown or explicit recreation of the function. I'd like to implement a way of automatic function replanning. I can think of two possible approaches. 1. in a more general way: extend pg_proc by an interval column "ttl" or "replanAfter" and add a function declaration attributeto the parser "... STRICT STABLE REPLAN AFTER '3 days'::interval" + general approach, every language that can pre-compute plans can use this feature, the check can be done in one placefor all languages - in fact only plpsql can do that at the moment (right?) and there is no other candidate for something similar at themoment - catalog change that also requires interval to be specially treated while bootstrapping - catalog would grow, every function would have the attribute though it is only applicable for a very low number of functions,let alone the number of functions that would actually use it in a typical installation 2. use the #option feature of plpgsql. Add the possibility to specify #option ttl '3 days' or #option replan-after '1day 2 hours' + Minor changes, changes only local to plpgsql - plpgsql specific solution - is #option documented at all? Should it stay "unofficial"? If so, why? 3. (not automatic) add a statement that makes pgsql forget the plan and compute a new one when the function gets calledagain. "The user should rather use cron for doing maintenance tasks." What do you think? Any other ideas? Joachim
On Tue, 2005-12-13 at 22:32 +0100, Joachim Wieland wrote: > there's a topic that comes up from time to time on the lists, the problem > that pgsql functions get planned only once and thereafter the same query > plan is used until server shutdown or explicit recreation of the function. The problem really has nothing to do with functions, per se: whenever a plan is created and then stored for future use, the assumptions made by that plan may be invalidated by the time the plan is executed. This applies to PREPARE, pl/pgsql functions, perhaps the plan caching done by the RI triggers, and so forth. I also think that invalidating cached plans on a periodic basis is the wrong approach -- we can use sinval to invalidate plans as soon as a dependent database object changes and not before. This thread contains some ideas on how to do this: http://archives.postgresql.org/pgsql-hackers/2005-03/msg00426.php I got somewhat sidetracked by the complexities of the "central plan caching module" that Tom would like to see, but I'm still hoping to take a look at this for 8.2. -Neil
On Tue, Dec 13, 2005 at 04:49:10PM -0500, Neil Conway wrote: > On Tue, 2005-12-13 at 22:32 +0100, Joachim Wieland wrote: > > there's a topic that comes up from time to time on the lists, the problem > > that pgsql functions get planned only once and thereafter the same query > > plan is used until server shutdown or explicit recreation of the function. > > The problem really has nothing to do with functions, per se: whenever a > plan is created and then stored for future use, the assumptions made by > that plan may be invalidated by the time the plan is executed. This > applies to PREPARE, pl/pgsql functions, perhaps the plan caching done by > the RI triggers, and so forth. > > I also think that invalidating cached plans on a periodic basis is the > wrong approach -- we can use sinval to invalidate plans as soon as a > dependent database object changes and not before. This thread contains > some ideas on how to do this: > > http://archives.postgresql.org/pgsql-hackers/2005-03/msg00426.php > > I got somewhat sidetracked by the complexities of the "central plan > caching module" that Tom would like to see, but I'm still hoping to take > a look at this for 8.2. As for predicate-driven plan changes (ie: query is planned the first time with a predicate that has high cardinality, but there are also low cardinality values that will be queried on), it would make more sense to track the amount of work (probably tuples fetched) normally required to execute a prepared statement. Any time that prepared statement is executed with a set of predicates that substantially changes the amount of work required it should be remembered and considered for re-planning the next time the query is executed with those predicates. -- 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
Good idea, TODO updated: * Flush cached query plans when the dependent objects change or when the cardinality of parameters changes dramatically --------------------------------------------------------------------------- Jim C. Nasby wrote: > On Tue, Dec 13, 2005 at 04:49:10PM -0500, Neil Conway wrote: > > On Tue, 2005-12-13 at 22:32 +0100, Joachim Wieland wrote: > > > there's a topic that comes up from time to time on the lists, the problem > > > that pgsql functions get planned only once and thereafter the same query > > > plan is used until server shutdown or explicit recreation of the function. > > > > The problem really has nothing to do with functions, per se: whenever a > > plan is created and then stored for future use, the assumptions made by > > that plan may be invalidated by the time the plan is executed. This > > applies to PREPARE, pl/pgsql functions, perhaps the plan caching done by > > the RI triggers, and so forth. > > > > I also think that invalidating cached plans on a periodic basis is the > > wrong approach -- we can use sinval to invalidate plans as soon as a > > dependent database object changes and not before. This thread contains > > some ideas on how to do this: > > > > http://archives.postgresql.org/pgsql-hackers/2005-03/msg00426.php > > > > I got somewhat sidetracked by the complexities of the "central plan > > caching module" that Tom would like to see, but I'm still hoping to take > > a look at this for 8.2. > > As for predicate-driven plan changes (ie: query is planned the first > time with a predicate that has high cardinality, but there are also low > cardinality values that will be queried on), it would make more sense to > track the amount of work (probably tuples fetched) normally required to > execute a prepared statement. Any time that prepared statement is > executed with a set of predicates that substantially changes the amount > of work required it should be remembered and considered for re-planning > the next time the query is executed with those predicates. > -- > 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 4: Have you searched our list archives? > > http://archives.postgresql.org > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
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. On Fri, Dec 16, 2005 at 11:10:43PM -0500, Bruce Momjian wrote: > > Good idea, TODO updated: > > * Flush cached query plans when the dependent objects change or > when the cardinality of parameters changes dramatically > > > --------------------------------------------------------------------------- > > Jim C. Nasby wrote: > > On Tue, Dec 13, 2005 at 04:49:10PM -0500, Neil Conway wrote: > > > On Tue, 2005-12-13 at 22:32 +0100, Joachim Wieland wrote: > > > > there's a topic that comes up from time to time on the lists, the problem > > > > that pgsql functions get planned only once and thereafter the same query > > > > plan is used until server shutdown or explicit recreation of the function. > > > > > > The problem really has nothing to do with functions, per se: whenever a > > > plan is created and then stored for future use, the assumptions made by > > > that plan may be invalidated by the time the plan is executed. This > > > applies to PREPARE, pl/pgsql functions, perhaps the plan caching done by > > > the RI triggers, and so forth. > > > > > > I also think that invalidating cached plans on a periodic basis is the > > > wrong approach -- we can use sinval to invalidate plans as soon as a > > > dependent database object changes and not before. This thread contains > > > some ideas on how to do this: > > > > > > http://archives.postgresql.org/pgsql-hackers/2005-03/msg00426.php > > > > > > I got somewhat sidetracked by the complexities of the "central plan > > > caching module" that Tom would like to see, but I'm still hoping to take > > > a look at this for 8.2. > > > > As for predicate-driven plan changes (ie: query is planned the first > > time with a predicate that has high cardinality, but there are also low > > cardinality values that will be queried on), it would make more sense to > > track the amount of work (probably tuples fetched) normally required to > > execute a prepared statement. Any time that prepared statement is > > executed with a set of predicates that substantially changes the amount > > of work required it should be remembered and considered for re-planning > > the next time the query is executed with those predicates. > > -- > > 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 4: Have you searched our list archives? > > > > http://archives.postgresql.org > > > > -- > Bruce Momjian | http://candle.pha.pa.us > pgman@candle.pha.pa.us | (610) 359-1001 > + If your life is a hard drive, | 13 Roberts Road > + Christ can be your backup. | Newtown Square, Pennsylvania 19073 > > ---------------------------(end of broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings > -- 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
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. 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 --------------------------------------------------------------------------- > > On Fri, Dec 16, 2005 at 11:10:43PM -0500, Bruce Momjian wrote: > > > > Good idea, TODO updated: > > > > * Flush cached query plans when the dependent objects change or > > when the cardinality of parameters changes dramatically > > > > > > --------------------------------------------------------------------------- > > > > Jim C. Nasby wrote: > > > On Tue, Dec 13, 2005 at 04:49:10PM -0500, Neil Conway wrote: > > > > On Tue, 2005-12-13 at 22:32 +0100, Joachim Wieland wrote: > > > > > there's a topic that comes up from time to time on the lists, the problem > > > > > that pgsql functions get planned only once and thereafter the same query > > > > > plan is used until server shutdown or explicit recreation of the function. > > > > > > > > The problem really has nothing to do with functions, per se: whenever a > > > > plan is created and then stored for future use, the assumptions made by > > > > that plan may be invalidated by the time the plan is executed. This > > > > applies to PREPARE, pl/pgsql functions, perhaps the plan caching done by > > > > the RI triggers, and so forth. > > > > > > > > I also think that invalidating cached plans on a periodic basis is the > > > > wrong approach -- we can use sinval to invalidate plans as soon as a > > > > dependent database object changes and not before. This thread contains > > > > some ideas on how to do this: > > > > > > > > http://archives.postgresql.org/pgsql-hackers/2005-03/msg00426.php > > > > > > > > I got somewhat sidetracked by the complexities of the "central plan > > > > caching module" that Tom would like to see, but I'm still hoping to take > > > > a look at this for 8.2. > > > > > > As for predicate-driven plan changes (ie: query is planned the first > > > time with a predicate that has high cardinality, but there are also low > > > cardinality values that will be queried on), it would make more sense to > > > track the amount of work (probably tuples fetched) normally required to > > > execute a prepared statement. Any time that prepared statement is > > > executed with a set of predicates that substantially changes the amount > > > of work required it should be remembered and considered for re-planning > > > the next time the query is executed with those predicates. > > > -- > > > 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 4: Have you searched our list archives? > > > > > > http://archives.postgresql.org > > > > > > > -- > > Bruce Momjian | http://candle.pha.pa.us > > pgman@candle.pha.pa.us | (610) 359-1001 > > + If your life is a hard drive, | 13 Roberts Road > > + Christ can be your backup. | Newtown Square, Pennsylvania 19073 > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 5: don't forget to increase your free space map settings > > > > -- > 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 > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Bruce Momjian wrote: > * Flush cached query plans when the dependent objects change, > when the cardinality of parameters changes dramatically, or > when new ANALYZE statistics are available Wouldn't it also make sense to flush a cached query plan when after execution it is determined that one or more assumptions that the cached query plan was based on was found to be off? Like the query plan was based on the assumption that a particular table would only return a hand full of rows, but in reality it returned a few thousand. regards, Lukas
Lukas Smith <mls@pooteeweet.org> writes:
> Bruce Momjian wrote:
>
>> * Flush cached query plans when the dependent objects change,
>> when the cardinality of parameters changes dramatically, or
>> when new ANALYZE statistics are available
>
> Wouldn't it also make sense to flush a cached query plan when after
> execution it is determined that one or more assumptions that the
> cached query plan was based on was found to be off? Like the query
> plan was based on the assumption that a particular table would only
> return a hand full of rows, but in reality it returned a few
> thousand.
There is some merit to that.
I could also see it being sensible to flush a cached plan any time the
query took more than some [arbitrary/GUC-chosen] interval.
Supposing it took 20s to execute the query, it would surely seem
surprising for re-evaluating the plan to be expected to make up a
material proportion of the cost of the *next* invocation.
If we flush every plan that took >10s to evaluate, that offers the
possibility for it to be done better next time...
--
output = reverse("gro.mca" "@" "enworbbc")
http://www.ntlug.org/~cbbrowne/internet.html
Points are awarded for getting the last word in. Drawing the
conversation out so long that the original message disappears due to
being indented off the right hand edge of the screen is one way to do
this. Another is to imply that anyone replying further is a hopeless
cretin and is wasting everyone's valuable time.
-- from the Symbolics Guidelines for Sending Mail
Chris Browne wrote: > Lukas Smith <mls@pooteeweet.org> writes: > > Bruce Momjian wrote: > > > >> * Flush cached query plans when the dependent objects change, > >> when the cardinality of parameters changes dramatically, or > >> when new ANALYZE statistics are available > > > > Wouldn't it also make sense to flush a cached query plan when after > > execution it is determined that one or more assumptions that the > > cached query plan was based on was found to be off? Like the query > > plan was based on the assumption that a particular table would only > > return a hand full of rows, but in reality it returned a few > > thousand. > > There is some merit to that. > > I could also see it being sensible to flush a cached plan any time the > query took more than some [arbitrary/GUC-chosen] interval. > > Supposing it took 20s to execute the query, it would surely seem > surprising for re-evaluating the plan to be expected to make up a > material proportion of the cost of the *next* invocation. > > If we flush every plan that took >10s to evaluate, that offers the > possibility for it to be done better next time... Ah, but how do you pass that information back to the optimizer so you don't end up getting the same plan again? -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Lukas Smith <mls@pooteeweet.org> writes:
> Bruce Momjian wrote:
>> * Flush cached query plans when the dependent objects change,
>> when the cardinality of parameters changes dramatically, or
>> when new ANALYZE statistics are available
> Wouldn't it also make sense to flush a cached query plan when after
> execution it is determined that one or more assumptions that the cached
> query plan was based on was found to be off?
Not unless you do something that would cause the planner to make
different choices next time. (Such as changing the ANALYZE statistics,
perhaps.) The TODO item is OK as stated, it's just talking about
mechanism and not the things that might trigger the mechanism.
regards, tom lane
> Chris Browne wrote: >> Lukas Smith <mls@pooteeweet.org> writes: >> > Bruce Momjian wrote: >> > >> >> * Flush cached query plans when the dependent objects change, >> >> when the cardinality of parameters changes dramatically, or >> >> when new ANALYZE statistics are available >> > >> > Wouldn't it also make sense to flush a cached query plan when after >> > execution it is determined that one or more assumptions that the >> > cached query plan was based on was found to be off? Like the query >> > plan was based on the assumption that a particular table would only >> > return a hand full of rows, but in reality it returned a few >> > thousand. >> >> There is some merit to that. >> >> I could also see it being sensible to flush a cached plan any time the >> query took more than some [arbitrary/GUC-chosen] interval. >> >> Supposing it took 20s to execute the query, it would surely seem >> surprising for re-evaluating the plan to be expected to make up a >> material proportion of the cost of the *next* invocation. >> >> If we flush every plan that took >10s to evaluate, that offers the >> possibility for it to be done better next time... > > Ah, but how do you pass that information back to the optimizer so you > don't end up getting the same plan again? We can't, in any direct fashion, of course. Even if flushing the plan doesn't lead to a better one, now, it still leaves you ready for when an ANALYZE will come along and change the stats and possibly improve things. One possibility to do something indirect would be for this "plan invalidation" to also feed some stats to pg_autovacuum, such that every "bad query plan" (as evaluated by the notion that the actual number of tuples for a given table wildly varied from expectations) would bump up the stats for the offending table. That would give some feedback to encourage pg_autovacuum to analyze trouble tables again sooner. -- (reverse (concatenate 'string "moc.liamg" "@" "enworbbc")) http://cbbrowne.com/info/slony.html ``Lisp has jokingly been called "the most intelligent way to misuse a computer". I think that description is a great compliment because it transmits the full flavor of liberation: it has assisted a number of our most gifted fellow humans in thinking previously impossible thoughts.'' -- "The Humble Programmer", E. Dijkstra, CACM, vol. 15, n. 10, 1972
> Lukas Smith <mls@pooteeweet.org> writes: >> Bruce Momjian wrote: >>> * Flush cached query plans when the dependent objects change, >>> when the cardinality of parameters changes dramatically, or >>> when new ANALYZE statistics are available > >> Wouldn't it also make sense to flush a cached query plan when after >> execution it is determined that one or more assumptions that the cached >> query plan was based on was found to be off? > > Not unless you do something that would cause the planner to make > different choices next time. (Such as changing the ANALYZE statistics, > perhaps.) The TODO item is OK as stated, it's just talking about > mechanism and not the things that might trigger the mechanism. A mechanism might be to bump up the stats stored for pg_autovacuum, which would encourage a table to be re-ANALYZEd. That may not immediately change ANALYZE statistics, but it's something... Even if there is NO feedback mechanism on statistics, if we know the plan was pretty bad, it is surely at least *a* feedback to invalidate the plan. -- (reverse (concatenate 'string "moc.liamg" "@" "enworbbc")) http://cbbrowne.com/info/slony.html ``Lisp has jokingly been called "the most intelligent way to misuse a computer". I think that description is a great compliment because it transmits the full flavor of liberation: it has assisted a number of our most gifted fellow humans in thinking previously impossible thoughts.'' -- "The Humble Programmer", E. Dijkstra, CACM, vol. 15, n. 10, 1972
Christopher Browne wrote: > > Not unless you do something that would cause the planner to make > > different choices next time. (Such as changing the ANALYZE statistics, > > perhaps.) The TODO item is OK as stated, it's just talking about > > mechanism and not the things that might trigger the mechanism. > > A mechanism might be to bump up the stats stored for pg_autovacuum, > which would encourage a table to be re-ANALYZEd. > > That may not immediately change ANALYZE statistics, but it's > something... > > Even if there is NO feedback mechanism on statistics, if we know the > plan was pretty bad, it is surely at least *a* feedback to invalidate > the plan. Something has to cause the replan to be better, and that is one possibility. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
On Saturday 2005-12-17 16:28, Lukas Smith wrote: > Bruce Momjian wrote: > > * Flush cached query plans when the dependent objects change, > > when the cardinality of parameters changes dramatically, or > > when new ANALYZE statistics are available > > Wouldn't it also make sense to flush a cached query plan when after > execution it is determined that one or more assumptions that the cached > query plan was based on was found to be off? Like the query plan was > based on the assumption that a particular table would only return a hand > full of rows, but in reality it returned a few thousand. > > regards, > Lukas > Proposed rewrite * Mark query plan for flush (opportunistic replan) when:** dependent objects change,** cardinality of parameters changessufficiently (per planner parameter)** when new ANALYZE statistics are available and per planner parameter differ sufficiently from prior statistics. * Mark plan as "tried" when parameters of returned set out of statistical control, create alternate plan hill-climbing to statical control.** Too many/too few rows relative to plan expectations *** Auto-sample for better statistics?** History of plan shows throughput time for result set varies excessively (need more execution stability, possibly at expense of median optimality). > ---------------------------(end of broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly
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
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 >
Trent Shipley wrote: > On Saturday 2005-12-17 16:28, Lukas Smith wrote: > > Bruce Momjian wrote: > > > * Flush cached query plans when the dependent objects change, > > > when the cardinality of parameters changes dramatically, or > > > when new ANALYZE statistics are available > > > > Wouldn't it also make sense to flush a cached query plan when after > > execution it is determined that one or more assumptions that the cached > > query plan was based on was found to be off? Like the query plan was > > based on the assumption that a particular table would only return a hand > > full of rows, but in reality it returned a few thousand. > > > > regards, > > Lukas > > > > Proposed rewrite > > * Mark query plan for flush (opportunistic replan) when: > ** dependent objects change, > ** cardinality of parameters changes sufficiently (per planner parameter) > ** when new ANALYZE statistics are available and per planner parameter differ > sufficiently from prior statistics. Frankly, I think any new ANALYZE statistics should just invalidate the plan. I don't think it is worth trying to determine if they changed sufficiently or not --- you might as we just replan. > * Mark plan as "tried" when parameters of returned set out of statistical > control, create alternate plan hill-climbing to statical control. > ** Too many/too few rows relative to plan expectations > *** Auto-sample for better statistics? > ** History of plan shows throughput time for result set varies excessively > (need more execution stability, possibly at expense of median optimality). This is a new idea, that you are remembering bad plans. I am unsure how we would track that information. It gets into the area of having the optimizer change its behavior based on previous runs, and I am not sure we have ever agreed to get into that kind of behavior. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
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@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 > > > > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
On Wed, Dec 21, 2005 at 05:43:38PM -0500, Bruce Momjian wrote: > 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. Well, how about this, since it's a prerequisit for #2 and would be generally useful anyway: Track normal resource consumption (ie: tuples read) for planned queries and record parameter values that result in drastically different resource consumption. This would at least make it easy for admins to identify prepared queries that have a highly variable execution cost. -- 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
Jim C. Nasby wrote: > On Wed, Dec 21, 2005 at 05:43:38PM -0500, Bruce Momjian wrote: > > 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. > > Well, how about this, since it's a prerequisit for #2 and would be > generally useful anyway: > > Track normal resource consumption (ie: tuples read) for planned queries > and record parameter values that result in drastically different > resource consumption. > > This would at least make it easy for admins to identify prepared queries > that have a highly variable execution cost. We have that TODO already: * Log statements where the optimizer row estimates were dramatically different from the number of rows actually found? -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Jim C. Nasby wrote: > On Wed, Dec 21, 2005 at 05:43:38PM -0500, Bruce Momjian wrote: > >>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. > > > Well, how about this, since it's a prerequisit for #2 and would be > generally useful anyway: > > Track normal resource consumption (ie: tuples read) for planned queries > and record parameter values that result in drastically different > resource consumption. > > This would at least make it easy for admins to identify prepared queries > that have a highly variable execution cost. Yeah, it seems such a log would be very helpful in its own right for DBA's and also as a feedback loop to find possibles issues in the query planner. And maybe one day this feedback loop can be even directly used by the server itself. regards, Lukas Smith
On Wed, Dec 21, 2005 at 11:00:31PM -0500, Bruce Momjian wrote: > > Track normal resource consumption (ie: tuples read) for planned queries > > and record parameter values that result in drastically different > > resource consumption. > > > > This would at least make it easy for admins to identify prepared queries > > that have a highly variable execution cost. > > We have that TODO already: > > * Log statements where the optimizer row estimates were dramatically > different from the number of rows actually found? Does the stored plan also save how many rows were expected? Otherwise I'm not sure how that TODO covers it... If it does then please ignore my ramblings below. :) My idea has nothing to do with row estimates. It has to do with the amount of work actually done to perform a query. Consider this example: CREATE TABLE queue (status char NOT NULL, queue_item text NOT NULL); CREATE INDEX queue__status ON queue (status); Obviously, to process this you'll need a query like: SELECT * FROM queue WHERE status='N' -- N for New; Say you also occasionally need to see a list of items that have been processed: SELECT * FROM queue WHERE status='D' -- D for Done; And let's say you need to keep done items around for 30 days. Now, if both of these are done using a prepared statement, it's going to look like: SELECT * FROM queue WHERE status='?'; If the first one to run is the queue processing one, the planner will probably choose the index. This means that when we're searching on 'N', there will be a fairly small number of tuples read to execute the query, but when searching for 'D' a very large number of tuples will be read. What I'm proposing is to keep track of the 'normal' number of tuples read when executing a prepared query, and logging any queries that are substantially different. So, if you normally have to read 50 tuples to find all 'N' records, when the query looking for 'D' records comes along and has to read 5000 tuples instead, we want to log that. Probably the easiest way to accomplish this is to store a moving average of tuples read with each prepared statement entry. -- 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
Oh, OK, so you are logging prepared queries where the plan generates a significantly different number of rows from previous runs. I am not sure why that is better, or easier, than just invalidating the cached plan if the cardinality changes. --------------------------------------------------------------------------- Jim C. Nasby wrote: > On Wed, Dec 21, 2005 at 11:00:31PM -0500, Bruce Momjian wrote: > > > Track normal resource consumption (ie: tuples read) for planned queries > > > and record parameter values that result in drastically different > > > resource consumption. > > > > > > This would at least make it easy for admins to identify prepared queries > > > that have a highly variable execution cost. > > > > We have that TODO already: > > > > * Log statements where the optimizer row estimates were dramatically > > different from the number of rows actually found? > > Does the stored plan also save how many rows were expected? Otherwise > I'm not sure how that TODO covers it... If it does then please ignore my > ramblings below. :) > > My idea has nothing to do with row estimates. It has to do with the > amount of work actually done to perform a query. Consider this example: > > CREATE TABLE queue (status char NOT NULL, queue_item text NOT NULL); > CREATE INDEX queue__status ON queue (status); > > Obviously, to process this you'll need a query like: > SELECT * FROM queue WHERE status='N' -- N for New; > > Say you also occasionally need to see a list of items that have been > processed: > SELECT * FROM queue WHERE status='D' -- D for Done; > > And let's say you need to keep done items around for 30 days. > > Now, if both of these are done using a prepared statement, it's going to > look like: > > SELECT * FROM queue WHERE status='?'; > > If the first one to run is the queue processing one, the planner will > probably choose the index. This means that when we're searching on 'N', > there will be a fairly small number of tuples read to execute the query, > but when searching for 'D' a very large number of tuples will be read. > > What I'm proposing is to keep track of the 'normal' number of tuples > read when executing a prepared query, and logging any queries that are > substantially different. So, if you normally have to read 50 tuples to > find all 'N' records, when the query looking for 'D' records comes along > and has to read 5000 tuples instead, we want to log that. Probably the > easiest way to accomplish this is to store a moving average of tuples > read with each prepared statement entry. > -- > 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 6: explain analyze is your friend > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Jim C. Nasby wrote: > Now, if both of these are done using a prepared statement, it's going to > look like: > > SELECT * FROM queue WHERE status='?'; > > If the first one to run is the queue processing one, the planner will > probably choose the index. This means that when we're searching on 'N', > there will be a fairly small number of tuples read to execute the query, > but when searching for 'D' a very large number of tuples will be read. I do not know how exactly how pg handles this internally, however while skimming the oracle tuning pocket guide I picked up for 2 euros I noticed that it mentioned that since oracle 9i bound parameter values are evaluated before the execution plan is determined. Maybe I am mixing up separate concepts (are bound variables and prepared statements different concepts?) here. I also do not really understand if that means that oracle does not store a query plan for a prepared query or if it just does some special handling in case it knows that a prepared statement column is known to have a highly varying selectivity per value. regards, Lukas
Lukas Smith wrote: > Jim C. Nasby wrote: > > > Now, if both of these are done using a prepared statement, it's going to > > look like: > > > > SELECT * FROM queue WHERE status='?'; > > > > If the first one to run is the queue processing one, the planner will > > probably choose the index. This means that when we're searching on 'N', > > there will be a fairly small number of tuples read to execute the query, > > but when searching for 'D' a very large number of tuples will be read. > > I do not know how exactly how pg handles this internally, however while > skimming the oracle tuning pocket guide I picked up for 2 euros I > noticed that it mentioned that since oracle 9i bound parameter values > are evaluated before the execution plan is determined. > > Maybe I am mixing up separate concepts (are bound variables and prepared > statements different concepts?) here. I also do not really understand if > that means that oracle does not store a query plan for a prepared query > or if it just does some special handling in case it knows that a > prepared statement column is known to have a highly varying selectivity > per value. What the Oralce manual means I think is that the plan of the query is delayed until the _first_ EXECUTE, so it has some values to use in the optimizer. The problem is that later queries might use constants of greatly different cardinality. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Bruce Momjian wrote: >> Maybe I am mixing up separate concepts (are bound variables and prepared >> statements different concepts?) here. I also do not really understand if >> that means that oracle does not store a query plan for a prepared query >> or if it just does some special handling in case it knows that a >> prepared statement column is known to have a highly varying selectivity >> per value. > > What the Oralce manual means I think is that the plan of the query is > delayed until the _first_ EXECUTE, so it has some values to use in the > optimizer. The problem is that later queries might use constants of > greatly different cardinality. ok .. which just goes to tell to not use prepared statements for a column with highly varying selectivity ..? or is there a realistic shot at fixing this use case? regards, Lukas
On Thu, Dec 22, 2005 at 09:55:14PM +0100, Lukas Smith wrote: > Bruce Momjian wrote: > > >>Maybe I am mixing up separate concepts (are bound variables and prepared > >>statements different concepts?) here. I also do not really understand if > >>that means that oracle does not store a query plan for a prepared query > >>or if it just does some special handling in case it knows that a > >>prepared statement column is known to have a highly varying selectivity > >>per value. > > > >What the Oralce manual means I think is that the plan of the query is > >delayed until the _first_ EXECUTE, so it has some values to use in the > >optimizer. The problem is that later queries might use constants of > >greatly different cardinality. > > ok .. which just goes to tell to not use prepared statements for a > column with highly varying selectivity ..? > > or is there a realistic shot at fixing this use case? FWIW, I believe that 10g has some brains in this regard, where it can detect if it should store multiple plans for one prepared statement. This is critical for them, because they'r parser/planner is much harder on the system than ours is. -- 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
Lukas Smith wrote: > Bruce Momjian wrote: > > >> Maybe I am mixing up separate concepts (are bound variables and prepared > >> statements different concepts?) here. I also do not really understand if > >> that means that oracle does not store a query plan for a prepared query > >> or if it just does some special handling in case it knows that a > >> prepared statement column is known to have a highly varying selectivity > >> per value. > > > > What the Oralce manual means I think is that the plan of the query is > > delayed until the _first_ EXECUTE, so it has some values to use in the > > optimizer. The problem is that later queries might use constants of > > greatly different cardinality. > > ok .. which just goes to tell to not use prepared statements for a > column with highly varying selectivity ..? > > or is there a realistic shot at fixing this use case? It is an issue for all databases. We gave a TODO about it: * Flush cached query plans when the dependent objects change, when the cardinality of parameters changes dramatically, or when new ANALYZE statistics are available -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Bruce Momjian wrote: > It is an issue for all databases. We gave a TODO about it: > > * Flush cached query plans when the dependent objects change, > when the cardinality of parameters changes dramatically, or > when new ANALYZE statistics are available Ok, just so I understand this correctly. In the mentioned case the cardinality does not really change in regards to the table stats, its just thatI happen to use a value that has a different selectivity and therefore I may need a different plan. So I do not really see how this use case is handled with the above todo, nor do I really see how its handled with what Jim suggested earlier. The fact of the matter is that for this use case you need to use different query plans for the same prepared statements. regards, Lukas
Well, not just rows; total tuples, both base heap and index. ISTM that would be a better metric than just plain rows read out of base or rows returned. Depending on how far down this road we want to go, this would allow for detecting what parameter values require different query plans, and then using different query plans for different sets of values. Simply invalidating the cached plan means you could potentially end up needing to re-plan very frequently. But given the current speed of our optimizer, it's probably not worth going to this extent. Another concern I have is: is cardinality the only metric we need to look at when deciding to re-plan or are there others? In either case, my guess is that tracking the info needed to make this idea happen is probably much easier than doing automatic plan invalidation based on cardinality, so it would be a useful interum step. But if we could actually get cardinality invalidation into 8.2, I'd say put the effort into that... On Thu, Dec 22, 2005 at 03:14:09PM -0500, Bruce Momjian wrote: > > Oh, OK, so you are logging prepared queries where the plan generates a > significantly different number of rows from previous runs. I am not > sure why that is better, or easier, than just invalidating the cached > plan if the cardinality changes. > > --------------------------------------------------------------------------- > > Jim C. Nasby wrote: > > On Wed, Dec 21, 2005 at 11:00:31PM -0500, Bruce Momjian wrote: > > > > Track normal resource consumption (ie: tuples read) for planned queries > > > > and record parameter values that result in drastically different > > > > resource consumption. > > > > > > > > This would at least make it easy for admins to identify prepared queries > > > > that have a highly variable execution cost. > > > > > > We have that TODO already: > > > > > > * Log statements where the optimizer row estimates were dramatically > > > different from the number of rows actually found? > > > > Does the stored plan also save how many rows were expected? Otherwise > > I'm not sure how that TODO covers it... If it does then please ignore my > > ramblings below. :) > > > > My idea has nothing to do with row estimates. It has to do with the > > amount of work actually done to perform a query. Consider this example: > > > > CREATE TABLE queue (status char NOT NULL, queue_item text NOT NULL); > > CREATE INDEX queue__status ON queue (status); > > > > Obviously, to process this you'll need a query like: > > SELECT * FROM queue WHERE status='N' -- N for New; > > > > Say you also occasionally need to see a list of items that have been > > processed: > > SELECT * FROM queue WHERE status='D' -- D for Done; > > > > And let's say you need to keep done items around for 30 days. > > > > Now, if both of these are done using a prepared statement, it's going to > > look like: > > > > SELECT * FROM queue WHERE status='?'; > > > > If the first one to run is the queue processing one, the planner will > > probably choose the index. This means that when we're searching on 'N', > > there will be a fairly small number of tuples read to execute the query, > > but when searching for 'D' a very large number of tuples will be read. > > > > What I'm proposing is to keep track of the 'normal' number of tuples > > read when executing a prepared query, and logging any queries that are > > substantially different. So, if you normally have to read 50 tuples to > > find all 'N' records, when the query looking for 'D' records comes along > > and has to read 5000 tuples instead, we want to log that. Probably the > > easiest way to accomplish this is to store a moving average of tuples > > read with each prepared statement entry. > > -- > > 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 6: explain analyze is your friend > > > > -- > Bruce Momjian | http://candle.pha.pa.us > pgman@candle.pha.pa.us | (610) 359-1001 > + If your life is a hard drive, | 13 Roberts Road > + Christ can be your backup. | Newtown Square, Pennsylvania 19073 > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq > -- 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
On Thu, Dec 22, 2005 at 10:14:15PM +0100, Lukas Smith wrote: > Ok, just so I understand this correctly. In the mentioned case the > cardinality does not really change in regards to the table stats, its > just thatI happen to use a value that has a different selectivity and > therefore I may need a different plan. So I do not really see how this > use case is handled with the above todo, nor do I really see how its > handled with what Jim suggested earlier. The fact of the matter is that > for this use case you need to use different query plans for the same > prepared statements. What I mentioned would allow for identifying query plans that this is happening on. Doing something about it would be the next step after that. Can anyone think of something other than selectivity that would make a stored query plan go bad based soley on the parameters being fed into it? (In other words ignore the obvious cases of bad statistics or a DDL change). -- 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
We need invalidation anyway, so I don't see why an intermediate step makes sense. --------------------------------------------------------------------------- Jim C. Nasby wrote: > Well, not just rows; total tuples, both base heap and index. ISTM that > would be a better metric than just plain rows read out of base or rows > returned. > > Depending on how far down this road we want to go, this would allow for > detecting what parameter values require different query plans, and then > using different query plans for different sets of values. Simply > invalidating the cached plan means you could potentially end up needing > to re-plan very frequently. But given the current speed of our > optimizer, it's probably not worth going to this extent. > > Another concern I have is: is cardinality the only metric we need to > look at when deciding to re-plan or are there others? > > In either case, my guess is that tracking the info needed to make this > idea happen is probably much easier than doing automatic plan > invalidation based on cardinality, so it would be a useful interum step. > But if we could actually get cardinality invalidation into 8.2, I'd say > put the effort into that... > > On Thu, Dec 22, 2005 at 03:14:09PM -0500, Bruce Momjian wrote: > > > > Oh, OK, so you are logging prepared queries where the plan generates a > > significantly different number of rows from previous runs. I am not > > sure why that is better, or easier, than just invalidating the cached > > plan if the cardinality changes. > > > > --------------------------------------------------------------------------- > > > > Jim C. Nasby wrote: > > > On Wed, Dec 21, 2005 at 11:00:31PM -0500, Bruce Momjian wrote: > > > > > Track normal resource consumption (ie: tuples read) for planned queries > > > > > and record parameter values that result in drastically different > > > > > resource consumption. > > > > > > > > > > This would at least make it easy for admins to identify prepared queries > > > > > that have a highly variable execution cost. > > > > > > > > We have that TODO already: > > > > > > > > * Log statements where the optimizer row estimates were dramatically > > > > different from the number of rows actually found? > > > > > > Does the stored plan also save how many rows were expected? Otherwise > > > I'm not sure how that TODO covers it... If it does then please ignore my > > > ramblings below. :) > > > > > > My idea has nothing to do with row estimates. It has to do with the > > > amount of work actually done to perform a query. Consider this example: > > > > > > CREATE TABLE queue (status char NOT NULL, queue_item text NOT NULL); > > > CREATE INDEX queue__status ON queue (status); > > > > > > Obviously, to process this you'll need a query like: > > > SELECT * FROM queue WHERE status='N' -- N for New; > > > > > > Say you also occasionally need to see a list of items that have been > > > processed: > > > SELECT * FROM queue WHERE status='D' -- D for Done; > > > > > > And let's say you need to keep done items around for 30 days. > > > > > > Now, if both of these are done using a prepared statement, it's going to > > > look like: > > > > > > SELECT * FROM queue WHERE status='?'; > > > > > > If the first one to run is the queue processing one, the planner will > > > probably choose the index. This means that when we're searching on 'N', > > > there will be a fairly small number of tuples read to execute the query, > > > but when searching for 'D' a very large number of tuples will be read. > > > > > > What I'm proposing is to keep track of the 'normal' number of tuples > > > read when executing a prepared query, and logging any queries that are > > > substantially different. So, if you normally have to read 50 tuples to > > > find all 'N' records, when the query looking for 'D' records comes along > > > and has to read 5000 tuples instead, we want to log that. Probably the > > > easiest way to accomplish this is to store a moving average of tuples > > > read with each prepared statement entry. > > > -- > > > 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 6: explain analyze is your friend > > > > > > > -- > > Bruce Momjian | http://candle.pha.pa.us > > pgman@candle.pha.pa.us | (610) 359-1001 > > + If your life is a hard drive, | 13 Roberts Road > > + Christ can be your backup. | Newtown Square, Pennsylvania 19073 > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 3: Have you checked our extensive FAQ? > > > > http://www.postgresql.org/docs/faq > > > > -- > 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 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Bruce Momjian wrote: > Right, if the cardinality changes, you realize this before execution and > optimize/save the plan again. A further optimization would be to save > _multiple_ plans for a single prepared plan based on constants and > choose one of the other, but that is beyond where we are willing to > consider at this stage, I think. ok .. so you store the cardinality that was used when generating the original plan. on the next execution you look up the cardinality again and compare it, if its off too much, you replan. however this could in extreme cases mean that you replan on every execution and thereby killing off the entire advantage of storing the plan. but thats the absolute worse case scenario. regards, Lukas PS: bruce original email was only send to me directly ..
On Thursday 2005-12-22 14:28, Lukas Kahwe Smith wrote: > Bruce Momjian wrote: > > Right, if the cardinality changes, you realize this before execution and > > optimize/save the plan again. A further optimization would be to save > > _multiple_ plans for a single prepared plan based on constants and > > choose one of the other, but that is beyond where we are willing to > > consider at this stage, I think. > > ok .. so you store the cardinality that was used when generating the > original plan. on the next execution you look up the cardinality again > and compare it, if its off too much, you replan. however this could in > extreme cases mean that you replan on every execution and thereby > killing off the entire advantage of storing the plan. but thats the > absolute worse case scenario. > > regards, > Lukas > > PS: bruce original email was only send to me directly .. So you have a parameterized query (one parameter for simplicity of argument), as the parameter changes, cardinality changes dramatically. It seems to me that in this case better than replanning is building a data structure that associates different parameter values with appropriate plans. The plans can be reused until, as would be the case with an no-parameter query, a parameter specific plan should be flushed (or the entire family of plans can be flushed).
Trent Shipley wrote: > On Thursday 2005-12-22 14:28, Lukas Kahwe Smith wrote: > > Bruce Momjian wrote: > > > Right, if the cardinality changes, you realize this before execution and > > > optimize/save the plan again. A further optimization would be to save > > > _multiple_ plans for a single prepared plan based on constants and > > > choose one of the other, but that is beyond where we are willing to > > > consider at this stage, I think. > > > > ok .. so you store the cardinality that was used when generating the > > original plan. on the next execution you look up the cardinality again > > and compare it, if its off too much, you replan. however this could in > > extreme cases mean that you replan on every execution and thereby > > killing off the entire advantage of storing the plan. but thats the > > absolute worse case scenario. > > > > regards, > > Lukas > > > > PS: bruce original email was only send to me directly .. > > So you have a parameterized query (one parameter for simplicity of argument), > as the parameter changes, cardinality changes dramatically. > > It seems to me that in this case better than replanning is building a data > structure that associates different parameter values with appropriate plans. > The plans can be reused until, as would be the case with an no-parameter > query, a parameter specific plan should be flushed (or the entire family of > plans can be flushed). TODO updated:* Flush cached query plans when the dependent objects change, when the cardinality of parameters changes dramatically,or when new ANALYZE statistics are available A more complex solution would be to save multiple plans for different cardinality and use the appropriate plan based on the EXECUTE values. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073