Hi all,
running a 7.4.5 engine, I'm facing this bad plan:
empdb=# explain analyze SELECT
name,url,descr,request_status,url_status,size_mb,estimated_start,request_time_stamp
empdb-# FROM v_sc_user_request
empdb-# WHERE
empdb-# login = 'babinow1'
empdb-# LIMIT 10 ;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=1716.38..1716.39 rows=1 width=232) (actual
time=52847.239..52847.322 rows=10 loops=1)
-> Subquery Scan v_sc_user_request (cost=1716.38..1716.39 rows=1
width=232) (actual time=52847.234..52847.301 rows=10 loops=1)
-> Sort (cost=1716.38..1716.39 rows=1 width=201) (actual
time=52847.219..52847.227 rows=10 loops=1)
Sort Key: sr.id_sat_request
-> Nested Loop Left Join (cost=1478.82..1716.37 rows=1
width=201) (actual time=3254.483..52847.064 rows=31 loops=1)
Join Filter: ("outer".id_package = "inner".id_package)
-> Nested Loop (cost=493.09..691.55 rows=1 width=193)
(actual time=347.665..940.582 rows=31 loops=1)
-> Nested Loop (cost=493.09..688.49 rows=1
width=40) (actual time=331.446..505.628 rows=31 loops=1)
Join Filter: ("inner".id_user =
"outer".id_user)
-> Index Scan using user_login_login_key on
user_login ul (cost=0.00..4.00 rows=2 width=16) (actual time=12.065..12.071
rows=1 loops=1)
Index Cond: ((login)::text =
'babinow1'::text)
-> Materialize (cost=493.09..531.37
rows=7656 width=28) (actual time=167.654..481.813 rows=8363 loops=1)
-> Seq Scan on sat_request sr
(cost=0.00..493.09 rows=7656 width=28) (actual time=167.644..467.344 rows=8363
loops=1)
Filter: (request_time > (now() -
'1 mon'::interval))
-> Index Scan using url_pkey on url u
(cost=0.00..3.05 rows=1 width=161) (actual time=13.994..14.000 rows=1 loops=31)
Index Cond: ("outer".id_url = u.id_url)
-> Subquery Scan vsp (cost=985.73..1016.53 rows=1103
width=12) (actual time=25.328..1668.754 rows=493 loops=31)
-> Merge Join (cost=985.73..1011.01 rows=1103
width=130) (actual time=25.321..1666.666 rows=493 loops=31)
Merge Cond: ("outer".id_program =
"inner".id_program)
-> Sort (cost=20.74..20.97 rows=93 width=19)
(actual time=0.385..0.431 rows=47 loops=31)
Sort Key: programs.id_program
-> Seq Scan on programs
(cost=0.00..17.70 rows=93 width=19) (actual time=0.022..11.709 rows=48 loops=1)
Filter: (id_program <> 0)
-> Sort (cost=964.99..967.75 rows=1102
width=115) (actual time=14.592..15.218 rows=493 loops=31)
Sort Key: sequences.id_program
-> Merge Join (cost=696.16..909.31
rows=1102 width=115) (actual time=79.717..451.495 rows=493 loops=1)
Merge Cond: ("outer".id_package =
"inner".id_package)
-> Merge Left Join
(cost=0.00..186.59 rows=1229 width=103) (actual time=0.101..366.854 rows=1247
loops=1)
Merge Cond:
("outer".id_package = "inner".id_package)
-> Index Scan using
packages_pkey on packages p (cost=0.00..131.04 rows=1229 width=103) (actual
time=0.048..163.503 rows=1247 loops=1)
-> Index Scan using
package_security_id_package_key on package_security ps (cost=0.00..46.83
rows=855 width=4) (actual time=0.022..178.599 rows=879 loops=1)
-> Sort (cost=696.16..705.69
rows=3812 width=16) (actual time=79.582..79.968 rows=493 loops=1)
Sort Key:
sequences.id_package
-> Seq Scan on sequences
(cost=0.00..469.42 rows=3812 width=16) (actual time=0.012..78.863 rows=493
loops=1)
Filter:
(estimated_start IS NOT NULL)
Total runtime: 52878.516 ms
(36 rows)
Disabling the nestloop then the execution time become more affordable:
empdb=# set enable_nestloop = false;
SET
empdb=# explain analyze SELECT
name,url,descr,request_status,url_status,size_mb,estimated_start,request_time_stamp
empdb-# FROM v_sc_user_request
empdb-# WHERE
empdb-# login = 'babinow1'
empdb-# LIMIT 10 ;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=4467.64..4467.65 rows=1 width=232) (actual
time=7091.233..7091.289 rows=10 loops=1)
-> Subquery Scan v_sc_user_request (cost=4467.64..4467.65 rows=1
width=232) (actual time=7091.228..7091.272 rows=10 loops=1)
-> Sort (cost=4467.64..4467.64 rows=1 width=201) (actual
time=7091.216..7091.221 rows=10 loops=1)
Sort Key: sr.id_sat_request
-> Merge Left Join (cost=4462.07..4467.63 rows=1 width=201)
(actual time=6377.732..7091.067 rows=31 loops=1)
Merge Cond: ("outer".id_package = "inner".id_package)
-> Sort (cost=3389.81..3389.81 rows=1 width=193) (actual
time=1338.759..1338.814 rows=31 loops=1)
Sort Key: sr.id_package
-> Merge Join (cost=3285.05..3389.80 rows=1
width=193) (actual time=1318.877..1338.651 rows=31 loops=1)
Merge Cond: ("outer".id_url = "inner".id_url)
-> Sort (cost=1029.26..1029.26 rows=1
width=40) (actual time=703.085..703.113 rows=31 loops=1)
Sort Key: sr.id_url
-> Merge Join (cost=991.00..1029.25
rows=1 width=40) (actual time=702.740..702.984 rows=31 loops=1)
Merge Cond: ("outer".id_user =
"inner".id_user)
-> Sort (cost=986.99..1006.13
rows=7656 width=28) (actual time=648.559..655.302 rows=8041 loops=1)
Sort Key: sr.id_user
-> Seq Scan on sat_request
sr (cost=0.00..493.09 rows=7656 width=28) (actual time=201.968..614.631
rows=8363 loops=1)
Filter: (request_time
> (now() - '1 mon'::interval))
-> Sort (cost=4.01..4.02 rows=2
width=16) (actual time=35.252..35.282 rows=1 loops=1)
Sort Key: ul.id_user
-> Index Scan using
user_login_login_key on user_login ul (cost=0.00..4.00 rows=2 width=16)
(actual time=35.214..35.221 rows=1 loops=1)
Index Cond:
((login)::text = 'babinow1'::text)
-> Sort (cost=2255.79..2308.95 rows=21264
width=161) (actual time=587.664..602.490 rows=21250 loops=1)
Sort Key: u.id_url
-> Seq Scan on url u
(cost=0.00..727.32 rows=21264 width=161) (actual time=0.026..418.586 rows=21264
loops=1)
-> Sort (cost=1072.27..1075.03 rows=1103 width=12)
(actual time=5015.761..5016.092 rows=493 loops=1)
Sort Key: vsp.id_package
-> Subquery Scan vsp (cost=985.73..1016.53
rows=1103 width=12) (actual time=898.876..5014.570 rows=494 loops=1)
-> Merge Join (cost=985.73..1011.01
rows=1103 width=130) (actual time=898.869..5011.954 rows=494 loops=1)
Merge Cond: ("outer".id_program =
"inner".id_program)
-> Sort (cost=20.74..20.97 rows=93
width=19) (actual time=29.669..29.708 rows=47 loops=1)
Sort Key: programs.id_program
-> Seq Scan on programs
(cost=0.00..17.70 rows=93 width=19) (actual time=0.035..29.525 rows=48 loops=1)
Filter: (id_program <> 0)
-> Sort (cost=964.99..967.75 rows=1102
width=115) (actual time=868.619..869.286 rows=494 loops=1)
Sort Key: sequences.id_program
-> Merge Join
(cost=696.16..909.31 rows=1102 width=115) (actual time=44.820..867.649 rows=494
loops=1)
Merge Cond:
("outer".id_package = "inner".id_package)
-> Merge Left Join
(cost=0.00..186.59 rows=1229 width=103) (actual time=19.563..835.352 rows=1248
loops=1)
Merge Cond:
("outer".id_package = "inner".id_package)
-> Index Scan using
packages_pkey on packages p (cost=0.00..131.04 rows=1229 width=103) (actual
time=12.796..457.520 rows=1248 loops=1)
-> Index Scan using
package_security_id_package_key on package_security ps (cost=0.00..46.83
rows=855 width=4) (actual time=6.703..283.944 rows=879 loops=1)
-> Sort
(cost=696.16..705.69 rows=3812 width=16) (actual time=25.222..25.705 rows=494
loops=1)
Sort Key:
sequences.id_package
-> Seq Scan on
sequences (cost=0.00..469.42 rows=3812 width=16) (actual time=0.017..24.412
rows=494 loops=1)
Filter:
(estimated_start IS NOT NULL)
Total runtime: 7104.946 ms
(47 rows)
May I know wich parameter may I tune in order to avoid to "disable" the nested
loop ?
Regards
Gaetano Mendola
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
