Massive memory use for star query

From: Mark Kirkwood <mark(dot)kirkwood(at)catalyst(dot)net(dot)nz>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Massive memory use for star query
Date: 2011-04-15 04:35:49
Message-ID: 4DA7CB25.5040102@catalyst.net.nz
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

I've recently seen examples of star-like queries using vast amounts of
memory in one of our production systems. Here's a simplified example
using synthetic data (see attached to generate if desired):

SET geqo_threshold = 14;
SET from_collapse_limit = 14;
SET join_collapse_limit = 14;

EXPLAIN
SELECT
1
FROM node n
JOIN nodekeyword kwn0 ON (n.nodeid = kwn0.nodeid)
JOIN keyword kw0 ON (kwn0.keywordid = kw0.keywordid)
JOIN nodekeyword kwn1 ON (n.nodeid = kwn1.nodeid)
JOIN keyword kw1 ON (kwn1.keywordid = kw1.keywordid)
JOIN nodekeyword kwn2 ON (n.nodeid = kwn2.nodeid)
JOIN keyword kw2 ON (kwn2.keywordid = kw2.keywordid)
JOIN nodekeyword kwn3 ON (n.nodeid = kwn3.nodeid)
JOIN keyword kw3 ON (kwn3.keywordid = kw3.keywordid)
JOIN nodekeyword kwn4 ON (n.nodeid = kwn4.nodeid)
JOIN keyword kw4 ON (kwn4.keywordid = kw4.keywordid)
JOIN nodekeyword kwn5 ON (n.nodeid = kwn5.nodeid)
JOIN keyword kw5 ON (kwn5.keywordid = kw5.keywordid)
WHERE kw0.keyword = 'sscghryv'
AND kw1.keyword = 'sscghryv'
AND kw2.keyword = 'sscghryv'
AND kw3.keyword = 'sscghryv'
AND kw4.keyword = 'sscghryv'
AND kw5.keyword = 'sscghryv'
;

Here's what a ps listing looks like:

VSZ RSS SZ CMD
1849524 1793680 1791144 postgres: postgres test [local] EXPLAIN

So we are using 1.7G doing an *EXPLAIN* - so presumably this is gonna be
the join search planning getting expensive for 13 tables. Is it expected
that this much memory could/would be used? Could this be evidence of a
leak?

Note this is a default 9.1 (2011-04-07) build w/o asserts, with a
default postgresql.conf.

Clearly this particular query is a bit dumb, making the keyword
predicates have different values results in much better behaved planning
memory usage... and also allowing geqo to do the join search for us
prevents the high memory use (however geqo has its own problems.... in
the production variant of this query *one* of the plans it would pick
liked to use >100G of temp space to execute...and there are only 100G
available...sigh). However for these semi ad-hoc systems it is hard to
prevent dumb queries altogether!

regards

Mark

Attachment Content-Type Size
starjoin.tar.gz application/x-gzip 1.8 KB

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Magnus Hagander 2011-04-15 07:47:56 Re: BUG #5979: postgres service cannot be started if database is located in NAS
Previous Message Chris Price 2011-04-15 01:16:42 BUG #5981: Attempt to install language pltcl fails on 64-bit installation