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
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>
Comments: In-reply-to "Hartranft,
 Robert M. (GSFC-423.0)[RAYTHEON CO]" <robert.m.hartranft@nasa.gov>
	message dated "Wed, 18 Oct 2017 20:00:14 -0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <22674.1508361426.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Wed, 18 Oct 2017 17:17:06 -0400
Message-ID: <22675.1508361426@sss.pgh.pa.us>
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

"Hartranft, Robert M. (GSFC-423.0)[RAYTHEON CO]" <robert.m.hartranft@nasa.g=
ov> writes:
> explain select count(1) from table1 g join table2 x on x.granuleid =3D g.=
granuleid where g.collectionid =3D 22467;
>                                                           QUERY PLAN
> -------------------------------------------------------------------------=
------------------------------------------------------
> Aggregate  (cost=3D18200480.80..18200480.81 rows=3D1 width=3D8)
>    ->  Hash Join  (cost=3D103206.82..18190602.43 rows=3D3951347 width=3D0)
>          Hash Cond: (x.granuleid =3D g.granuleid)
>          ->  Seq Scan on table2 x  (cost=3D0.00..10596253.01 rows=3D64424=
1901 width=3D8)
>          ->  Hash  (cost=3D92363.72..92363.72 rows=3D660888 width=3D8)
>                ->  Index Only Scan using idx_table1 on table1 g  (cost=3D=
0.57..92363.72 rows=3D660888 width=3D8)
>                      Index Cond: (collectionid =3D '22467'::bigint)
> (7 rows)

> My question is, what gets put into the Hash?
> I assume the with "width=3D8" 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


--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin