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
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
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:
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
Lukas Smith [EMAIL PROTECTED] 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
Chris Browne wrote:
Lukas Smith [EMAIL PROTECTED] 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
Lukas Smith [EMAIL PROTECTED] 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
Chris Browne wrote:
Lukas Smith [EMAIL PROTECTED] 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
Lukas Smith [EMAIL PROTECTED] 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
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
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
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
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
35 matches
Mail list logo
