Re: [BUGS] BUG #14726: Memory consumption of PreparedStatement

dmigowski@ikoffice.de writes:
> I concluded that the query plan of the statement, which I uploaded to despez
> would result in 30MB of memory footprint!
> https://explain.depesz.com/s/gN2

Really?  There's 132 plan nodes in that plan.  I could believe that we're
eating several KB per plan node, but not 220KB per node.

> * How can I determine the memory footprint of prepared statements?

It's not something we track specifically.

> * Wouldn't it be useful if we could give a memory limit for prepared
> statements on the server, so that PostgreSQL automatically evicts them if
> more are prepared, maybe by using an LRU list? 

I'd be against that, as it pretty much would destroy the point of having
prepared statements at all --- if the server forgets them at random, or
even just has to re-prepare them unexpectedly, it's not holding up its
end of the contract.  It's the application's job to use that resource
in a prudent manner, just like any other SQL resource.

> PostgreSQL could automatically replan them when they get used again, I
> think.

This view seems like a poster child for why that would be a bad idea
--- that query has to take a mighty long time to plan.
        regards, tom lane


-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs