bad performances using hashjoin

Hi all,
I'm stuck in a select that use the hash join where should not:
6 seconds vs 0.3 ms !!

If you need other info in order to improve the planner,
let me know.

Regards
Gaetano Mendola

empdb=# explain analyze SELECT id_sat_request
empdb-# FROM sat_request sr,
empdb-# v_sc_packages vs
empdb-# WHERE ----- JOIN ----
empdb-# sr.id_package = vs.id_package AND
empdb-# ---------------
empdb-# id_user = 29416 AND
empdb-# id_url = 329268 AND
empdb-# vs.estimated_start > now() AND
empdb-# id_sat_request_status = sp_lookup_id('sat_request_status', 'Scheduled');
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=272.95..276.61 rows=1 width=4) (actual time=6323.107..6323.107 rows=0 loops=1)
Hash Cond: ("outer".id_package = "inner".id_package)
-> Subquery Scan vs (cost=269.91..272.10 rows=292 width=4) (actual time=6316.534..6317.398 rows=407 loops=1)
-> Sort (cost=269.91..270.64 rows=292 width=263) (actual time=6316.526..6316.789 rows=407 loops=1)
Sort Key: vs.estimated_start
-> Hash Join (cost=227.58..257.95 rows=292 width=263) (actual time=6302.480..6313.982 rows=407 loops=1)
Hash Cond: ("outer".id_package = "inner".id_package)
-> Subquery Scan vpk (cost=141.82..150.04 rows=1097 width=218) (actual time=6106.020..6113.038 rows=1104 loops=1)
-> Sort (cost=141.82..144.56 rows=1097 width=162) (actual time=6106.006..6106.745 rows=1104 loops=1)
Sort Key: p.id_publisher, p.name
-> Hash Left Join (cost=15.54..86.42 rows=1097 width=162) (actual time=2.978..6087.608 rows=1104 loops=1)
Hash Cond: ("outer".id_package = "inner".id_package)
-> Seq Scan on packages p (cost=0.00..53.48 rows=1097 width=146) (actual time=0.011..7.978 rows=1104 loops=1)
-> Hash (cost=13.69..13.69 rows=738 width=20) (actual time=2.061..2.061 rows=0 loops=1)
-> Seq Scan on package_security ps (cost=0.00..13.69 rows=738 width=20) (actual time=0.027..1.289 rows=747 loops=1)
-> Hash (cost=85.63..85.63 rows=54 width=49) (actual time=196.022..196.022 rows=0 loops=1)
-> Merge Join (cost=82.83..85.63 rows=54 width=49) (actual time=192.898..195.565 rows=407 loops=1)
Merge Cond: ("outer".id_program = "inner".id_program)
-> Subquery Scan vs (cost=72.27..73.97 rows=226 width=16) (actual time=6.867..7.872 rows=407 loops=1)
-> Sort (cost=72.27..72.84 rows=226 width=20) (actual time=6.851..7.114 rows=407 loops=1)
Sort Key: sequences.id_program, sequences.internal_position
-> Seq Scan on sequences (cost=0.00..63.44 rows=226 width=20) (actual time=0.295..3.370 rows=407 loops=1)
Filter: ((estimated_start IS NOT NULL) AND (date_trunc('seconds'::text, estimated_start) > now()))
-> Sort (cost=10.56..10.68 rows=47 width=37) (actual time=186.013..186.296 rows=439 loops=1)
Sort Key: vpr.id_program
-> Subquery Scan vpr (cost=8.90..9.25 rows=47 width=37) (actual time=185.812..185.928 rows=48 loops=1)
-> Sort (cost=8.90..9.02 rows=47 width=61) (actual time=185.806..185.839 rows=48 loops=1)
Sort Key: programs.id_publisher, programs.id_program
-> Seq Scan on programs (cost=0.00..7.60 rows=47 width=61) (actual time=9.592..185.634 rows=48 loops=1)
Filter: (id_program <> 0)
-> Hash (cost=3.04..3.04 rows=1 width=8) (actual time=4.862..4.862 rows=0 loops=1)
-> Index Scan using idx_id_url_sat_request on sat_request sr (cost=0.00..3.04 rows=1 width=8) (actual time=4.851..4.851 rows=0 loops=1)
Index Cond: (id_url = 329268)
Filter: ((id_user = 29416) AND (id_sat_request_status = 1))
Total runtime: 6324.435 ms
(35 rows)

empdb=# set enable_hashjoin = false;
SET
empdb=# explain analyze SELECT id_sat_request
empdb-# FROM sat_request sr,
empdb-# v_sc_packages vs
empdb-# WHERE ----- JOIN ----
empdb-# sr.id_package = vs.id_package AND
empdb-# ---------------
empdb-# id_user = 29416 AND
empdb-# id_url = 329268 AND
empdb-# vs.estimated_start > now() AND
empdb-# id_sat_request_status = sp_lookup_id('sat_request_status', 'Scheduled');
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=393.41..400.83 rows=1 width=4) (actual time=0.080..0.080 rows=0 loops=1)
Join Filter: ("outer".id_package = "inner".id_package)
-> Index Scan using idx_id_url_sat_request on sat_request sr (cost=0.00..3.04 rows=1 width=8) (actual time=0.078..0.078 rows=0 loops=1)
Index Cond: (id_url = 329268)
Filter: ((id_user = 29416) AND (id_sat_request_status = 1))
-> Subquery Scan vs (cost=393.41..395.60 rows=292 width=4) (never executed)
-> Sort (cost=393.41..394.14 rows=292 width=263) (never executed)
Sort Key: vs.estimated_start
-> Merge Join (cost=372.76..381.46 rows=292 width=263) (never executed)
Merge Cond: ("outer".id_package = "inner".id_package)
-> Sort (cost=87.19..87.32 rows=54 width=49) (never executed)
Sort Key: vs.id_package
-> Merge Join (cost=82.83..85.63 rows=54 width=49) (never executed)
Merge Cond: ("outer".id_program = "inner".id_program)
-> Subquery Scan vs (cost=72.27..73.97 rows=226 width=16) (never executed)
-> Sort (cost=72.27..72.84 rows=226 width=20) (never executed)
Sort Key: sequences.id_program, sequences.internal_position
-> Seq Scan on sequences (cost=0.00..63.44 rows=226 width=20) (never executed)
Filter: ((estimated_start IS NOT NULL) AND (date_trunc('seconds'::text, estimated_start) > now()))
-> Sort (cost=10.56..10.68 rows=47 width=37) (never executed)
Sort Key: vpr.id_program
-> Subquery Scan vpr (cost=8.90..9.25 rows=47 width=37) (never executed)
-> Sort (cost=8.90..9.02 rows=47 width=61) (never executed)
Sort Key: programs.id_publisher, programs.id_program
-> Seq Scan on programs (cost=0.00..7.60 rows=47 width=61) (never executed)
Filter: (id_program <> 0)
-> Sort (cost=285.57..288.31 rows=1097 width=218) (never executed)
Sort Key: vpk.id_package
-> Subquery Scan vpk (cost=221.95..230.17 rows=1097 width=218) (never executed)
-> Sort (cost=221.95..224.69 rows=1097 width=162) (never executed)
Sort Key: p.id_publisher, p.name
-> Merge Right Join (cost=108.88..166.55 rows=1097 width=162) (never executed)
Merge Cond: ("outer".id_package = "inner".id_package)
-> Index Scan using package_security_id_package_key on package_security ps (cost=0.00..38.50 rows=738 width=20) (never executed)
-> Sort (cost=108.88..111.62 rows=1097 width=146) (never executed)
Sort Key: p.id_package
-> Seq Scan on packages p (cost=0.00..53.48 rows=1097 width=146) (never executed)
Total runtime: 0.302 ms
(38 rows)