Robert James <srobertjames@xxxxxxxxx> 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
