Re: [INTERFACES] Back end memory consumption (was Front end memory consumption in SELECT)

Douglas Thomson <dougt(at)mugc(dot)cc(dot)monash(dot)edu(dot)au> writes:
> If I hadn't checked that I have indexes on
> all the attributes (and attribute combinations) that make up the keys
> needed for the join, I would think the back end was trying to read in
> and sort a large part of the data just for the query!

Well, of course it *is* ... the trick is to make sure that anything
really large ends up on disk (in a temp file) and not in memory.

Offhand the plan looks pretty reasonable for a 9-way join. I see that
you have two sorts and five hashes going on here. Each of those will
feel entitled to use as much memory as whatever your -S setting is,
which IIRC is 512K by default. But 3.5meg altogether isn't enough to
explain your memory usage ... unless you are using a larger-than-normal
-S switch?

Also, while the sorts are pretty certain to honor your -S limit (plus or
minus some slop), the hashes are much more probabilistic affairs. If a
table is much larger than the planner guesses, or if the distribution of
tuple values is very uneven, a hash join might use a lot more memory
than -S. It's hard to tell whether this might be happening without a
lot more info than EXPLAIN provides, however. EXPLAIN shows that your
tables being hashed are all pretty small, the largest being
't24 users' at 6955 rows. If that's accurate then I doubt hash overrun
is the explanation...

Anyone have any other ideas?

regards, tom lane