A query I has spends a long time on Hash Joins (and Hash Left Joins).
I have a few questions:

1. When does Postgres decide to do a Hash Join, over another type of Join?
2. Do Hash Joins normally perform poorly? What can I do to speed them up?
3. What can I do to enable Postgres to use a faster type of join?

If there's a good resource for me to read on this, please let me know.

Thanks!

Search Discussions

  • Kevin Grittner at Jun 2, 2011 at 2:57 pm

    Robert James wrote:

    A query I has spends a long time on Hash Joins (and Hash Left
    Joins).
    To submit a post which gives us enough information to help you speed
    up that query, please read this page:

    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    I have a few questions:

    1. When does Postgres decide to do a Hash Join, over another type
    of Join?
    2. Do Hash Joins normally perform poorly? What can I do to speed
    them up?
    3. What can I do to enable Postgres to use a faster type of join?
    Questions this general can only be answered in a general way, so
    here goes.

    The planner doesn't choose a particular plan type, exactly -- it
    generates a lot of alternative plans,, basically looking at all the
    ways it knows how to retrieve the requested set of data, and
    estimates a cost for each plan based on available resources and
    adjustable costing factors. It will choose the plan with the lowest
    estimated cost. There are many situations where a hash join is
    faster than the alternatives. If it's using one where another
    alternative is actually faster, it's not a matter of "enabling a
    faster join type" -- it's a matter of setting your cost factors to
    accurately reflect the real costs on your system.

    You can generally make hash joins faster by increasing work_mem, but
    that tends to cause data to be pushed from cache sooner and can run
    you out of memory entirely, so it must be tuned carefully. And the
    planner does take the size of work_mem and the expected data set
    into consideration when estimating the cost of the hash join.

    -Kevin
  • Claudio Freire at Jun 2, 2011 at 6:56 pm

    On Thu, Jun 2, 2011 at 4:57 PM, Kevin Grittner wrote:
    And the
    planner does take the size of work_mem and the expected data set
    into consideration when estimating the cost of the hash join.
    And shouldn't it?

    In a gross mode, when hash joins go to disk, they perform very poorly.
    Maybe the planner should take that into account.
  • Robert Haas at Jun 3, 2011 at 3:55 am

    On Thu, Jun 2, 2011 at 2:56 PM, Claudio Freire wrote:
    On Thu, Jun 2, 2011 at 4:57 PM, Kevin Grittner
    wrote:
    And the
    planner does take the size of work_mem and the expected data set
    into consideration when estimating the cost of the hash join.
    And shouldn't it?

    In a gross mode, when hash joins go to disk, they perform very poorly.
    Maybe the planner should take that into account.
    It does.

    --
    Robert Haas
    EnterpriseDB: http://www.enterprisedb.com
    The Enterprise PostgreSQL Company
  • Robert Haas at Jun 2, 2011 at 6:50 pm

    On Wed, Jun 1, 2011 at 8:10 PM, Robert James wrote:
    A query I has spends a long time on Hash Joins (and Hash Left Joins).
    I have a few questions:

    1. When does Postgres decide to do a Hash Join, over another type of Join?
    2. Do Hash Joins normally perform poorly?  What can I do to speed them up?
    3. What can I do to enable Postgres to use a faster type of join?
    IME, hash joins usually are much faster than any other type. There's
    not enough information in your email to speculate as to what might be
    going wrong in your particular case, though.

    --
    Robert Haas
    EnterpriseDB: http://www.enterprisedb.com
    The Enterprise PostgreSQL Company

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-performance @
categoriespostgresql
postedJun 2, '11 at 12:10a
activeJun 3, '11 at 3:55a
posts5
users4
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase