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;
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
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)
