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
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.
