Re: Query Tuning

Hi Naveen, Have you heard of https://explain.depesz.com/ ? It will give you
a great cost and time breakdown along with an in-depth explanation of each
of these operations in a readable format. Just copy and paste the query
plan into it.

On Sat, Sep 28, 2019 at 7:33 PM Naveen Sankineni <[email protected]>
wrote:

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