From: Tom Lane <tgl@sss.pgh.pa.us>
To: Hartranft, Robert M. (GSFC-423.0)[RAYTHEON CO] <robert.m.hartranft@nasa.gov>
Cc: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: Re: question on hash joins
Date: Wed, 18 Oct 2017 17:17:06 -0400
Message-ID: <22675.1508361426@sss.pgh.pa.us> (raw)
In-Reply-To: <63F14C4C-1518-4EAA-9EBE-87222988F073@nasa.gov>
References: <60286BB5-0A38-42C8-A673-5289CA6D2354@nasa.gov>
<63F14C4C-1518-4EAA-9EBE-87222988F073@nasa.gov>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
"Hartranft, Robert M. (GSFC-423.0)[RAYTHEON CO]" <robert.m.hartranft@nasa.gov> writes:
> explain select count(1) from table1 g join table2 x on x.granuleid = g.granuleid where g.collectionid = 22467;
> QUERY PLAN
> -------------------------------------------------------------------------------------------------------------------------------
> Aggregate (cost=18200480.80..18200480.81 rows=1 width=8)
> -> Hash Join (cost=103206.82..18190602.43 rows=3951347 width=0)
> Hash Cond: (x.granuleid = g.granuleid)
> -> Seq Scan on table2 x (cost=0.00..10596253.01 rows=644241901 width=8)
> -> Hash (cost=92363.72..92363.72 rows=660888 width=8)
> -> Index Only Scan using idx_table1 on table1 g (cost=0.57..92363.72 rows=660888 width=8)
> Index Cond: (collectionid = '22467'::bigint)
> (7 rows)
> My question is, what gets put into the Hash?
> I assume the with "width=8" must refer to the size of the key.
It's the data payload width, but there's some per-row overhead too, which
is more than 8 bytes ... don't recall how much more at the moment.
> The reason I ask is because, when I try to run the query it fails due to
> temp file use over 10GB.
What have you got work_mem set to? If the hashed table exceeds that,
you're going to end up with a multi-batch join, in which case most of
the outer table is going to get written into temp files as well.
I don't think it's a good idea to have such a small temp_file_limit
when you're working with such huge tables. Either a merge or hash
join is likely to require temp file space similar to the table size,
and you do *not* want a nestloop join, at least not if you want your
answers in reasonable time.
regards, tom lane
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
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-admin@postgresql.org
Cc: tgl@sss.pgh.pa.us, robert.m.hartranft@nasa.gov
Subject: Re: question on hash joins
In-Reply-To: <22675.1508361426@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