Re: Statement Pooling

> > > > I remember postgresql 8.4 is replanning prepared statements when
> > > > statistics change occur, but I didn't find it in the release notes.
> > > > It is just saying "Invalidate cached plans when referenced schemas,
> > > > functions, operators, or operator classes are modified". Does PG
> > > > replans prepared statements from time to time if underlying data
> > > > statistics change?
> > >
> > > I don't think so, though I may be wrong. The change you refer to
> > > replans such things when the actual objects change, such as when you
> > > remove a column or something that would make the plan fail to execute.
> >
> > Ok, so i should be aware of this. if the execution plan postgresql is
> > caching gets old, i can run into trouble.
> >
> > So i should close my connections in the pool from time to time to force
> > replanning of my prepared statements.
>
> I'm not sure this is a great idea, but it would depend on your application.
> With caching and pooling and such, a prepared statement might live a really
> long time, but your database's statistics might take even longer to change
> sufficiently to suggest one plan is better than another. Explicitly
> replanning sounds like it's probably more work than it's worth, unless you
> see specific plan problems.

I know close my connections every 5th hour, so a new connection can create new
preparedStatements with a new execution plan.

I had some situations in the past, where an old plan took so much longer. I
think planning a statement doesn't cost so much compared with executing a
statement with a wrong plan (like sequence scan instead of using an index)

But I remember a post of tom lane who said that prepared statements get
replaned since 8.4 if underlying statistics change. But I really can't find it.
Maybe I do not remember correctly.

kind regards
Janning

> --
> Joshua Tolley / eggyknap
> End Point Corporation
> http://www.endpoint.com