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 <nsankineni(at)gmail(dot)com>
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.
>
>
>