Hi all,

Looking through the query preparing and stats analyze code, I noticed that
prepared queries using an analyzed table are not replanned. I imagine that
some users of prepared queries would not want the above behaviour, plan
stability etc, but surely others would.

I didn't notice any discusion of this in the list archives. Any technical
reasons why this shouldn't happen?

Thanks,

Gavin


Mon, 28 Apr 2003 09:18:44 +1000 (EST)
Gavin Sherry <swm@linuxworld.com.au>
Prepared queries and ANALYZE
Gavin Sherry <swm@linuxworld.com.au> writes:
> Looking through the query preparing and stats analyze code, I noticed that
> prepared queries using an analyzed table are not replanned. I imagine that
> some users of prepared queries would not want the above behaviour, plan
> stability etc, but surely others would.
> I didn't notice any discusion of this in the list archives. Any technical
> reasons why this shouldn't happen?

Well, there are implementation reasons why it doesn't happen: there's no
infrastructure for determining which tables a prepared query depends on
nor for re-planning it if we did notice they'd changed.

But really this is a special case of the problem of "schema changed
underneath a plan", and yes it'd be nice to notice that and invalidate
the plan.

			regards, tom lane


Mon, 28 Apr 2003 00:57:10 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Prepared queries and ANALYZE 
>>>>> "Tom" == Tom Lane <tgl@sss.pgh.pa.us> writes:

    Tom> But really this is a special case of the problem of "schema
    Tom> changed underneath a plan", and yes it'd be nice to notice
    Tom> that and invalidate the plan.

In general, pgsql doesn't cache access plans, correct ? 

If there was a cache of access plans, repeated queries (not just
prepared by the same client) could use the same plan avoiding
recompilation. The cache will also be a single place in shared memory
where invalidation can take place.

Just my 2c.

-- 
Pip-pip
Sailesh
http://www.cs.berkeley.edu/~sailesh


27 Apr 2003 22:01:02 -0700
Sailesh Krishnamurthy <sailesh@cs.berkeley.edu>
Re: Prepared queries and ANALYZE
On Mon, 28 Apr 2003, Tom Lane wrote:

> Gavin Sherry <swm@linuxworld.com.au> writes:
> > Looking through the query preparing and stats analyze code, I noticed that
> > prepared queries using an analyzed table are not replanned. I imagine that
> > some users of prepared queries would not want the above behaviour, plan
> > stability etc, but surely others would.
> > I didn't notice any discusion of this in the list archives. Any technical
> > reasons why this shouldn't happen?
> 
> Well, there are implementation reasons why it doesn't happen: there's no
> infrastructure for determining which tables a prepared query depends on
> nor for re-planning it if we did notice they'd changed.

Yes. Speaking to Bruce on IRC I was reminded that prepared queries exist
on a single backend which makes such this problem less pertinent than a
general solution, which you mentioned.

Gavin


Mon, 28 Apr 2003 15:37:17 +1000 (EST)
Gavin Sherry <swm@linuxworld.com.au>
Re: Prepared queries and ANALYZE 