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

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

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, Pennsylvania 19073

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster