Why is Postgres doing a hash in this request?

Question

I have two tables: Aand P. I want to get information from all the rows in Awhich identifier is stored in a temporary table that I created tmp_ids. However, there is more information about Athe table P, fooand I also want to get this information. I have the following query:

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

I noticed that this was slow, and when I asked Postgres to explain, I noticed that it combines tmp_idswith the index Athat I created for H_id, with a nested loop. However, it hashes everything Pbefore making a hash join with the result of the first merge. Pquite large, and I think this is what takes all the time. Why would this create a hash? P.idis a Pprimary key, and A.P_idhas its own index.

UPDATE: all data types are INTEGER except A.size, which is DOUBLE PRECISION and P.foowhich is VARCHAR. I am using PostgreSQL version 8.4.

Here is an explanation: http://explain.depesz.com/s/WBo .

Answer 1

, , 2100 .

Answer 2

- , random_page_cost seq_page_cost.

set enable_hashjoin = false;

Answer 3

, , , "A.H_id = tmp_ids.id" , - , . , 21 " idx_A_handid A", 3. , 7, .

, , 2100 , -, .

If this were correctly known, there would have been only 300 probes; this could have done something else related only to a subset of the data. You cannot expect to get good plans from joining temporary tables due to their lack of statistics. This may be the case when he needs to push the correct behavior by disabling enable_hashjoin before executing the request.