Re: Hash Join performance

From: Tom Lane
Subject: Re: Hash Join performance
Date: ,
Msg-id: 22599.1236985691@sss.pgh.pa.us
(view: Whole thread, Raw)
In response to: Re: Hash Join performance  (Vamsidhar Thummala)
Responses: Re: Hash Join performance  (Vamsidhar Thummala)
List: pgsql-performance

Tree view

Hash Join performance  (Vamsidhar Thummala, )
 Re: Hash Join performance  (Tom Lane, )
  Re: Hash Join performance  (Vamsidhar Thummala, )
   Re: Hash Join performance  (Tom Lane, )
    Re: Hash Join performance  (Vamsidhar Thummala, )

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.

> 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.

            regards, tom lane


pgsql-performance by date:

From: david@lang.hm
Date:
Subject: Re: Proposal of tunable fix for scalability of 8.4
From: Heikki Linnakangas
Date:
Subject: Re: Proposal of tunable fix for scalability of 8.4