Query Tuning

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.