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 22:10:43 -0400
Message-ID: <e0e3da5e0903131910q53a387eei66300aa2f7481d9f@mail.gmail.com> (raw)
In-Reply-To: <22599.1236985691@sss.pgh.pa.us>
References: <e0e3da5e0903131407k75ca87a4j5c7dcc360805d5c1@mail.gmail.com>
<e0e3da5e0903131415k38dc870bo15c6ad2625cd922b@mail.gmail.com>
<18410.1236980078@sss.pgh.pa.us>
<e0e3da5e0903131511q664ec424lfbf5b6c36da84598@mail.gmail.com>
<22599.1236985691@sss.pgh.pa.us>
On Fri, Mar 13, 2009 at 7:08 PM, Tom Lane wrote:
> Vamsidhar Thummala writes:
> > I am wondering why are we subtracting the entire Seq Scan time of
> Lineitem
> > from the total time to calculate the HashJoin time.
>
> Well, if you're trying to identify the speed of the join itself and not
> how long it takes to provide the input for it, that seems like a
> sensible calculation to make.
I am still not clear on this. I am thinking the output is produced in a
pipelined fashion i.e., as soon as the record of outer child is read
(sequentially here) and if HashJoin finds a match by probing the inner hash
table (in memory), we have an output record. Please correct if I am wrong
here.
>
>
> > 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):
>
> Yeah, that seems to work out to about 25us per row instead of 3us, which
> is a lot slower. Maybe the hash got split up into multiple batches ...
> what have you got work_mem set to? Try turning on log_temp_files and
> see if it records any temp files as getting created.
Unfortunately, I am working with Postgres 8.2 which doesn't have
log_temp_files. The work_mem is still at 1MB (all other parameters were kept
constant apart from shared_buffers w.r.t previous configuration). The hash
is build on 57 records (~20kb, customer row length is 179 bytes and orders
row length is 104 bytes) produced by inner subplan and so I will be
surprised if multiple batches are created.
Thank you.
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: <e0e3da5e0903131910q53a387eei66300aa2f7481d9f@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