Hi folks. I have the query below which when run takes approx 3 seconds. However when I add the condition 'and w_ws_id = 10' onto the end changes to 30+ seconds.
Can anyone see why? I've included the explain for the long select.
select w_vin as key, w_vin, v_o_number as vista, v_status,
s_stock_no, s_customer_order,
coalesce(d.r_registration, p.r_registration, w_reg_no) as
reg_no,w_arrival,
w_updated::date,CURRENT_DATE-w_created::date as age, w_model,
w_radio_code, w_key_no, w_inspected, w_damage,
w_walon_repair,w_collect_date, w_despatch_date, w_sheet,
w_plates, w_accessories,
coalesce(p.d_des, d.d_des) as d_des,
coalesce(p.de_des, d.de_des) as de_des,
p.r_id as pdi,
d.r_id as delivery,
o.o_id, o.state
from walon
left outer join request_details p on p.t_id = 'P' and
substring(p.r_chassis from '(.{11}$)') = w_vin
left outer join request_details d on d.t_id = 'D' and
substring(d.r_chassis from '(.{11}$)') = w_vin
left outer join order_details o on
substring(o.o_vin from '(.{11}$)') = w_vin
left outer join stock s on substring(s.s_vin from '(.{11}$)') = w_vin
left outer join vista v on v.v_vin = w_vin
where w_hide = 0.
--
Gary Stainburn
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop Left Join (cost=14430.85..16251.57 rows=2 width=427)
-> Nested Loop Left Join (cost=14430.85..16239.49 rows=2 width=405)
Join Filter: ("substring"(("inner".s_vin)::text, '(.{11}$)'::text) =
("outer".w_vin)::text)
-> Nested Loop Left Join (cost=13060.02..14496.72 rows=2 width=394)
Join Filter: ("substring"(("inner".o_vin)::text,
'(.{11}$)'::text) = ("outer".w_vin)::text)
-> Nested Loop Left Join (cost=6635.38..7785.17 rows=2
width=352)
Join Filter: ("substring"(("inner".r_chassis)::text,
'(.{11}$)'::text) = ("outer".w_vin)::text)
-> Nested Loop Left Join (cost=3617.97..4441.81 rows=2
width=246)
Join Filter: ("substring"(("inner".r_chassis)::text,
'(.{11}$)'::text) = ("outer".w_vin)::text)
-> Index Scan using walon_hide_index on walon
(cost=0.00..85.04 rows=2 width=140)
Index Cond: (w_hide = 0)
Filter: (w_ws_id = 10)
-> Materialize (cost=3617.97..3861.73 rows=8376
width=127)
-> Subquery Scan p (cost=2372.95..3449.59
rows=8376 width=127)
-> Merge Join (cost=2372.95..3365.83
rows=8376 width=196)
Merge Cond: ("outer".r_id =
"inner".r_id)
-> Merge Left Join
(cost=208.05..954.29 rows=14895 width=20)
Merge Cond: ("outer".r_id =
"inner".co_r_id)
-> Merge Left Join
(cost=105.13..810.93 rows=14895 width=12)
Merge Cond:
("outer".r_id = "inner".co_r_id)
-> Index Scan using
requests_pkey on requests r (cost=0.00..653.03 rows=14895 width=4)
-> Sort
(cost=105.13..107.72 rows=1036 width=12)
Sort Key:
co.co_r_id
-> Subquery
Scan co (cost=29.93..53.24 rows=1036 width=12)
->
HashAggregate (cost=29.93..42.88 rows=1036 width=4)
->
Seq Scan on comments (cost=0.00..24.62 rows=1062 width=4)
-> Sort
(cost=102.93..103.46 rows=213 width=12)
Sort Key: cor.co_r_id
-> Subquery Scan cor
(cost=89.90..94.69 rows=213 width=12)
->
HashAggregate (cost=89.90..92.56 rows=213 width=4)
-> Hash
Join (cost=56.77..88.83 rows=213 width=4)
Hash
Cond: ("outer".co_id = "inner".co_id)
->
Seq Scan on comments (cost=0.00..24.62 rows=1062 width=8)
->
Hash (cost=56.24..56.24 rows=213 width=4)
-> Unique (cost=53.04..54.11 rows=213 width=4)
-> Sort (cost=53.04..53.58 rows=213 width=4)
Sort Key: c.co_id
-> Hash Join (cost=10.09..44.81 rows=213 width=4)
Hash Cond: ("outer".co_id = "inner".cor_co_id)
-> Seq Scan on comments c (cost=0.00..24.62 rows=1062
width=4)
-> Hash (cost=9.56..9.56 rows=213 width=4)
-> Seq Scan on co_recipients co (cost=0.00..9.56
rows=213 width=4)
Filter: (cor_viewed IS NULL)
-> Sort (cost=2164.90..2185.84
rows=8376 width=180)
Sort Key: r.r_id
-> Hash Join
(cost=23.23..1178.62 rows=8376 width=180)
Hash Cond:
("outer".r_c_id = "inner".c_id)
-> Hash Join
(cost=7.53..1037.28 rows=8376 width=168)
Hash Cond:
("outer".r_d_id = "inner".d_id)
-> Hash Join
(cost=5.46..909.57 rows=8376 width=154)
Hash Cond:
("outer".r_u_id = "inner".u_id)
-> Hash
Join (cost=2.15..780.62 rows=8376 width=125)
Hash
Cond: ("outer".r_de_id = "inner".de_id)
->
Hash Join (cost=1.09..653.92 rows=8376 width=122)
Hash Cond: ("outer".r_s_id = "inner".s_id)
-> Seq Scan on requests r (cost=0.00..527.19 rows=8376 width=111)
Filter: ('P'::bpchar = r_t_id)
-> Hash (cost=1.07..1.07 rows=7 width=15)
-> Seq Scan on request_states s (cost=0.00..1.07 rows=7 width=15)
->
Hash (cost=1.05..1.05 rows=5 width=11)
-> Seq Scan on departments de (cost=0.00..1.05 rows=5 width=11)
-> Hash
(cost=3.05..3.05 rows=105 width=33)
->
Seq Scan on users u (cost=0.00..3.05 rows=105 width=33)
-> Hash
(cost=2.06..2.06 rows=2 width=27)
-> Nested
Loop (cost=0.00..2.06 rows=2 width=27)
->
Seq Scan on request_types t (cost=0.00..1.02 rows=1 width=15)
Filter: (t_id = 'P'::bpchar)
->
Seq Scan on dealerships d (cost=0.00..1.02 rows=2 width=12)
-> Hash
(cost=13.76..13.76 rows=776 width=16)
-> Seq Scan on
customers c (cost=0.00..13.76 rows=776 width=16)
-> Materialize (cost=3017.42..3082.61 rows=6519
width=127)
-> Subquery Scan d (cost=1999.25..3010.90
rows=6519 width=127)
-> Merge Join (cost=1999.25..2945.71
rows=6519 width=196)
Merge Cond: ("outer".r_id = "inner".r_id)
-> Merge Left Join
(cost=208.05..954.29 rows=14895 width=20)
Merge Cond: ("outer".r_id =
"inner".co_r_id)
-> Merge Left Join
(cost=105.13..810.93 rows=14895 width=12)
Merge Cond: ("outer".r_id =
"inner".co_r_id)
-> Index Scan using
requests_pkey on requests r (cost=0.00..653.03 rows=14895 width=4)
-> Sort
(cost=105.13..107.72 rows=1036 width=12)
Sort Key: co.co_r_id
-> Subquery Scan co
(cost=29.93..53.24 rows=1036 width=12)
->
HashAggregate (cost=29.93..42.88 rows=1036 width=4)
-> Seq
Scan on comments (cost=0.00..24.62 rows=1062 width=4)
-> Sort (cost=102.93..103.46
rows=213 width=12)
Sort Key: cor.co_r_id
-> Subquery Scan cor
(cost=89.90..94.69 rows=213 width=12)
-> HashAggregate
(cost=89.90..92.56 rows=213 width=4)
-> Hash Join
(cost=56.77..88.83 rows=213 width=4)
Hash Cond:
("outer".co_id = "inner".co_id)
-> Seq
Scan on comments (cost=0.00..24.62 rows=1062 width=8)
-> Hash
(cost=56.24..56.24 rows=213 width=4)
->
Unique (cost=53.04..54.11 rows=213 width=4)
-> Sort (cost=53.04..53.58 rows=213 width=4)
Sort Key: c.co_id
-> Hash Join (cost=10.09..44.81 rows=213 width=4)
Hash Cond: ("outer".co_id = "inner".cor_co_id)
-> Seq Scan on comments c (cost=0.00..24.62 rows=1062 width=4)
-> Hash (cost=9.56..9.56 rows=213 width=4)
-> Seq Scan on co_recipients co (cost=0.00..9.56 rows=213
width=4)
Filter: (cor_viewed IS NULL)
-> Sort (cost=1791.20..1807.50
rows=6519 width=180)
Sort Key: r.r_id
-> Hash Join
(cost=23.23..1039.34 rows=6519 width=180)
Hash Cond: ("outer".r_d_id =
"inner".d_id)
-> Hash Join
(cost=21.16..939.49 rows=6519 width=166)
Hash Cond:
("outer".r_u_id = "inner".u_id)
-> Hash Join
(cost=17.85..838.39 rows=6519 width=137)
Hash Cond:
("outer".r_s_id = "inner".s_id)
-> Hash Join
(cost=16.76..739.52 rows=6519 width=126)
Hash Cond:
("outer".r_de_id = "inner".de_id)
-> Hash
Join (cost=15.70..640.67 rows=6519 width=123)
Hash
Cond: ("outer".r_c_id = "inner".c_id)
->
Seq Scan on requests r (cost=0.00..527.19 rows=6519 width=111)
Filter: ('D'::bpchar = r_t_id)
->
Hash (cost=13.76..13.76 rows=776 width=16)
-> Seq Scan on customers c (cost=0.00..13.76 rows=776 width=16)
-> Hash
(cost=1.05..1.05 rows=5 width=11)
->
Seq Scan on departments de (cost=0.00..1.05 rows=5 width=11)
-> Hash
(cost=1.07..1.07 rows=7 width=15)
-> Seq
Scan on request_states s (cost=0.00..1.07 rows=7 width=15)
-> Hash
(cost=3.05..3.05 rows=105 width=33)
-> Seq Scan on
users u (cost=0.00..3.05 rows=105 width=33)
-> Hash (cost=2.06..2.06
rows=2 width=27)
-> Nested Loop
(cost=0.00..2.06 rows=2 width=27)
-> Seq Scan on
request_types t (cost=0.00..1.02 rows=1 width=15)
Filter:
(t_id = 'D'::bpchar)
-> Seq Scan on
dealerships d (cost=0.00..1.02 rows=2 width=12)
-> Materialize (cost=6424.64..6482.02 rows=5738 width=66)
-> Subquery Scan o (cost=5833.50..6418.90 rows=5738
width=66)
-> Hash Join (cost=5833.50..6361.52 rows=5738
width=1191)
Hash Cond: ("outer".o_pt_id = "inner".pt_id)
-> Hash Join (cost=5832.39..6231.31
rows=5738 width=1176)
Hash Cond: ("outer".o_ls_id =
"inner".ls_id)
-> Hash Left Join
(cost=5831.35..6144.20 rows=5738 width=1162)
Hash Cond: (("outer".o_d_id =
"inner".d_id) AND ("outer".o_de_id = "inner".de_id))
-> Hash Left Join
(cost=5830.20..5999.60 rows=5738 width=1153)
Hash Cond: ("outer".o_de_id
= "inner".de_id)
-> Merge Left Join
(cost=5829.14..5912.47 rows=5738 width=1146)
Merge Cond:
("outer".o_id = "inner".ol_o_id)
-> Merge Left Join
(cost=4463.28..4506.28 rows=5738 width=1130)
Merge Cond:
("outer".o_id = "inner".dw_o_id)
-> Sort
(cost=4348.32..4362.67 rows=5738 width=1114)
Sort Key:
o.o_id
-> Hash
Join (cost=59.55..1612.32 rows=5738 width=1114)
Hash
Cond: ("outer".o_d_id = "inner".d_id)
->
Hash Left Join (cost=58.52..1525.22 rows=5738 width=1106)
Hash Cond: ("outer".o_del_comp = "inner".dc_id)
-> Hash Left Join (cost=57.38..1459.15 rows=5738 width=1023)
Hash Cond: ("outer".o_ds_id = "inner".ds_id)
-> Hash Left Join (cost=56.30..1372.01 rows=5738 width=1010)
Hash Cond: ("outer".o_rs_id = "inner".rs_id)
-> Hash Left Join (cost=55.26..1284.90 rows=5738 width=999)
Hash Cond: ("outer".o_tax_state = "inner".ts_id)
-> Hash Left Join (cost=54.22..1197.79 rows=5738 width=988)
Hash Cond: ("outer".o_pdi_state = "inner".pdi_id)
-> Hash Left Join (cost=53.16..1110.66 rows=5738
width=972)
Hash Cond: ("outer".o_sl_id = "inner".sl_id)
-> Hash Left Join (cost=52.11..1023.54
rows=5738 width=962)
Hash Cond: ("outer".o_sman = "inner".u_id)
-> Hash Left Join (cost=48.80..934.53
rows=5738 width=946)
Hash Cond: ("outer".o_u_id =
"inner".u_id)
-> Hash Left Join
(cost=45.49..845.14 rows=5738 width=930)
Hash Cond: ("outer".o_eu_id =
"inner".eu_id)
-> Hash Left Join
(cost=10.61..738.27 rows=5738 width=826)
Hash Cond:
("outer".o_f_id = "inner".f_id)
-> Hash Join
(cost=6.87..648.47 rows=5738 width=741)
Hash Cond:
("outer".o_ss_id = "inner".ss_id)
-> Hash Join
(cost=5.71..561.23 rows=5738 width=715)
Hash Cond:
("outer".o_model = "inner".sm_id)
-> Hash
Join (cost=4.47..473.93 rows=5738 width=706)
Hash
Cond: ("outer".o_p_id = "inner".p_id)
->
Seq Scan on orders o (cost=0.00..383.38 rows=5738 width=602)
->
Hash (cost=4.18..4.18 rows=118 width=104)
-> Seq Scan on partners p (cost=0.00..4.18 rows=118 width=104)
-> Hash
(cost=1.19..1.19 rows=19 width=13)
->
Seq Scan on stock_models sm (cost=0.00..1.19 rows=19 width=13)
-> Hash
(cost=1.13..1.13 rows=13 width=30)
-> Seq Scan
on stock_status ss (cost=0.00..1.13 rows=13 width=30)
-> Hash
(cost=3.39..3.39 rows=139 width=85)
-> Seq Scan on
financiers f (cost=0.00..3.39 rows=139 width=85)
-> Hash (cost=30.90..30.90
rows=1590 width=104)
-> Seq Scan on
end_users eu (cost=0.00..30.90 rows=1590 width=104)
-> Hash (cost=3.05..3.05 rows=105
width=20)
-> Seq Scan on users u
(cost=0.00..3.05 rows=105 width=20)
-> Hash (cost=3.05..3.05 rows=105
width=20)
-> Seq Scan on users sman
(cost=0.00..3.05 rows=105 width=20)
-> Hash (cost=1.04..1.04 rows=4 width=14)
-> Seq Scan on stock_locations sl
(cost=0.00..1.04 rows=4 width=14)
-> Hash (cost=1.05..1.05 rows=5 width=20)
-> Seq Scan on pdi_state pdi (cost=0.00..1.05
rows=5 width=20)
-> Hash (cost=1.03..1.03 rows=3 width=15)
-> Seq Scan on tax_state ts (cost=0.00..1.03 rows=3
width=15)
-> Hash (cost=1.03..1.03 rows=3 width=15)
-> Seq Scan on release_state rs (cost=0.00..1.03 rows=3
width=15)
-> Hash (cost=1.06..1.06 rows=6 width=17)
-> Seq Scan on delivery_state ds (cost=0.00..1.06 rows=6
width=17)
-> Hash (cost=1.12..1.12 rows=12 width=83)
-> Seq Scan on delivery_companies dc (cost=0.00..1.12 rows=12 width=83)
->
Hash (cost=1.02..1.02 rows=2 width=12)
-> Seq Scan on dealerships d (cost=0.00..1.02 rows=2 width=12)
-> Sort
(cost=114.96..117.34 rows=954 width=20)
Sort Key:
osw.dw_o_id
->
Subquery Scan osw (cost=41.51..67.74 rows=954 width=20)
->
HashAggregate (cost=41.51..58.20 rows=954 width=8)
-> Seq Scan on dfa_work (cost=0.00..32.29 rows=1229 width=8)
-> Sort
(cost=1365.86..1370.19 rows=1732 width=20)
Sort Key:
l.ol_o_id
-> Subquery
Scan l (cost=1229.40..1272.70 rows=1732 width=20)
->
HashAggregate (cost=1229.40..1255.38 rows=1732 width=12)
->
Seq Scan on orders_log (cost=0.00..919.37 rows=41337 width=12)
-> Hash (cost=1.05..1.05
rows=5 width=11)
-> Seq Scan on
departments de (cost=0.00..1.05 rows=5 width=11)
-> Hash (cost=1.10..1.10 rows=10
width=17)
-> Seq Scan on dealer_codes
dlc (cost=0.00..1.10 rows=10 width=17)
-> Hash (cost=1.03..1.03 rows=3
width=18)
-> Seq Scan on logbook_status ls
(cost=0.00..1.03 rows=3 width=18)
-> Hash (cost=1.09..1.09 rows=9 width=19)
-> Seq Scan on payment_terms pt
(cost=0.00..1.09 rows=9 width=19)
-> Materialize (cost=1370.83..1445.22 rows=7439 width=31)
-> Seq Scan on stock s (cost=0.00..1363.39 rows=7439 width=31)
-> Index Scan using vista_vin_index on vista v (cost=0.00..6.01 rows=1
width=35)
Index Cond: ((v.v_vin)::text = ("outer".w_vin)::text)
(223 rows)
---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
