Massive memory use for star query
От | Mark Kirkwood |
---|---|
Тема | Massive memory use for star query |
Дата | |
Msg-id | 4DA7CB25.5040102@catalyst.net.nz обсуждение исходный текст |
Ответы |
Re: Massive memory use for star query
Re: Massive memory use for star query Re: Massive memory use for star query |
Список | 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
Вложения
В списке pgsql-bugs по дате отправления: