[BUGS] Massive memory use for star query

[Mark Kirkwood]

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







regards

Mark

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!

starjoin.tar.gz
Description: GNU Zip compressed data

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