Обсуждение: Prepared queries and ANALYZE

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

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

>>>>> "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
niceto 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

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