title: Why is Postgres doing a Hash in this query?
body: <p>I have two tables: <code>A</code> and <code>P</code>. I want to get information out of all rows in <code>A</code> whose id is in a temporary table I created, <code>tmp_ids</code>. However, there is additional information about <code>A</code> in the <code>P</code> table, <code>foo</code>, and I want to get this info as well. I have the following query:</p>
  
  <pre><code>SELECT A.H_id AS hid,
         A.id AS aid,
         P.foo, A.pos, A.size
  FROM tmp_ids, P, A
  WHERE tmp_ids.id = A.H_id
    AND P.id = A.P_id
  </code></pre>
  
  <p>I noticed it going slowly, and when I asked Postgres to explain, I noticed that it combines <code>tmp_ids</code> with an index on <code>A</code> I created for <code>H_id</code> with a nested loop.  However, it hashes all of <code>P</code> before doing a Hash join with the result of the first merge. <code>P</code> is quite large and I think this is what's taking all the time. Why would it create a hash there? <code>P.id</code> is <code>P</code>'s primary key, and <code>A.P_id</code> has an index of its own. </p>
  
  <p>UPDATE: All the datatypes are INTEGER, except <code>A.size</code> which is a DOUBLE PRECISION and <code>P.foo</code> which is VARCHAR. I'm using PostgreSQL version 8.4.</p>
  
  <p>Here is the explain: <a href="http://explain.depesz.com/s/WBo" rel="nofollow noreferrer">http://explain.depesz.com/s/WBo</a> .</p>