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 prep
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 an
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
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 ro
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
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
us
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 unders
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
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 f
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 kno
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 m
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
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.
-
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
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 mo
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
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
> >
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 an
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
> >
> > Wo
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 diffe
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
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 me
> 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
>> >
>> > W
> 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
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 whe
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
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
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
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 rep
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
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:10P
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
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
34 matches
Mail list logo
