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)