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 [EMAIL PROTECTED]
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
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
