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)
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
