Re: Performance bug in DO blocks

On Tue, Jan 18, 2011 at 1:46 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> I just noticed that if you execute the same DO command over and over
> within a session, it gets slower and slower.  And if you keep it up
> you'll notice the backend's RAM consumption bloating too.  The cause
> appears to be that we leak the cached plans created for any SQL
> statements or expressions within the DO command --- the next iteration
> won't reuse those, but rather create its own set.  Probably ought to
> look into releasing those when the DO block is over.

Should we try to do something about this?

I don't really understand what's going on here. I thought maybe the
problem was that the inline handler was getting called with
TopMemoryContext active, but it's not. It's getting called with the
PortalHeapMemory context active, so anything that is allocated without
first specifying the context ought to get cleaned up at
end-of-statement. So somewhere we're deliberately copying something
into a longer-lived memory context where we shouldn't be. Your
comment about cached plans got me looking at CreateCachedPlan(), which
does this; it's called from SPI_saveplan(), which is called from
exec_prepare_plan(). But that's immediately followed by
SPI_freeplan(), so I'm all tapped out.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company