Stacking views is a bad practice. It usually means that you are making the db do a lot of unnecessary work, scanning tables more than once when you don't even need them. According to your description, you have 3 layers of views on partitioned tables. I can imagine that that leaves the planner with a lot of possible query plans, a lot of interaction and a lot of statistics to read.
do you have any special settings for the statistics on these tables? and could you please post the non-default settings in your postgresql.conf file? $ grep ^[^#] /etc/postgresql/9.1/main/postgresql.conf|grep -e ^[^[:space:]] Would be helpful to see if you have any statistics or planner stuff altered. Cheers, WBL On Wed, Apr 25, 2012 at 8:18 AM, Toby Corkindale < toby.corkind...@strategicdata.com.au> wrote: > Hi, > I'm hitting some peculiar behaviour. I'm currently on Pg 9.1.3 on a 64bit > Debian system. > > I have a database which is moderately large - 20 GByte or so - and > contains that data split up over dozens of tables, which are themselves > partitioned. > Queries are usually only run against fairly small, partitioned, sets of > data. > > These queries generally run fairly fast. Performance is not a problem. > > However Postgres is chewing up huge amounts of memory just to create the > query plan! > > For example, even if I just run > EXPLAIN SELECT a_column FROM a_view > WHERE partition_id = 1; > > Then the postgres backend process takes several seconds to return, and in > the worst example here, is hogging more than 3Gbyte once it comes back. (It > doesn't free that up until you close the connection) > > The query plan that comes back does seem quite convoluted, but then, the > view is a query run over about eight other views, each of which is pulling > data from a few other views. The actual underlying data being touched is > only *a few dozen* small rows. > > As I said, the query runs fast enough.. however we only need a handful of > these queries to get run in separate connections, and the database server > will be exhausted of memory. Especially since the memory isn't returned > until the end of the connection, yet these connections typically stay up > for a while. > > I wondered if there's anything I can do to reduce this memory usage? And, > is this a bug? > > I've posted the output of the query plan here: > https://gist.github.com/2487097 > > > Thanks in advance, > Toby > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- "Quality comes from focus and clarity of purpose" -- Mark Shuttleworth
