Skip site navigation (1) Skip section navigation (2)

Re: Why is a hash join being used?

From: "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
To: "Tim Jacobs" <tjacobs2(at)email(dot)unc(dot)edu>, <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Why is a hash join being used?
Date: 2012-06-22 15:59:44
Message-ID: 4FE4502002000025000489DA@gw.wicourts.gov (view raw or flat)
Thread:
Lists: pgsql-performance
Tim Jacobs <tjacobs2(at)email(dot)unc(dot)edu> wrote:
 
> The nested loop join performs very quickly, whereas the hash join
> is incredibly slow. If I disable the hash join temporarily then a
> nested loop join is used in the second case and is the query runs
> much more quickly. How can I change my configuration to favor the
> nested join in this case? Is this a bad idea?
 
Before anyone can make solid suggestions on what you might want to
change in your configuration, they would need to know more.  Please
read this page:
 
http://wiki.postgresql.org/wiki/SlowQueryQuestions
 
... and repost with your PostgreSQL version, your configuration
overrides, a description of your hardware, and EXPLAIN ANALYZE
output from the query (rather than just EXPLAIN output).
 
You might not be modeling your costs correctly, you might not be
allocating resources well, you might be on an old version without an
optimizer as smart as more recent versions, your statistics might be
out of date, or you might be running into an optimizer weakness of
some sort.
 
-Kevin

In response to

pgsql-performance by date

Next:From: David KerrDate: 2012-06-22 17:07:56
Subject: Drop statistics?
Previous:From: Maxim BogukDate: 2012-06-22 07:02:32
Subject: Performance of a large array access by position (tested version 9.1.3)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group