Query Tuning

Would you please help me understand which subquery is taking more time from
the below SQL and explain plan of the query.

SQL text

SELECT
"v_associated_team_component"."role_extension","v_associated_team_component"."team_hier_id_asso","v_associated_team_component"."component_type_id","v_associated_team_component"."component_key_id"
FROM "ifcauth"."v_associated_team_component"
"v_associated_team_component";

VIEW

CREATE OR REPLACE VIEW ifcauth.v_associated_team_component AS
SELECT th.component_team_hier_id AS team_hier_id,
th.component_type_id,
th.component_key_id::bigint AS component_key_id,
th.component_team_hier_id AS team_hier_id_asso,
th.component_type_id AS component_type_id_asso,
th.component_key_id AS component_key_id_asso,
'Self'::text AS association_type,
'Self'::character varying AS association_desc,
CASE
WHEN p.managing_unit_code IS NULL THEN NULL::text
WHEN p.managing_unit_code::text = 'A'::text THEN 'AMC'::text
ELSE 'Non-AMC'::text
END AS role_extension
FROM team.component_team_hierarchy th
LEFT JOIN platformsource.platform p ON th.component_key_id =
p.platform_id::numeric AND (th.component_type_id = ANY (ARRAY[104::numeric,
107::numeric]))
UNION ALL
SELECT ph.project_hierarchy_id AS team_hier_id,
ph.component_type_id,
ph.project_id AS component_key_id,
ath.component_team_hier_id AS team_hier_id_asso,
ath.component_type_id AS component_type_id_asso,
ath.component_key_id AS component_key_id_asso,
'Project-Region'::text AS association_type,
r.region_nme AS association_desc,
NULL::text AS role_extension
FROM team.component_team_hierarchy ath,
reference.region r,
ifcauth.v_project_hierarchy_data ph
WHERE ath.component_type_id = 106::numeric AND r.region_code::text =
to_char(ath.component_key_id) AND ph.region_code::text =
to_char(ath.component_key_id)
UNION ALL
SELECT ih.project_component_team_hier_id AS team_hier_id,
102::numeric(10,0) AS component_type_id,
ih.project_id AS component_key_id,
COALESCE(od.component_team_hier_id, ih.component_team_hier_id) AS
team_hier_id_asso,
COALESCE(od.component_type_id, ih.component_type_id) AS
component_type_id_asso,
ih.platform_id AS component_key_id_asso,
'Project-Platform:Association'::text AS association_type,
'Project-Platform:Association-Inheritance/Override'::character varying
AS association_desc,
CASE
WHEN pl.managing_unit_code::text = 'A'::text THEN 'AMC'::text
ELSE 'Non-AMC'::text
END AS role_extension
FROM ( SELECT ip.project_id,
chp.component_team_hier_id AS project_component_team_hier_id,
ip.platform_id,
ch.component_type_id,
ch.component_team_hier_id
FROM project.is_project ip
JOIN team.component_team_hierarchy chp ON
ip.project_id::numeric = chp.component_key_id AND chp.component_type_id =
102::numeric
JOIN team.component_team_hierarchy ch ON
ip.platform_id::numeric = ch.component_key_id AND ch.component_type_id =
104::numeric
UNION
SELECT pr.project_id,
chp.component_team_hier_id AS project_component_team_hier_id,
c.platform_id,
ch.component_type_id,
ch.component_team_hier_id
FROM product.product pr
JOIN team.component_team_hierarchy chp ON
pr.project_id::numeric = chp.component_key_id AND chp.component_type_id =
102::numeric
JOIN product.product_source_participation psp ON
pr.product_nbr = psp.product_nbr AND psp.snapshot_nbr = 0 AND
psp.active_ind = 'Y'::bpchar
JOIN platformsource.contribution c ON c.contribution_id =
psp.contribution_id AND c.platform_id IS NOT NULL
JOIN team.component_team_hierarchy ch ON
c.platform_id::numeric = ch.component_key_id AND ch.component_type_id =
104::numeric
WHERE pr.snapshot_nbr = 0) ih
JOIN platformsource.platform pl ON pl.platform_id = ih.platform_id
LEFT JOIN ( SELECT
to_number("substring"(a.component_team_hier_id::text, 4, 7)) AS project_id,
a.component_key_id AS platform_id,
107::numeric(10,0) AS component_type_id,
a.component_team_hier_id
FROM team.component_team_hierarchy a

WHERE a.active_ind = 'Y'::bpchar AND a.component_type_id =
107::numeric) od ON ih.project_id::numeric = od.project_id AND
ih.platform_id::numeric = od.platform_id;

explain plan

QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------

Subquery Scan on v_associated_team_component (cost=3.44..26570.46
rows=190940 width=64) (actual time=0.129..50798.804 rows=206165 loops=1)

