From: Vamsidhar Thummala <vamsi@cs.duke.edu>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: Hash Join performance
Date: Fri, 13 Mar 2009 18:11:52 -0400
Message-ID: <e0e3da5e0903131511q664ec424lfbf5b6c36da84598@mail.gmail.com> (raw)
In-Reply-To: <18410.1236980078@sss.pgh.pa.us>
References: <e0e3da5e0903131407k75ca87a4j5c7dcc360805d5c1@mail.gmail.com>
<e0e3da5e0903131415k38dc870bo15c6ad2625cd922b@mail.gmail.com>
<18410.1236980078@sss.pgh.pa.us>
Thanks for such quick response.
On Fri, Mar 13, 2009 at 5:34 PM, Tom Lane wrote:
> > 2) Why is the Hash Join (top most) so slow?
>
> Doesn't look that bad to me. The net time charged to the HashJoin node
> is 186107.210 - 53597.555 - 112439.592 = 20070.063 msec. In addition it
> would be reasonable to count the hashtable build time, which evidently
> is 112439.592 - 111855.510 = 584.082 msec. So the hashtable build took
> about 10 msec/row, in addition to the data fetching; and then the actual
> join spent about 3 microsec per outer row, again exclusive of obtaining
> those rows. The table build seems a bit slow, maybe, but I don't see a
> problem with the join speed.
>
I am wondering why are we subtracting the entire Seq Scan time of Lineitem
from the total time to calculate the HashJoin time.
Does the Hash probing start as soon as the first record of Lineitem is
available, i.e., after 112439.592ms?
Here is another plan I have for the same TPC-H 18 query with different
configuration parameters (shared_buffers set to 400MB, just for experimental
purposes) and HashJoin seems to take longer time (at least 155.58s based on
above calculation):
GroupAggregate (cost=905532.09..912092.04 rows=119707 width=57)
(actual time=392705.160..392705.853 rows=57 loops=1)
-> Sort (cost=905532.09..906082.74 rows=119707 width=57) (actual
time=392705.116..392705.220 rows=399 loops=1)
Sort Key: orders.o_totalprice, orders.o_orderdate,
customer.c_name, customer.c_custkey, orders.o_orderkey
-> Hash Join (cost=507550.05..877523.36 rows=119707
width=57) (actual time=72616.327..392703.675 rows=399 loops=1)
Hash Cond: (public.lineitem.l_orderkey = orders.o_orderkey)
-> Seq Scan on lineitem (cost=0.00..261655.05
rows=6000947 width=14) (actual time=0.027..178712.709 rows=6001215
loops=1)
-> Hash (cost=506580.84..506580.84 rows=29921
width=51) (actual time=58421.050..58421.050 rows=57 loops=1)
-> Hash Join (cost=416568.25..506580.84
rows=29921 width=51) (actual time=25208.925..58419.502 rows=57
loops=1)
Hash Cond: (orders.o_custkey = customer.c_custkey)
-> Merge IN Join
(cost=405349.14..493081.88 rows=29921 width=29) (actual
time=37.244..57646.024 rows=57 loops=1)
Merge Cond: (orders.o_orderkey =
"IN_subquery".l_orderkey)
-> Index Scan using orders_pkey on
orders (cost=0.00..79501.17 rows=1499952 width=25) (actual
time=0.100..5379.828 rows=1496151 loops=1)
-> Materialize
(cost=405349.14..406004.72 rows=29921 width=4) (actual
time=34.825..51619.816 rows=57 loops=1)
-> GroupAggregate
(cost=0.00..404639.71 rows=29921 width=14) (actual
time=34.818..51619.488 rows=57 loops=1)
Filter: (sum(l_quantity)
> 300::numeric)
-> Index Scan using
fkey_lineitem_1 on lineitem (cost=0.00..348617.14 rows=6000947
width=14) (actual time=0.079..44140.117 rows=6001215 loops=1)
-> Hash (cost=6803.60..6803.60
rows=149978 width=26) (actual time=640.980..640.980 rows=150000
loops=1)
-> Seq Scan on customer
(cost=0.00..6803.60 rows=149978 width=26) (actual time=0.021..510.993
rows=150000 loops=1)
I re-ran the query multiple times to verify the accuracy of results.
Regards,
~Vamsi
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-performance@postgresql.org
Cc: vamsi@cs.duke.edu, tgl@sss.pgh.pa.us
Subject: Re: Hash Join performance
In-Reply-To: <e0e3da5e0903131511q664ec424lfbf5b6c36da84598@mail.gmail.com>
* 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