Обсуждение: [ADMIN] question on hash joins

"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

Thanks for the help!

We set:  work_mem = 16MB

Also, table1 has 100 million rows, not 1 million; that is why it uses the index
in my query.

Sorry if I am being dense, but I still have a question…
Is it possible for me to estimate the size of the hash and a value for 
the temp_file_limit setting using information in the explain plan?

For example, one possibility is that the hash contains the entire tuple for each
matching row.  In that case, I could multiply the number of rows estimated in
the explain output times an average row size to get an “order of magnitude” 
estimate for temp_file_limit.

Another possibility would be the hash only contains the columns
needed to satisfy the remaining query clauses (in my example that would just be the
granuleid column) and thus much smaller.

Thanks in advance,
Bob



On 10/18/17, 5:17 PM, "Tom Lane" <tgl@sss.pgh.pa.us> wrote:
   "Hartranft, Robert M. (GSFC-423.0)[RAYTHEON CO]" <robert.m.hartranft@nasa.gov> writes:   > explain select count(1)
fromtable1 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=3951347width=0)   >          Hash Cond: (x.granuleid = g.granuleid)   >          ->  Seq Scan on table2 x
(cost=0.00..10596253.01rows=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-rowoverhead too, which   is more than 8 bytes ... don't recall how much more at the moment.      > The reason I ask
isbecause, 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
outertable 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
filespace similar to the table size,   and you do *not* want a nestloop join, at least not if you want your   answers
inreasonable 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

"Hartranft, Robert M. (GSFC-423.0)[RAYTHEON CO]" <robert.m.hartranft@nasa.gov> writes:
> Sorry if I am being dense, but I still have a question…
> Is it possible for me to estimate the size of the hash and a value for
> the temp_file_limit setting using information in the explain plan?

Well, it'd be (row_overhead + data_width) * number_of_rows.

Poking around in the source code, it looks like the row_overhead in
a tuplestore temp file is 10 bytes (can be more if you have nulls in
the data).  Your example seemed to be storing one bigint column,
so data_width is 8 bytes.  data_width can be a fairly squishy thing
to estimate if the data being passed through the join involves variable-
width columns, but the planner's number is usually an OK place to start.

> For example, one possibility is that the hash contains the entire tuple for each
> matching row.

No, it's just the columns that need to be used in or passed through the
join.  If you want to be clear about this you can use EXPLAIN VERBOSE
and check what columns are emitted by the plan node just below the hash.
        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

Thanks Tom,

Given that the hash would only contain keys and values needed for supporting
the query I am having a hard time understanding why I am exceeding the 
10 GB temp_file_limit.


In my case the hash estimates 660888 rows from table1
and I verified that is accurate.

In theory, the hash only contains a bigint key, bigint value, and some overhead.
But even if I assume 50 bytes per row that gives a total size of
33,044,400 bytes for the hash which is way below the 10 GB limit.

What could be causing the temp_file growth?


select granuleid
from table2
where granuleid in (select granuleid from table1 where collectionid = 22467);
ERROR:  temporary file size exceeds temp_file_limit (10485760kB)

explain select granuleid
from table2
where granuleid in (select granuleid from table1 where collectionid = 22467);
          QUERY PLAN                                                       
 

------------------------------------------------------------------------------------------------------------------------Hash
Join (cost=597264.56..18687640.08 rows=644348762 width=8)  Hash Cond: (table2.granuleid = table1.granuleid)  ->  Seq
Scanon table2  (cost=0.00..10598010.62 rows=644348762 width=8)  ->  Hash  (cost=586509.04..586509.04 rows=655562
width=8)       ->  Index Only Scan using idx_tabl1 on table1  (cost=0.57..586509.04 rows=655562 width=8)
IndexCond: (collectionid = '22467'::bigint)
 
(6 rows)


Bob


On 10/19/17, 10:14 AM, "Tom Lane" <tgl@sss.pgh.pa.us> wrote:
   "Hartranft, Robert M. (GSFC-423.0)[RAYTHEON CO]" <robert.m.hartranft@nasa.gov> writes:   > Sorry if I am being
dense,but I still have a question…   > Is it possible for me to estimate the size of the hash and a value for    > the
temp_file_limitsetting using information in the explain plan?      Well, it'd be (row_overhead + data_width) *
number_of_rows.     Poking around in the source code, it looks like the row_overhead in   a tuplestore temp file is 10
bytes(can be more if you have nulls in   the data).  Your example seemed to be storing one bigint column,   so
data_widthis 8 bytes.  data_width can be a fairly squishy thing   to estimate if the data being passed through the join
involvesvariable-   width columns, but the planner's number is usually an OK place to start.      > For example, one
possibilityis that the hash contains the entire tuple for each   > matching row.      No, it's just the columns that
needto be used in or passed through the   join.  If you want to be clear about this you can use EXPLAIN VERBOSE   and
checkwhat columns are emitted by the plan node just below the hash.                  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

"Hartranft, Robert M. (GSFC-423.0)[RAYTHEON CO]" <robert.m.hartranft@nasa.gov> writes:
> Given that the hash would only contain keys and values needed for supporting
> the query I am having a hard time understanding why I am exceeding the
> 10 GB temp_file_limit.

Because *both sides* of the join are getting dumped to temp files.
This is necessary when the hash requires multiple batches.  All
but the first batch of hash keys get written out to files, and then
we reload each batch of the inner relation into the in-memory table
and scan the corresponding batch file from the outer relation.

If you can make work_mem large enough to hold the inner relation
then the problem should go away.  Note though that the per-row
overhead is significantly larger in the in-memory representation;
don't have an estimate for that offhand.
        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