-> Append (cost=3.44..24661.06 rows=190940 width=184) (actual
time=0.128..50748.433 rows=206165 loops=1)

-> Hash Left Join (cost=3.44..6653.80 rows=188724 width=136)
(actual time=0.128..242.968 rows=188908 loops=1)

Hash Cond: (th.component_key_id = (p.platform_id)::numeric)

Join Filter: (th.component_type_id = ANY
('{104,107}'::numeric[]))

Rows Removed by Join Filter: 16

-> Seq Scan on component_team_hierarchy th
(cost=0.00..4762.24 rows=188724 width=19) (actual time=0.019..47.806
rows=188908 loops=1)

-> Hash (cost=2.64..2.64 rows=64 width=10) (actual
time=0.063..0.063 rows=65 loops=1)

Buckets: 1024 Batches: 1 Memory Usage: 11kB

-> Seq Scan on platform p (cost=0.00..2.64 rows=64
width=10) (actual time=0.010..0.032 rows=65 loops=1)

-> Hash Join (cost=7624.30..9591.92 rows=2214 width=134) (actual
time=129.872..47733.251 rows=12646 loops=1)

Hash Cond: ((p_1.region_code)::text = (r.region_code)::text)

-> Nested Loop Left Join (cost=7623.09..9584.52 rows=65
width=29) (actual time=129.758..47645.026 rows=12646 loops=1)

Join Filter: (__unnamed_subquery_0.project_id =
p_1.project_id)

Rows Removed by Join Filter: 247824194

-> Hash Join (cost=5234.06..6487.82 rows=65
width=29) (actual time=76.561..154.499 rows=12646 loops=1)

Hash Cond: ((p_1.region_code)::text =
(ath.component_key_id)::text)

-> Seq Scan on project p_1 (cost=0.00..1128.56
rows=19927 width=10) (actual time=0.018..34.051 rows=20132 loops=1)

Filter: (snapshot_nbr = 0)

Rows Removed by Filter: 1516

-> Hash (cost=5234.05..5234.05 rows=1
width=19) (actual time=76.505..76.505 rows=7 loops=1)

Buckets: 1024 Batches: 1 Memory Usage:
9kB

-> Seq Scan on component_team_hierarchy
ath (cost=0.00..5234.05 rows=1 width=19) (actual time=0.014..76.483 rows=7
loops=1

)

Filter: (component_type_id =
'106'::numeric)

Rows Removed by Filter: 188901

-> Materialize (cost=2389.03..3004.31 rows=95
width=8) (actual time=0.004..1.289 rows=19598 loops=12646)

-> Subquery Scan on __unnamed_subquery_0
(cost=2389.03..3003.83 rows=95 width=8) (actual time=49.281..124.294
rows=19598 loops=1

)

Filter: (__unnamed_subquery_0.rn = 1)

-> WindowAgg (cost=2389.03..2767.37
rows=18917 width=48) (actual time=49.275..96.747 rows=19598 loops=1)

-> Sort (cost=2389.03..2436.32
rows=18917 width=16) (actual time=49.238..87.108 rows=19598 loops=1)

Sort Key:
project_status_history.project_id, project_status_history.status_start_date

Sort Method: quicksort
Memory: 1687kB

-> Seq Scan on
project_status_history (cost=0.00..1045.22 rows=18917 width=16) (actual
time=0.025..31.698 rows

=19598 loops=1)

Filter:
((status_end_date IS NULL) AND (snapshot_nbr = 0))

Rows Removed by Filter:
12579

-> Hash (cost=1.09..1.09 rows=9 width=21) (actual
time=0.035..0.035 rows=9 loops=1)

Buckets: 1024 Batches: 1 Memory Usage: 9kB

-> Seq Scan on region r (cost=0.00..1.09 rows=9
width=21) (actual time=0.010..0.012 rows=9 loops=1)

-> Subquery Scan on "*SELECT* 3" (cost=6488.52..6505.97 rows=2
width=184) (actual time=120.270..2744.493 rows=4611 loops=1)

-> Nested Loop Left Join (cost=6488.52..6505.95 rows=2
width=160) (actual time=120.263..2742.226 rows=4611 loops=1)

-> Hash Join (cost=6488.10..6491.00 rows=2 width=50)
(actual time=119.698..122.981 rows=4611 loops=1)

Hash Cond: (pl.platform_id = ih.platform_id)

-> Seq Scan on platform pl (cost=0.00..2.64
rows=64 width=10) (actual time=0.015..0.047 rows=65 loops=1)

-> Hash (cost=6488.08..6488.08 rows=2
width=48) (actual time=119.551..119.551 rows=4611 loops=1)

