Re: Slow running query

" cast " is affecting your execution time.

On Wed, Dec 11, 2019 at 12:40 PM Shrikant Bhende <
shrikantpostgresql(at)gmail(dot)com> wrote:

> Hello experts,
>
> Below is the query which is running very slow, can anyone suggest any
> improvement for the same to make it faster.
> QUERY
> =============
>
> Explain analyze
>
> SELECT
>
> coalesce(g.group_id,0::INT) as group_id,
> coalesce(g.group_name,'unknown') as group_name, coalesce(g.asset_id,0) as
> asset_id, coalesce(g.asset_name,'unknown') as asset_name,
>
> coalesce(g.asset_cost,0::INT) as asset_cost,
>
>
> coalesce(bb.num_apps_in_asset,0::INT) as
> asset_number_of_applications,
>
> coalesce(g.asset_num_nodes,0::INT) as
> asset_number_of_nodes,
>
> coalesce(g.group_cost,0::REAL) as group_cost,
>
> cast(coalesce(bb.num_apps_in_group,0::INT) as INT) as
> group_number_of_applications,
>
> cast(coalesce(g.group_num_nodes,0::INT) as INT) as
> group_number_of_nodes,
>
> coalesce(bb.num_apps_in_customer,0::INT) as
> customer_number_of_applications,
>
> g.asset_num_connections_to_other_assets_as_client,
> g.asset_num_connections_to_other_assets_as_server,
>
> g.asset_num_proprietary_apps,
> g.asset_num_specialized_apps,
>
> g.asset_insufficient_instance_type,
> g.asset_num_autoscalable_nodes, g.asset_num_nodes_with_bursty_iops,
> g.asset_num_nodes_with_database,
>
> g.asset_num_nodes_on_t2,
>
>
> g.group_num_connections_to_other_groups_as_client,
> g.group_num_connections_to_other_groups_as_server,
>
> g.group_num_proprietary_apps,
> g.group_num_specialized_apps,
>
> g.group_insufficient_instance_type,
>
> --g.asset_insufficient_instance_type,
>
> g.group_num_autoscalable_nodes,
> g.group_num_nodes_with_has_bursty_iops, g.group_num_nodes_with_database,
>
> g.group_num_nodes_on_t2
>
> FROM
>
> (SELECT
>
> ww.asset_id, ww.asset_name, ww.asset_cost,
> ww.asset_num_nodes,
>
> ww.asset_num_connections_to_other_assets_as_client,
> ww.asset_num_connections_to_other_assets_as_server,
>
> ww.asset_num_proprietary_apps,
> ww.asset_num_specialized_apps,
>
> ww.asset_insufficient_instance_type,
> ww.asset_num_autoscalable_nodes, ww.asset_num_nodes_with_bursty_iops,
> ww.asset_num_nodes_with_database,
>
> ww.asset_num_nodes_on_t2,
>
>
> ww.group_id, ww.group_name, ww.group_cost,
> ww.group_num_nodes,
>
> ww.group_num_connections_to_other_groups_as_client,
> ww.group_num_connections_to_other_groups_as_server,
>
> ww.group_num_proprietary_apps,
> ww.group_num_specialized_apps,
>
>
>
> ww.group_insufficient_instance_type,
> ww.group_num_autoscalable_nodes, ww.group_num_nodes_with_has_bursty_iops,
> ww.group_num_nodes_with_database,
>
> ww.group_num_nodes_on_t2
>
> FROM
>
> (SELECT
>
> w.asset_id, w.asset_name, w.asset_cost,
> w.asset_num_nodes,
>
> w.asset_num_connections_to_other_assets_as_client,
> w.asset_num_connections_to_other_assets_as_server,
>
> w.asset_num_proprietary_apps,
> w.asset_num_specialized_apps,
>
> w.asset_insufficient_instance_type,
> w.asset_num_autoscalable_nodes, w.asset_num_nodes_with_bursty_iops,
> w.asset_num_nodes_with_database,
>
> w.asset_num_nodes_on_t2,
>
>
> w.group_id, w.group_name, w.group_cost,
> w.group_num_nodes,
>
> w.group_num_connections_to_other_groups_as_client,
> w.group_num_connections_to_other_groups_as_server,
>
> w.group_num_proprietary_apps,
> w.group_num_specialized_apps,
>
>
>
> w.group_insufficient_instance_type,
> w.group_num_autoscalable_nodes, w.group_num_nodes_with_has_bursty_iops,
> w.group_num_nodes_with_database,
>
> w.group_num_nodes_on_t2
>
> FROM migrator.get_asset_migration_info2(196715,
> 0) as w
>
> ) as ww
>
> UNION ALL
>
> (SELECT
>
> w.asset_id, w.asset_name, w.asset_cost,
> w.asset_number_of_nodes as asset_num_nodes,
>
> 0::INT as
> asset_num_connections_to_other_assets_as_client, 0::INT as
> asset_num_connections_to_other_assets_as_server,
>
> 0::INT as asset_num_proprietary_apps, 0::INT
> as asset_num_specialized_apps,
>
>
> false as asset_insufficient_memory, 0::INT as
> asset_num_autoscalable_nodes, 0::INT as asset_num_nodes_with_bursty_iops,
> 0::INT as asset_num_nodes_with_database,
>
> 0::INT as asset_num_nodes_on_t2,
>
>
> w.group_id , w.group_name,
>
> sum(w.asset_cost) OVER (PARTITION BY
> w.group_id) as group_cost,
>
>
> sum(w.asset_number_of_nodes) OVER (PARTITION BY
> w.group_id) as group_num_nodes,
>
>
> 0::INT as
> group_num_connections_to_other_groups_as_client, 0::INT
> group_num_connections_to_other_groups_as_server,
>
> 0::INT as group_num_proprietary_apps, 0::INT
> as group_num_specialized_apps,
>
>
>
> false as group_insufficient_memory,
>
> 0::INT as group_num_autoscalable_nodes,
> 0::INT as group_num_nodes_with_has_bursty_iops, 0::INT as
> group_num_nodes_with_database,
>
> 0::INT as group_num_nodes_on_t2
>
> FROM
> migrator.get_migration_info_without_agents(196715, 0) as w
>
> )
>
> ) as g
>
> LEFT JOIN
>
> (
>
> WITH T AS
>
> (SELECT distinct w2.asset_id, w2.group_id,
> w1.display_product_name FROM
>
> (SELECT display_product_name, instance_id FROM
>
> (SELECT display_product_name, instance_id
> FROM server_process_info WHERE customer_id=196715
>
> AND instance_id IN (SELECT n.instance_id
> FROM machine_info as n WHERE customer_id=196715 AND machine_type=0)
>
> AND ignore=false
>
> ) as a
>
> UNION ALL
>
> (SELECT display_product_name, instance_id
> FROM client_process_info WHERE customer_id=196715
>
> AND instance_id IN (SELECT n.instance_id
> FROM machine_info as n WHERE customer_id=196715 AND machine_type=0)
>
> AND ignore=false
>
> )
>
> ) as w1
>
> INNER JOIN
>
> (SELECT n.instance_id, n.asset_id, n.group_id
> FROM machine_info as n WHERE customer_id=196715 AND machine_type=0 ) as w2
>
> ON w1.instance_id=w2.instance_id
>
> )
>
> SELECT x1.asset_id, x1.group_id, cast(x1.num_apps_in_asset
> as INT), cast(x2.num_apps_in_group as INT) , cast(x3.num_apps_in_customer
> as INT)
>
> FROM
>
> (SELECT tt.asset_id, tt.group_id, count(*) as
> num_apps_in_asset FROM T as tt GROUP BY tt.asset_id, tt.group_id) as x1
>
> LEFT JOIN
>
> (SELECT z.group_id, count(*) as num_apps_in_group FROM
>
>
> (SELECT distinct tt.group_id,
> tt.display_product_name FROM T as tt) as z
>
> GROUP BY z.group_id
>
> ) as x2
>
> ON x1.group_id=x2.group_id
>
> ,
>
> (SELECT count(*) as num_apps_in_customer FROM
>
> (SELECT distinct tt.display_product_name FROM T
> as tt) as z
>
> ) as x3
>
>
>
> ) as bb
>
> ON g.asset_id = bb.asset_id;
>
> EXPLAIN ANALYZE OF THE
> QIUERY
>
>
> ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> Hash Left Join (cost=5412665.87..5463153.23 rows=9668 width=166) (actual
> time=33924.184..33924.267 rows=26 loops=1)
> Hash Cond: ("*SELECT* 1".asset_id = bb.asset_id)
> -> Append (cost=51990.10..102009.81 rows=8407 width=158) (actual
> time=26420.686..26420.759 rows=26 loops=1)
> -> Subquery Scan on "*SELECT* 1" (cost=51990.10..52609.13
> rows=8406 width=96) (actual time=26420.685..26420.711 rows=26 loops=1)
> -> WindowAgg (cost=51990.10..52420.00 rows=8406 width=92)
> (actual time=26420.684..26420.704 rows=26 loops=1)
> -> Merge Left Join (cost=51990.10..52062.74
> rows=8406 width=67) (actual time=26420.609..26420.630 rows=26 loops=1)
> Merge Cond: ((f_2.group_id =
> (COALESCE(f.group_id, f_1.group_id))) AND (f_2.asset_id =
> (COALESCE(f.asset_id, f_1.asset_id))))
> -> Sort (cost=51804.86..51825.88 rows=8406
> width=35) (actual time=36.431..36.435 rows=26 loops=1)
> Sort Key: f_2.group_id, f_2.asset_id
> Sort Method: quicksort Memory: 28kB
> -> Subquery Scan on f_2
> (cost=50983.71..51256.91 rows=8406 width=35) (actual time=36.305..36.402
> rows=26 loops=1)
> -> HashAggregate
> (cost=50983.71..51172.85 rows=8406 width=45) (actual time=36.304..36.396
> rows=26 loops=1)
> Group Key: aa.asset_id,
> aa.group_id, aa.asset_name, aa.group_name
> -> Hash Left Join
> (cost=33516.69..50580.53 rows=8488 width=45) (actual time=28.558..33.128
> rows=4845 loops=1)
> Hash Cond:
> ((aa.instance_id_of_vm)::text = (w3.instanceid)::text)
> -> Merge Left Join
> (cost=25603.40..42635.40 rows=8488 width=63) (actual time=15.688..19.452
> rows=4845 loops=1)
> Merge Cond:
> ((aa.instance_id_of_vm)::text = (w.instance_id)::text)
> -> Merge Left
> Join (cost=19959.34..36958.68 rows=8488 width=59) (actual
> time=15.686..18.637 rows=4845 loops=1)
> Merge Cond:
> ((aa.instance_id_of_vm)::text = (w_3.instance_id)::text)
> -> Merge
> Left Join (cost=19958.78..21448.99 rows=8488 width=55) (actual
> time=15.684..17.810 rows=4845 loops=1)
> Merge
> Cond: ((aa.instance_id_of_vm)::text = (w_2.instance_id)::text)
> ->
> Merge Left Join (cost=19958.36..21230.93 rows=8488 width=51) (actual
> time=15.681..17.033 rows=4845 loops=1)
>
> Merge Cond: ((aa.instance_id_of_vm)::text = (w_1.instance_id)::text)
>
> -> Sort (cost=19957.93..19979.15 rows=8488 width=31) (actual
> time=15.676..16.051 rows=4845 loops=1)
>
> Sort Key: aa.instance_id_of_vm
>
> Sort Method: quicksort Memory: 583kB
>
> -> Index Scan using machine_info_customer_machine_type_idx on
> machine_info aa (cost=0.43..19404.04 rows=8488 width=31) (actual
> time=0.131..13.900 rows=4845 loops=1)
>
> Index Cond: ((customer_id = 196715) AND (machine_type = 0))
>
> -> GroupAggregate (cost=0.43..1224.46 rows=487 width=35) (never executed)
>
> Group Key: w_1.instance_id
>
> -> Index Scan using
> vmware_cpu_mapping_details_v2_customer_plan_instance_new_idx on
> vmware_cpu_mapping_details_v2 w_1 (cost=0.43..1212.24 rows=490 width=35)
> (never executed)
>
> Index Cond: ((customer_id = 196715) AND (plan_id = GREATEST(0,
> 1)))
> ->
> GroupAggregate (cost=0.42..195.84 rows=79 width=19) (never executed)
>
> Group Key: w_2.instance_id
>
> -> Index Scan using
> vmware_network_mapping_details_customer_plan_instance_new_idx on
> vmware_network_mapping_details w_2 (cost=0.42..194.66 rows=79 width=19)
> (never executed)
>
> Index Cond: ((customer_id = 196715) AND (plan_id = 0))
> ->
> GroupAggregate (cost=0.56..15426.86 rows=4920 width=18) (never executed)
> Group
> Key: w_3.instance_id
> ->
> Index Scan using
> vmware_storage_mapping_details_customer_plan_instance_new_idx on
> vmware_storage_mapping_details w_3 (cost=0.56..15350.28 rows=5477
> width=18) (never executed)
>
> Index Cond: ((customer_id = 196715) AND (plan_id = 0))
> -> Sort
> (cost=5644.00..5649.74 rows=2297 width=19) (never executed)
> Sort Key:
> w.instance_id
> -> Index
> Scan using vmware_cpu_mapping_details_v2_cust_plan_idx on
> vmware_cpu_mapping_details_v2 w (cost=0.42..5515.76 rows=2297 width=19)
> (never executed)
> Index
> Cond: ((customer_id = 196715) AND (plan_id = 3))
> -> Hash
> (cost=7866.81..7866.81 rows=3719 width=17) (actual time=12.843..12.844
> rows=4845 loops=1)
> Buckets: 8192
> (originally 4096) Batches: 1 (originally 1) Memory Usage: 268kB
> -> Bitmap Heap
> Scan on awsinstances w3 (cost=290.67..7866.81 rows=3719 width=17) (actual
> time=0.958..11.618 rows=4845 loops=1)
> Recheck
> Cond: (customerid = 196715)
> Filter:
> is_visible
> Rows
> Removed by Filter: 71
> Heap
> Blocks: exact=1127
> -> Bitmap
> Index Scan on awsinstances_customer_instance_new_idx (cost=0.00..289.75
> rows=4977 width=0) (actual time=0.799..0.799 rows=4916 loops=1)
> Index
> Cond: (customerid = 196715)
> -> Sort (cost=185.24..187.74 rows=1000
> width=48) (actual time=26384.167..26384.168 rows=27 loops=1)
> Sort Key: (COALESCE(f.group_id,
> f_1.group_id)), (COALESCE(f.asset_id, f_1.asset_id))
> Sort Method: quicksort Memory: 61kB
> -> Merge Full Join (cost=120.16..135.41
> rows=1000 width=48) (actual time=26383.634..26383.732 rows=263 loops=1)
> Merge Cond: ((f.asset_id =
> f_1.asset_id) AND (f.group_id = f_1.group_id))
> -> Sort (cost=60.08..62.58
> rows=1000 width=24) (actual time=22331.520..22331.540 rows=263 loops=1)
> Sort Key: f.asset_id,
> f.group_id
> Sort Method: quicksort
> Memory: 45kB
> -> Function Scan on
> get_num_connections f (cost=0.25..10.25 rows=1000 width=24) (actual
> time=22331.461..22331.479 rows=263 loops=1)
> -> Sort (cost=60.08..62.58
> rows=1000 width=24) (actual time=4052.103..4052.105 rows=26 loops=1)
> Sort Key: f_1.asset_id,
> f_1.group_id
> Sort Method: quicksort
> Memory: 27kB
> -> Function Scan on
> get_num_proprietary f_1 (cost=0.25..10.25 rows=1000 width=24) (actual
> time=4052.081..4052.085 rows=26 loops=1)
> -> WindowAgg (cost=49400.65..49400.67 rows=1 width=158) (actual
> time=0.045..0.045 rows=0 loops=1)
> -> Sort (cost=49400.65..49400.65 rows=1 width=80) (actual
> time=0.044..0.044 rows=0 loops=1)
> Sort Key: w_4.group_id
> Sort Method: quicksort Memory: 25kB
> -> Subquery Scan on w_4 (cost=19716.83..49400.64
> rows=1 width=80) (actual time=0.038..0.038 rows=0 loops=1)
> -> GroupAggregate (cost=19716.83..49400.63
> rows=1 width=2104) (actual time=0.038..0.038 rows=0 loops=1)
> Group Key: '-1'::integer, '-1'::integer,
> 'asset (no agents installed)'::character varying(1024), 'group (no agents
> installed)'::character varying(1024)
> -> Nested Loop (cost=19716.83..49400.60
> rows=1 width=1052) (actual time=0.037..0.037 rows=0 loops=1)
> Join Filter:
> ((aa_1.instanceid)::text = (w3_1.instanceid)::text)
> -> Merge Join
> (cost=19716.41..49396.13 rows=1 width=95) (actual time=0.036..0.036 rows=0
> loops=1)
> Merge Cond:
> ((aa_1.instanceid)::text = (w_7.instance_id)::text)
> -> Merge Join
> (cost=19715.85..33907.76 rows=1 width=77) (actual time=0.035..0.035 rows=0
> loops=1)
> Merge Cond:
> ((aa_1.instanceid)::text = (w_6.instance_id)::text)
> -> Nested Loop
> (cost=19715.43..27523.34 rows=1 width=58) (actual time=0.034..0.034 rows=0
> loops=1)
> Join Filter:
> ((aa_1.instanceid)::text = (w_5.instance_id)::text)
> ->
> GroupAggregate (cost=0.42..195.84 rows=79 width=19) (actual
> time=0.034..0.034 rows=0 loops=1)
> Group Key:
> w_5.instance_id
> -> Index
> Scan using vmware_network_mapping_details_customer_plan_instance_new_idx on
> vmware_network_mapping_details w_5 (cost=0.42..194.66 rows=79 width=19)
> (actual time=0.033..0.033 rows=0 loops=1)
> Index
> Cond: ((customer_id = 196715) AND (plan_id = 0))
> -> Materialize
> (cost=19715.00..27316.07 rows=9 width=39) (never executed)
> -> Bitmap
> Heap Scan on awsinstances aa_1 (cost=19715.00..27316.02 rows=9 width=39)
> (never executed)
>
> Recheck Cond: (customerid = 196715)
>
> Filter: (is_visible AND (group_id IS NOT NULL) AND (NOT (hashed SubPlan
> 1)) AND ((instanceid)::text = (instance_id_of_vm)::text))
> ->
> Bitmap Index Scan on awsinstances_customer_instance_new_idx
> (cost=0.00..289.75 rows=4977 width=0) (never executed)
>
> Index Cond: (customerid = 196715)
>
> SubPlan 1
> ->
> Index Scan using machine_info_customer_machine_type_idx on machine_info ww
> (cost=0.43..19404.04 rows=8488 width=18) (never executed)
>
> Index Cond: ((customer_id = 196715) AND (machine_type = 0))
> -> GroupAggregate
> (cost=0.42..6352.60 rows=2544 width=27) (never executed)
> Group Key:
> w_6.instance_id
> -> Index Scan
> using vmware_cpu_mapping_details_v2_customer_plan_instance_new_idx on
> vmware_cpu_mapping_details_v2 w_6 (cost=0.42..6314.05 rows=2622 width=19)
> (never executed)
> Index Cond:
> ((customer_id = 196715) AND (plan_id = 0))
> -> GroupAggregate
> (cost=0.56..15426.86 rows=4920 width=18) (never executed)
> Group Key:
> w_7.instance_id
> -> Index Scan using
> vmware_storage_mapping_details_customer_plan_instance_new_idx on
> vmware_storage_mapping_details w_7 (cost=0.56..15350.28 rows=5477
> width=18) (never executed)
> Index Cond:
> ((customer_id = 196715) AND (plan_id = 0))
> -> Index Scan using
> awsinstances_customer_instance_new_idx on awsinstances w3_1
> (cost=0.42..4.45 rows=1 width=17) (never executed)
> Index Cond: ((customerid =
> 196715) AND ((instanceid)::text = (w_7.instance_id)::text))
> Filter: is_visible
> -> Hash (cost=5360672.90..5360672.90 rows=230 width=16) (actual
> time=7503.482..7503.482 rows=26 loops=1)
> Buckets: 1024 Batches: 1 Memory Usage: 10kB
> -> Subquery Scan on bb (cost=5360661.34..5360672.90 rows=230
> width=16) (actual time=7503.455..7503.473 rows=26 loops=1)
> -> Nested Loop (cost=5360661.34..5360670.60 rows=230
> width=20) (actual time=7503.454..7503.469 rows=26 loops=1)
> CTE t
> -> Unique (cost=5360449.09..5360472.09 rows=2300
> width=40) (actual time=7164.754..7492.175 rows=5287 loops=1)
> -> Sort (cost=5360449.09..5360454.84
> rows=2300 width=40) (actual time=7164.752..7313.915 rows=1225252 loops=1)
> Sort Key: n.asset_id, n.group_id,
> server_process_info.display_product_name
> Sort Method: quicksort Memory: 130823kB
> -> Merge Join
> (cost=5355035.02..5360320.66 rows=2300 width=40) (actual
> time=5675.150..6409.097 rows=1225252 loops=1)
> Merge Cond:
> ((server_process_info.instance_id)::text = (n.instance_id)::text)
> -> Sort
> (cost=5335077.09..5337687.19 rows=1044041 width=548) (actual
> time=5663.170..5924.136 rows=1225252 loops=1)
> Sort Key:
> server_process_info.instance_id
> Sort Method: quicksort
> Memory: 147080kB
> -> Append
> (cost=19493.37..5220265.22 rows=1044041 width=548) (actual
> time=5.689..3529.484 rows=1225252 loops=1)
> -> Nested Loop
> (cost=19493.37..2337081.67 rows=363250 width=44) (actual
> time=5.689..587.545 rows=96400 loops=1)
> ->
> HashAggregate (cost=19425.26..19510.14 rows=8488 width=31) (actual
> time=5.615..8.193 rows=4845 loops=1)
> Group
> Key: (n_1.instance_id)::text
> -> Index
> Scan using machine_info_customer_machine_type_idx on machine_info n_1
> (cost=0.43..19404.04 rows=8488 width=31) (actual time=0.040..3.648
> rows=4845 loops=1)
>
> Index Cond: ((customer_id = 196715) AND (machine_type = 0))
> -> Bitmap Heap
> Scan on server_process_info (cost=68.11..272.61 rows=43 width=44) (actual
> time=0.025..0.116 rows=20 loops=4845)
> Recheck
> Cond: ((customer_id = 196715) AND ((instance_id)::text =
> (n_1.instance_id)::text))
> Filter:
> (NOT ignore)
> Rows
> Removed by Filter: 29
> Heap
> Blocks: exact=131905
> ->
> Bitmap Index Scan on server_process_info_customer_instance_idx
> (cost=0.00..68.10 rows=51 width=0) (actual time=0.018..0.018 rows=49
> loops=4845)
>
> Index Cond: ((customer_id = 196715) AND ((instance_id)::text =
> (n_1.instance_id)::text))
> -> Subquery Scan on
> "*SELECT* 2" (cost=385123.03..2879551.05 rows=680791 width=44) (actual
> time=666.647..2847.311 rows=1128852 loops=1)
> -> Hash Join
> (cost=385123.03..2872743.14 rows=680791 width=27) (actual
> time=666.644..2725.011 rows=1128852 loops=1)
> Hash
> Cond: ((client_process_info.instance_id)::text = (n_2.instance_id)::text)
> ->
> Bitmap Heap Scan on client_process_info (cost=365506.80..2843766.03
> rows=680791 width=27) (actual time=659.196..2343.370 rows=1199079 loops=1)
>
> Recheck Cond: (customer_id = 196715)
>
> Filter: (NOT ignore)
>
> Heap Blocks: exact=505225
> ->
> Bitmap Index Scan on client_process_info_customer_ign_not_clnt_insts_idx
> (cost=0.00..365336.60 rows=680791 width=0) (actual time=462.713..462.713
> rows=1199079 loops=1)
>
> Index Cond: ((customer_id = 196715) AND (ignore = false))
> -> Hash
> (cost=19510.14..19510.14 rows=8488 width=31) (actual time=7.403..7.403
> rows=4845 loops=1)
>
> Buckets: 16384 Batches: 1 Memory Usage: 332kB
> ->
> HashAggregate (cost=19425.26..19510.14 rows=8488 width=31) (actual
> time=5.415..6.398 rows=4845 loops=1)
>
> Group Key: (n_2.instance_id)::text
>
> -> Index Scan using machine_info_customer_machine_type_idx on
> machine_info n_2 (cost=0.43..19404.04 rows=8488 width=31) (actual
> time=0.029..3.568 rows=4845 loops=1)
>
> Index Cond: ((customer_id = 196715) AND (machine_type = 0))
> -> Sort
> (cost=19957.93..19979.15 rows=8488 width=39) (actual time=11.969..79.175
> rows=1225052 loops=1)
> Sort Key: n.instance_id
> Sort Method: quicksort
> Memory: 571kB
> -> Index Scan using
> machine_info_customer_machine_type_idx on machine_info n
> (cost=0.43..19404.04 rows=8488 width=39) (actual time=0.044..4.775
> rows=4845 loops=1)
> Index Cond:
> ((customer_id = 196715) AND (machine_type = 0))
> -> Aggregate (cost=56.25..56.26 rows=1 width=8)
> (actual time=7497.573..7497.573 rows=1 loops=1)
> -> HashAggregate (cost=51.75..53.75 rows=200
> width=32) (actual time=7496.314..7497.272 rows=4378 loops=1)
> Group Key: tt.display_product_name
> -> CTE Scan on t tt (cost=0.00..46.00
> rows=2300 width=32) (actual time=7164.774..7493.515 rows=5287 loops=1)
> -> Hash Left Join (cost=133.00..138.21 rows=230
> width=20) (actual time=5.875..5.887 rows=26 loops=1)
> Hash Cond: (tt_1.group_id = x2.group_id)
> -> HashAggregate (cost=63.25..65.55 rows=230
> width=16) (actual time=1.684..1.691 rows=26 loops=1)
> Group Key: tt_1.asset_id, tt_1.group_id
> -> CTE Scan on t tt_1 (cost=0.00..46.00
> rows=2300 width=8) (actual time=0.001..0.550 rows=5287 loops=1)
> -> Hash (cost=67.25..67.25 rows=200 width=12)
> (actual time=4.175..4.175 rows=1 loops=1)
> Buckets: 1024 Batches: 1 Memory Usage:
> 9kB
> -> Subquery Scan on x2
> (cost=63.25..67.25 rows=200 width=12) (actual time=4.171..4.172 rows=1
> loops=1)
> -> HashAggregate
> (cost=63.25..65.25 rows=200 width=12) (actual time=4.170..4.171 rows=1
> loops=1)
> Group Key: tt_2.group_id
> -> HashAggregate
> (cost=57.50..59.80 rows=230 width=36) (actual time=2.689..3.473 rows=4378
> loops=1)
> Group Key:
> tt_2.group_id, tt_2.display_product_name
> -> CTE Scan on t tt_2
> (cost=0.00..46.00 rows=2300 width=36) (actual time=0.001..0.498 rows=5287
> loops=1)
> Planning time: 18.362 ms
> Execution time: 33944.679 ms
> (171 rows)
>
>
> Thanks
>

--
*Sunilsuns <sunildeshalhre92(at)gmail(dot)com>*