From: Tom Lane <tgl@sss.pgh.pa.us>
To: dougt@mugc.cc.monash.edu.au
Cc: pgsql-interfaces@postgreSQL.org
Subject: Re: [INTERFACES] Back end memory consumption (was Front end memory consumption in SELECT)
Date: Thu, 02 Dec 1999 22:10:01 -0500
Message-ID: <29989.944190601@sss.pgh.pa.us> (raw)
In-Reply-To: <199912030212.NAA16861@mugca.cc.monash.edu.au>
Douglas Thomson <dougt@mugc.cc.monash.edu.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-interfaces@postgresql.org
Cc: tgl@sss.pgh.pa.us, dougt@mugc.cc.monash.edu.au
Subject: Re: [INTERFACES] Back end memory consumption (was Front end memory consumption in SELECT)
In-Reply-To: <29989.944190601@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox