Massive memory use for star query - Mailing list 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