Buckets: 8192 (originally 1024) Batches:
1 (originally 1) Memory Usage: 389kB

-> Subquery Scan on ih
(cost=6488.03..6488.08 rows=2 width=48) (actual time=115.218..118.647
rows=4611 loops=1)

-> Unique (cost=6488.03..6488.06
rows=2 width=48) (actual time=115.216..118.055 rows=4611 loops=1)

-> Sort
(cost=6488.03..6488.03 rows=2 width=48) (actual time=115.216..115.984
rows=9044 loops=1)

Sort Key: ip.project_id,
chp.component_team_hier_id, ip.platform_id, ch.component_type_id,
ch.component_te

am_hier_id

Sort Method: quicksort
Memory: 1091kB

-> Append
(cost=500.53..6488.02 rows=2 width=48) (actual time=4.992..109.490
rows=9044 loops=1)

-> Nested Loop
(cost=500.53..5845.37 rows=1 width=37) (actual time=4.991..48.698 rows=3714
loops=1

)

-> Hash
Join (cost=500.11..5842.00 rows=2 width=29) (actual time=4.948..35.412
rows=3797 loo

ps=1)

Hash
Cond: (ch.component_key_id = (ip.platform_id)::numeric)

->
Seq Scan on component_team_hierarchy ch (cost=0.00..5234.05 rows=38
width=19) (actu

al time=0.559..29.770 rows=22 loops=1)

Filter: (component_type_id = '104'::numeric)

Rows Removed by Filter: 188886

->
Hash (cost=358.38..358.38 rows=11338 width=16) (actual time=4.333..4.333
rows=4295

loops=1)

Buckets: 16384 Batches: 1 Memory Usage: 330kB

-> Seq Scan on is_project ip (cost=0.00..358.38 rows=11338 width=16)
(actual tim

e=0.017..2.979 rows=11479 loops=1)

-> Index
Scan using idx_component_team_key on component_team_hierarchy chp
(cost=0.42..1.68

rows=1 width=14) (actual time=0.003..0.003 rows=1 loops=3797)

Index
Cond: ((component_key_id = (ip.project_id)::numeric) AND (component_type_id
= '102

'::numeric))

-> Nested Loop
(cost=1.42..642.62 rows=1 width=37) (actual time=0.211..59.920 rows=5330
loops=1)

-> Nested
Loop (cost=0.99..637.08 rows=3 width=29) (actual time=0.197..40.469
rows=5347 loop

s=1)

->
Nested Loop (cost=0.71..636.10 rows=2 width=29) (actual time=0.125..24.609
rows=534

7 loops=1)

-> Nested Loop
(cost=0.42..409.05 rows=1 width=29) (actual time=0.079..0.447 row

s=37 loops=1)

-> Seq Scan on contribution c (cost=0.00..2.66 rows=55 width=16) (actual t

ime=0.036..0.077 rows=57 loops=1)

Filter: (platform_id IS NOT NULL)

Rows Removed by Filter: 11

-> Index Scan using idx_component_team_key on component_team_hierarchy ch_1

(cost=0.42..7.38 rows=1 width=19) (actual time=0.005..0.005 rows=1
loops=57)

Index Cond: ((component_key_id = (c.platform_id)::numeric) AND (compon

ent_type_id = '104'::numeric))

-> Index Scan using
pk_product_source_partcptn on product_source_participation ps

p (cost=0.29..224.92 rows=214 width=16) (actual time=0.071..0.637 rows=145
loops=37)

Index Cond: ((snapshot_nbr =
0) AND (contribution_id = c.contribution_id))

Filter: (active_ind = 'Y'::bpchar)

Rows Removed
by Filter: 9

->
Index Scan using pk_product on product pr (cost=0.29..0.48 rows=1
width=16) (actual

time=0.002..0.003 rows=1 loops=5347)

Index Cond: ((product_nbr = psp.product_nbr) AND (snapshot_nbr = 0))

-> Index
Scan using idx_component_team_key on component_team_hierarchy chp_1
(cost=0.42..1.8

4 rows=1 width=14) (actual time=0.003..0.003 rows=1 loops=5347)

Index
Cond: ((component_key_id = (pr.project_id)::numeric) AND (component_type_id
= '102

'::numeric))

-> Index Scan using idx_component_team_key on
component_team_hierarchy a (cost=0.42..7.46 rows=1 width=30) (actual
time=0.519..0.566 r

ows=0 loops=4611)

Index Cond: (((ih.platform_id)::numeric =
component_key_id) AND (component_type_id = '107'::numeric))

Filter: ((active_ind = 'Y'::bpchar) AND
((ih.project_id)::numeric =
to_number("substring"((component_team_hier_id)::text, 4, 7))))

Rows Removed by Filter: 382

Thanks,

Naveen.