Re: Hash Join performance

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