To: pgsql-performance@postgresql.org
From: Dave Roberge <droberge@bluetarp.com>
Subject: Hash Join node sometimes slow
Date: Wed, 2 Jul 2014 13:01:47 +0000 (UTC)
Lines: 41
Message-ID: <loom.20140702T144820-187@post.gmane.org>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
User-Agent: Loom/3.14 (http://gmane.org/)
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

Hi, I'm in the process of attempting to tune some slow queries. I came 
across a scenario where I'm not entirely sure how I 
might figure out why a node is taking awhile to process. I'm not concerned 
with the query itself, we are working to figure 
out how we can make it faster. But I was hoping someone might be able to 
provide some insight into why a hash join is 
sometimes slow.

For example, running explain (analyze, buffers) with the query, 4/5 times we 
will see the following:

->  Hash Join  (cost=16385.76..103974.09 rows=523954 width=64) (actual 
time=532.634..4018.678 rows=258648 loops=1)
      Hash Cond: (p.a = c.c)
      Buffers: shared hit=4 read=29147, temp read=12943 written=12923
      ->  Seq Scan on p (cost=0.00..38496.88 rows=1503188 width=60) (actual 
time=0.013..1388.205 rows=1503188 loops=1)
            Buffers: shared hit=1 read=23464
      ->  Hash  (cost=15382.47..15382.47 rows=57703 width=12) (actual 
time=527.237..527.237 rows=57789 loops=1)
            Buckets: 4096  Batches: 4  Memory Usage: 632kB
            Buffers: shared hit=3 read=5683, temp read=617 written=771

The other times, we will see something like this:

->  Hash Join  (cost=16385.76..103974.09 rows=523954 width=64) (actual 
time=587.277..15208.621 rows=258648 loops=1)
      Hash Cond: (p.a = c.c)
      Buffers: shared hit=26 read=29125, temp read=12943 written=12923
      ->  Seq Scan on p  (cost=0.00..38496.88 rows=1503188 width=60) (actual 
time=0.013..1525.608 rows=1503188 loops=1)
            Buffers: shared hit=22 read=23443
      ->  Hash  (cost=15382.47..15382.47 rows=57703 width=12) (actual 
time=581.638..581.638 rows=57789 loops=1)
            Buckets: 4096  Batches: 4  Memory Usage: 632kB
            Buffers: shared hit=4 read=5682, temp read=617 written=771

Does anyone have ideas on what might be causing the difference in timing for 
the hash join node?

Thanks


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance