bad performances using hashjoin - Mailing list pgsql-performance

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=1104loops=1) 
                           ->  Sort  (cost=141.82..144.56 rows=1097 width=162) (actual time=6106.006..6106.745
rows=1104loops=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.608rows=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.978rows=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)
(actualtime=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=407loops=1) 
                                       ->  Sort  (cost=72.27..72.84 rows=226 width=20) (actual time=6.851..7.114
rows=407loops=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.370rows=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.928rows=48 loops=1) 
                                             ->  Sort  (cost=8.90..9.02 rows=47 width=61) (actual time=185.806..185.839
rows=48loops=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.634rows=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.851rows=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.078rows=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)
(neverexecuted) 
 Total runtime: 0.302 ms
(38 rows)