On Thu, Aug 31, 2006 at 11:36:36AM -0400, AgentM wrote: > On Aug 31, 2006, at 11:18 , [EMAIL PROTECTED] wrote: > >I'm attempting to understand why prepared statements would be used for > >long enough for tables to change to a point that a given plan will > >change from 'optimal' to 'disastrous'. > > > >Wouldn't this require that the tables are completely re-written, or > >that their data is drastically updated? For my own tables, most of the > >data remains static for months on end. Data is accumulated. Small > >changes are made. I don't see why a prepared statement used over a > >24 hour period would ever become disastrous.
> Scenario: A web application maintains a pool of connections to the > database. If the connections have to be regularly restarted due to a > postgres implementation detail (stale plans), then that is a database > deficiency. Or a JDBC deficiency. Nobody is forcing JDBC to automatically reuse a prepared plan indefinately. If automatically prepared, it can regenerate them whenever it wishes. Does Oracle automatically regenerate prepared plans on occasion? I don't consider it a deficiency. It is doing exactly what you are asking it to do. That it isn't second guessing you isn't a deficiency. For all PostgreSQL knows, your tables are not changing such that a query a week later is suddenly disastrous because the consistency of your data has changed drastically, and what you prepared a week ago, and chose to execute today, is still the optimal plan. Cheers, mark -- [EMAIL PROTECTED] / [EMAIL PROTECTED] / [EMAIL PROTECTED] __________________________ . . _ ._ . . .__ . . ._. .__ . . . .__ | Neighbourhood Coder |\/| |_| |_| |/ |_ |\/| | |_ | |/ |_ | | | | | | \ | \ |__ . | | .|. |__ |__ | \ |__ | Ottawa, Ontario, Canada One ring to rule them all, one ring to find them, one ring to bring them all and in the darkness bind them... http://mark.mielke.cc/ ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings