Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id E6DBE632E83 for ; Fri, 13 Mar 2009 19:11:56 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 03927-08 for ; Fri, 13 Mar 2009 19:11:50 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from rv-out-0506.google.com (rv-out-0506.google.com [209.85.198.229]) by mail.postgresql.org (Postfix) with ESMTP id 039EA632E7F for ; Fri, 13 Mar 2009 19:11:53 -0300 (ADT) Received: by rv-out-0506.google.com with SMTP id f6so1948491rvb.43 for ; Fri, 13 Mar 2009 15:11:52 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:sender:received:in-reply-to :references:date:x-google-sender-auth:message-id:subject:from:to:cc :content-type; bh=+knYe02HQC3HeHRMbmVuFYXI3icwfA5LXmqWkUIl95E=; b=K7C9rAJL7SjvB6L2HMJgAc7jgkbVIDkc7VxxBS84B3LS0sHlHS+gPxVfaJ/ZrD75ox KR3bCPSARR62jG9R1HHMin6M/0dNJOaHc/kU7e/fxDf12rmVWtd/85mnofD6A93h0fZ6 Zbsb1d5DJnNIE/MUK1yFnFGwMq0J50WIr/RGA= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:sender:in-reply-to:references:date :x-google-sender-auth:message-id:subject:from:to:cc:content-type; b=KhIEF76B1HLT48tNZf59mQSnPnWMAqh4FZSstns4fJEgwCvUu8JVC5S09y8Z3u2Nrz FTfZlKGXLUrfz3ycIo4XO67ZLNHt7KPmgK7thkr29KU3KuC6N/eLuD2X/GPR+ddhpmud gevJbilLNkTKT0bUb3YC7qdohWJnsK1TPOXzc= MIME-Version: 1.0 Received: by 10.141.107.13 with SMTP id j13mr837048rvm.251.1236982312394; Fri, 13 Mar 2009 15:11:52 -0700 (PDT) In-Reply-To: <18410.1236980078@sss.pgh.pa.us> References: <18410.1236980078@sss.pgh.pa.us> Date: Fri, 13 Mar 2009 18:11:52 -0400 X-Google-Sender-Auth: 1cdd0b277fee41c3 Message-ID: Subject: Re: Hash Join performance From: Vamsidhar Thummala To: Tom Lane Cc: pgsql-performance@postgresql.org Content-Type: multipart/alternative; boundary=000e0cd13a2a52e1d1046507630f X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200903/178 X-Sequence-Number: 33100 --000e0cd13a2a52e1d1046507630f Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit 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 --000e0cd13a2a52e1d1046507630f Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Thanks for such quick response.

On Fri, M= ar 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. =A0The net time charged to the HashJ= oin node
is 186107.210 - 53597.555 - 112439.592 =3D 20070.063 msec. =A0In addition i= t
would be reasonable to count the hashtable build time, which evidently
is 112439.592 - 111855.510 =3D 584.082 msec. =A0So 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. =A0The 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.= =A0
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 experimenta= l purposes) and HashJoin seems to take longer time (at least 155.58s based = on above calculation):

GroupAggregate  (cost=3D905532.09..912092.04 rows=3D119707 width=
=3D57) (actual time=3D392705.160..392705.853 rows=3D57 loops=3D1)
-&g= t; Sort (cost=3D905532.09..906082.74 rows=3D119707 width=3D57) (actual ti= me=3D392705.116..392705.220 rows=3D399 loops=3D1)
Sort Key: orders.o_totalprice, orders.o_orderdate, customer.c_name= , customer.c_custkey, orders.o_orderkey
-> Hash Join (cost= =3D507550.05..877523.36 rows=3D119707 width=3D57) (actual time=3D72616.327.= .392703.675 rows=3D399 loops=3D1)
Hash Cond: (public.lineitem.l_orderkey =3D orders.o_orderkey= )
-> Seq Scan on lineitem (cost=3D0.00..261655.05 ro= ws=3D6000947 width=3D14) (actual time=3D0.027..178712.709 rows=3D6001215 lo= ops=3D1)
-> Hash (cost=3D506580.84..506580.84 rows=3D29921 width= =3D51) (actual time=3D58421.050..58421.050 rows=3D57 loops=3D1)
= -> Hash Join (cost=3D416568.25..506580.84 rows=3D29921 wi= dth=3D51) (actual time=3D25208.925..58419.502 rows=3D57 loops=3D1)
Hash Cond: (orders.o_custkey =3D customer.c_cust= key)
-> Merge IN Join (cost=3D405349.14.= .493081.88 rows=3D29921 width=3D29) (actual time=3D37.244..57646.024 rows= =3D57 loops=3D1)
Merge Cond: (orders.o_orderkey =3D "I= N_subquery".l_orderkey)
-> Ind= ex Scan using orders_pkey on orders (cost=3D0.00..79501.17 rows=3D1499952 = width=3D25) (actual time=3D0.100..5379.828 rows=3D1496151 loops=3D1)
-> Materialize (cost=3D405349.14..406= 004.72 rows=3D29921 width=3D4) (actual time=3D34.825..51619.816 rows=3D57 l= oops=3D1)
-> GroupAggregate = (cost=3D0.00..404639.71 rows=3D29921 width=3D14) (actual time=3D34.818..516= 19.488 rows=3D57 loops=3D1)
Filter: (sum(l_quantity) > = 300::numeric)
-> Index = Scan using fkey_lineitem_1 on lineitem (cost=3D0.00..348617.14 rows=3D6000= 947 width=3D14) (actual time=3D0.079..44140.117 rows=3D6001215 loops=3D1) -> Hash (cost=3D6803.60..6803.60 rows=3D149= 978 width=3D26) (actual time=3D640.980..640.980 rows=3D150000 loops=3D1) -> Seq Scan on customer (cost=3D0.00= ..6803.60 rows=3D149978 width=3D26) (actual time=3D0.021..510.993 rows=3D15= 0000 loops=3D1)

I re-ran the query multiple times to verify the accuracy of resul= ts.

Regards,
~Vamsi
--000e0cd13a2a52e1d1046507630f--