explain.depesz.com

PostgreSQL's explain analyze made readable

Result: aLNx

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.010 8,903.894 ↑ 1.0 45 1

Limit (cost=17,903.03..17,909.33 rows=45 width=3,051) (actual time=8,903.849..8,903.894 rows=45 loops=1)

  • Output: simcard0_.id, q.id, simcard0_.apn_name, simcard0_.apn_uuid, simcard0_.billing_pool_id, simcard0_.billing_status, simcard0_.cancel_reason, simcard0_.originorg_id, simcard0_.deleted, simcard0_.device_id, simcard0_.iccid, simcard0_.imei, simcard0_.imsi, simcard0_.info, simcard0_.logistic_status, simcard0_.market, simcard0_.msisdn, simcard0_.next_ta
2. 0.161 8,903.884 ↑ 42.7 180 1

Unique (cost=17,884.13..18,959.89 rows=7,684 width=3,051) (actual time=8,903.701..8,903.884 rows=180 loops=1)

  • Output: simcard0_.id, q.id, simcard0_.apn_name, simcard0_.apn_uuid, simcard0_.billing_pool_id, simcard0_.billing_status, simcard0_.cancel_reason, simcard0_.originorg_id, simcard0_.deleted, simcard0_.device_id, simcard0_.iccid, simcard0_.imei, simcard0_.imsi, simcard0_.info, simcard0_.logistic_status, simcard0_.market, simcard0_.msisdn, simcard0_.n
3. 12.763 8,903.723 ↑ 42.7 180 1

Sort (cost=17,884.13..17,903.34 rows=7,684 width=3,051) (actual time=8,903.697..8,903.723 rows=180 loops=1)

  • Output: simcard0_.id, q.id, simcard0_.apn_name, simcard0_.apn_uuid, simcard0_.billing_pool_id, simcard0_.billing_status, simcard0_.cancel_reason, simcard0_.originorg_id, simcard0_.deleted, simcard0_.device_id, simcard0_.iccid, simcard0_.imei, simcard0_.imsi, simcard0_.info, simcard0_.logistic_status, simcard0_.market, simcard0_.msisdn, simca
  • Sort Key: simcard0_.id, q.id, simcard0_.apn_name, simcard0_.apn_uuid, simcard0_.billing_pool_id, simcard0_.billing_status, simcard0_.cancel_reason, simcard0_.originorg_id, simcard0_.device_id, simcard0_.iccid, simcard0_.imei, simcard0_.imsi, simcard0_.info, simcard0_.logistic_status, simcard0_.market, simcard0_.msisdn, simcard0_.next_tariff,
  • Sort Method: external merge Disk: 3,984kB
4. 4,770.921 8,890.960 ↓ 1.0 7,766 1

Nested Loop Left Join (cost=10,839.65..11,463.72 rows=7,684 width=3,051) (actual time=916.479..8,890.960 rows=7,766 loops=1)

  • Output: simcard0_.id, q.id, simcard0_.apn_name, simcard0_.apn_uuid, simcard0_.billing_pool_id, simcard0_.billing_status, simcard0_.cancel_reason, simcard0_.originorg_id, simcard0_.deleted, simcard0_.device_id, simcard0_.iccid, simcard0_.imei, simcard0_.imsi, simcard0_.info, simcard0_.logistic_status, simcard0_.market, simcard0_.msisdn,
  • Join Filter: (simcard0_.profile_id = q.id)
  • Rows Removed by Join Filter: 60,310,756
5. 4.059 4.059 ↓ 1.0 7,766 1

Seq Scan on fwc.sim_cards simcard0_ (cost=0.00..501.06 rows=7,684 width=2,839) (actual time=0.012..4.059 rows=7,766 loops=1)

  • Output: simcard0_.id, simcard0_.iccid, simcard0_.msisdn, simcard0_.uuid, simcard0_.pay_start, simcard0_.pay_cancel, simcard0_.pay_performed, simcard0_.info, simcard0_.status_code, simcard0_.operator_id, simcard0_.cancel_reason, simcard0_.market, simcard0_.originorg_id, simcard0_.orgunit_id, simcard0_.tariff_uuid, simcard0_.tariff
  • Filter: (simcard0_.deleted = 0)
  • Rows Removed by Filter: 1
6. 3,132.156 4,115.980 ↓ 7,767.0 7,767 7,766

Materialize (cost=10,839.65..10,847.40 rows=1 width=212) (actual time=0.100..0.530 rows=7,767 loops=7,766)

  • Output: q.id, q.active_account_subscription_id, q.active_priceplan_name, q.active_priceplan_uuid, q.active_subscription_item_type, q.base_account_subscription_item_id, q.base_priceplan_name, q.base_priceplan_uuid, q.base_subscription_item_type
7. 14.520 983.824 ↓ 7,767.0 7,767 1

Subquery Scan on q (cost=10,839.65..10,847.39 rows=1 width=212) (actual time=777.224..983.824 rows=7,767 loops=1)

  • Output: q.id, q.active_account_subscription_id, q.active_priceplan_name, q.active_priceplan_uuid, q.active_subscription_item_type, q.base_account_subscription_item_id, q.base_priceplan_name, q.base_priceplan_uuid, q.base_subscription_item_type
  • Filter: (q.rank = 1)
  • Rows Removed by Filter: 139,752
8. 165.244 969.304 ↓ 857.7 147,519 1

WindowAgg (cost=10,839.65..10,845.24 rows=172 width=244) (actual time=777.221..969.304 rows=147,519 loops=1)

  • Output: sim_card.id, sub.account_subscription_item_id, (asi.c_subscription_item_type)::text, sil.subscription_item_name, sil.uuid, rank() OVER (?), COALESCE(sub.base_account_subscription_item_id, sub.account_subscription_item_id), (COALESCE(basi.c_subscription_item_type, asi.c_subscription_item_type))::text, bsil.subs
9. 133.794 804.060 ↓ 857.7 147,519 1

Sort (cost=10,839.65..10,840.08 rows=172 width=176) (actual time=777.209..804.060 rows=147,519 loops=1)

  • Output: (COALESCE(ptm.prepaid_notify, 0)), (COALESCE(ptm.id, ptmn.id)), sim.sim_id, sim_card.id, sub.account_subscription_item_id, asi.c_subscription_item_type, sil.subscription_item_name, sil.uuid, sub.base_account_subscription_item_id, basi.c_subscription_item_type, bsil.subscription_item_name, bsil.uuid
  • Sort Key: sim.sim_id, (COALESCE(ptm.prepaid_notify, 0)), (COALESCE(ptm.id, ptmn.id)) DESC
  • Sort Method: external merge Disk: 30,680kB
10. 53.708 670.266 ↓ 857.7 147,519 1

Hash Left Join (cost=9,827.79..10,833.27 rows=172 width=176) (actual time=506.537..670.266 rows=147,519 loops=1)

  • Output: COALESCE(ptm.prepaid_notify, 0), COALESCE(ptm.id, ptmn.id), sim.sim_id, sim_card.id, sub.account_subscription_item_id, asi.c_subscription_item_type, sil.subscription_item_name, sil.uuid, sub.base_account_subscription_item_id, basi.c_subscription_item_type, bsil.subscription_item_name, bsil.uuid
  • Hash Cond: (bsi.subscription_item_id = bsil.subscription_item_id)
11. 42.028 615.375 ↓ 857.7 147,519 1

Hash Left Join (cost=5,661.37..6,665.78 rows=172 width=128) (actual time=505.347..615.375 rows=147,519 loops=1)

  • Output: sub.account_subscription_item_id, sub.base_account_subscription_item_id, asi.c_subscription_item_type, basi.c_subscription_item_type, sim.sim_id, sim_card.id, ptm.prepaid_notify, ptm.id, ptmn.id, bsi.subscription_item_id, sil.subscription_item_name, sil.uuid
  • Inner Unique: true
  • Hash Cond: (bbpsi.subscription_item_id = bsi.subscription_item_id)
12. 68.020 573.286 ↓ 857.7 147,519 1

Hash Right Join (cost=5,653.38..6,657.32 rows=172 width=128) (actual time=505.275..573.286 rows=147,519 loops=1)

  • Output: sub.account_subscription_item_id, sub.base_account_subscription_item_id, asi.c_subscription_item_type, basi.c_subscription_item_type, sim.sim_id, sim_card.id, bbpsi.subscription_item_id, ptm.prepaid_notify, ptm.id, ptmn.id, sil.subscription_item_name, sil.uuid
  • Hash Cond: (ptmn.bm_sim_subscription_item_id = sim_sub.sim_subscription_id)
13. 0.049 0.049 ↓ 0.0 0 1

Foreign Scan on bm_mysql._products_telephone_mobilephone_nwtariff ptmn (cost=25.00..1,025.00 rows=1,000 width=16) (actual time=0.049..0.049 rows=0 loops=1)

  • Output: ptmn.id, ptmn.bm_subscription_item_id, ptmn.bm_sim_subscription_item_id, ptmn.pay_start, ptmn.perform, ptmn.info, ptmn.uuid
  • Remote server startup cost: 25
  • Remote query: SELECT `id`, `bm_sim_subscription_item_id` FROM `office_nokia`.`products_telephone_mobilephone_nwtariff`
14. 61.987 505.217 ↓ 857.7 147,519 1

Hash (cost=5,626.23..5,626.23 rows=172 width=128) (actual time=505.217..505.217 rows=147,519 loops=1)

  • Output: sim_sub.sim_subscription_id, sub.account_subscription_item_id, sub.base_account_subscription_item_id, asi.c_subscription_item_type, basi.c_subscription_item_type, sim.sim_id, sim_card.id, bbpsi.subscription_item_id, ptm.prepaid_notify, ptm.id, sil.subscription_item_name, sil.uuid
  • Buckets: 32,768 (originally 1024) Batches: 8 (originally 1) Memory Usage: 3,841kB
15. 68.053 443.230 ↓ 857.7 147,519 1

Hash Right Join (cost=4,622.29..5,626.23 rows=172 width=128) (actual time=91.445..443.230 rows=147,519 loops=1)

  • Output: sim_sub.sim_subscription_id, sub.account_subscription_item_id, sub.base_account_subscription_item_id, asi.c_subscription_item_type, basi.c_subscription_item_type, sim.sim_id, sim_card.id, bbpsi.subscription_item_id, ptm.prepaid_notify, ptm.id, sil.subscription_item_name, sil
  • Hash Cond: (ptm.bm_sim_subscription_item_id = sim_sub.sim_subscription_id)
16. 283.969 283.969 ↓ 148.2 148,210 1

Foreign Scan on bm_mysql._products_telephone_mobilephone ptm (cost=25.00..1,025.00 rows=1,000 width=20) (actual time=0.224..283.969 rows=148,210 loops=1)

  • Output: ptm.id, ptm.bm_subscription_item_id, ptm.bm_sim_subscription_item_id, ptm.pay_start, ptm.perform, ptm.info, ptm.uuid, ptm.prepaid_notify, ptm.customer_id, ptm.contract_pool_id, ptm.product_pool_id, ptm.type
  • Remote server startup cost: 25
  • Remote query: SELECT `id`, `bm_sim_subscription_item_id`, `prepaid_notify` FROM `office_nokia`.`products_telephone_mobilephone`
17. 2.647 91.208 ↓ 45.2 7,776 1

Hash (cost=4,595.14..4,595.14 rows=172 width=116) (actual time=91.208..91.208 rows=7,776 loops=1)

  • Output: sim_sub.sim_subscription_id, sub.account_subscription_item_id, sub.base_account_subscription_item_id, asi.c_subscription_item_type, basi.c_subscription_item_type, sim.sim_id, sim_card.id, bbpsi.subscription_item_id, sil.subscription_item_name, sil.uuid
  • Buckets: 8,192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1,279kB
18. 2.687 88.561 ↓ 45.2 7,776 1

Hash Join (cost=4,204.89..4,595.14 rows=172 width=116) (actual time=1.615..88.561 rows=7,776 loops=1)

  • Output: sim_sub.sim_subscription_id, sub.account_subscription_item_id, sub.base_account_subscription_item_id, asi.c_subscription_item_type, basi.c_subscription_item_type, sim.sim_id, sim_card.id, bbpsi.subscription_item_id, sil.subscription_item_name, sil.uuid
  • Inner Unique: true
  • Hash Cond: (bpsi.subscription_item_id = si.subscription_item_id)
19. 2.894 85.820 ↓ 32.4 7,776 1

Hash Join (cost=4,196.90..4,586.50 rows=240 width=132) (actual time=1.555..85.820 rows=7,776 loops=1)

  • Output: sim_sub.sim_subscription_id, sub.account_subscription_item_id, sub.base_account_subscription_item_id, asi.c_subscription_item_type, basi.c_subscription_item_type, sim.sim_id, sim_card.id, bpsi.subscription_item_id, bbpsi.subscription_item_id, sil.subscripti
  • Hash Cond: (bpsi.subscription_item_id = sil.subscription_item_id)
20. 3.046 81.734 ↓ 11.2 7,776 1

Hash Left Join (cost=30.48..415.08 rows=695 width=60) (actual time=0.357..81.734 rows=7,776 loops=1)

  • Output: sim_sub.sim_subscription_id, sub.account_subscription_item_id, sub.base_account_subscription_item_id, asi.c_subscription_item_type, basi.c_subscription_item_type, sim.sim_id, sim_card.id, bpsi.subscription_item_id, bbpsi.subscription_item_id
  • Hash Cond: (sub.base_account_subscription_item_id = basi.account_subscription_item_id)
21. 2.528 78.420 ↓ 11.2 7,776 1

Merge Join (cost=1.55..383.24 rows=695 width=48) (actual time=0.083..78.420 rows=7,776 loops=1)

  • Output: sim_sub.sim_subscription_id, sub.account_subscription_item_id, sub.base_account_subscription_item_id, asi.c_subscription_item_type, sim.sim_id, sim_card.id, bpsi.subscription_item_id
  • Inner Unique: true
  • Merge Cond: (asi.billing_profile_subscription_item_id = bpsi.billing_profile_subscription_item_id)
22. 3.610 75.853 ↓ 11.2 7,776 1

Nested Loop Left Join (cost=1.41..1,142.04 rows=695 width=48) (actual time=0.071..75.853 rows=7,776 loops=1)

  • Output: sim_sub.sim_subscription_id, sub.account_subscription_item_id, sub.base_account_subscription_item_id, asi.c_subscription_item_type, asi.billing_profile_subscription_item_id, sim.sim_id, sim_card.id
23. 7.219 33.363 ↓ 11.2 7,776 1

Nested Loop (cost=1.13..921.47 rows=695 width=64) (actual time=0.056..33.363 rows=7,776 loops=1)

  • Output: sim_sub.sim_subscription_id, sub.account_subscription_item_id, sub.base_account_subscription_item_id, asi.c_subscription_item_type, asi.billing_profile_subscription_item_id, sim.sim_id, sim.sim_identifier
  • Inner Unique: true
24. 5.900 18.368 ↓ 11.2 7,776 1

Nested Loop (cost=0.71..414.25 rows=695 width=44) (actual time=0.043..18.368 rows=7,776 loops=1)

  • Output: sim_sub.sim_id, sim_sub.sim_subscription_id, sub.account_subscription_item_id, sub.base_account_subscription_item_id, asi.c_subscription_item_type, asi.billing_profile_subscription_item_id
25. 1.766 4.539 ↓ 13.3 7,929 1

Nested Loop (cost=0.42..215.94 rows=598 width=36) (actual time=0.028..4.539 rows=7,929 loops=1)

  • Output: sub.account_subscription_item_id, sub.base_account_subscription_item_id, sub.subscription_id, asi.c_subscription_item_type, asi.billing_profile_subscription_item_id
26. 0.163 0.163 ↓ 24.9 174 1

Index Scan using account_subscription_item_billing_profile_subscription_item_idx on am.account_subscription_item asi (cost=0.14..27.43 rows=7 width=20) (actual time=0.014..0.163 rows=174 loops=1)

  • Output: asi.account_subscription_item_id, asi.account_id, asi.billing_profile_subscription_item_id, asi.is_default_indicator, asi.is_base_priceplan_indicator, asi.status, asi.c_subscription_item_type, asi.c_network_interfac
  • Filter: ((asi.c_subscription_item_type)::text = ANY ('{PRICEPLAN-NETWORKPRICELIST,PRICEPLAN-POSTPAID-FLEX,PRICEPLAN-POSTPAID-INDIVIDUAL,PRICEPLAN-POSTPAID-STATIC,PRICEPLAN-PREPAID-FLEX,PRICEPLAN-PREPAID-INDIVIDUAL,PRICEPLAN
  • Rows Removed by Filter: 16
27. 2.610 2.610 ↑ 1.1 46 174

Index Scan using ix_fk_account_si__subscription on bm.subscription sub (cost=0.28..26.43 rows=50 width=24) (actual time=0.002..0.015 rows=46 loops=174)

  • Output: sub.subscription_id, sub.account_id, sub.account_subscription_item_id, sub.base_account_subscription_item_id, sub.subscription_type, sub.subscription_status, sub.invoice_billing_information, sub.service_assignment_t
  • Index Cond: (sub.account_subscription_item_id = asi.account_subscription_item_id)
28. 7.929 7.929 ↑ 1.0 1 7,929

Index Scan using ix_fk_subscription__sim_subscription on bm.sim_subscription sim_sub (cost=0.29..0.32 rows=1 width=24) (actual time=0.001..0.001 rows=1 loops=7,929)

  • Output: sim_sub.subscription_id, sim_sub.sim_id, sim_sub.sim_subscription_id
  • Index Cond: (sim_sub.subscription_id = sub.subscription_id)
29. 7.776 7.776 ↑ 1.0 1 7,776

Index Scan using pk_sim on rm.sim (cost=0.42..0.73 rows=1 width=28) (actual time=0.001..0.001 rows=1 loops=7,776)

  • Output: sim.sim_id, sim.sim_identifier, sim.sim_profile_type, sim.pin1, sim.puk1, sim.pin2, sim.puk2, sim.batch_file_id, sim.network_provider_id, sim.sim_product_id, sim.account_id, sim.orgunit_id, sim.po_id, sim.bootstrap_profile_pare
  • Index Cond: (sim.sim_id = sim_sub.sim_id)
30. 38.880 38.880 ↑ 1.0 1 7,776

Index Scan using sim_cards_iccid_idx on fwc.sim_cards sim_card (cost=0.28..0.31 rows=1 width=24) (actual time=0.005..0.005 rows=1 loops=7,776)

  • Output: sim_card.id, sim_card.iccid, sim_card.msisdn, sim_card.uuid, sim_card.pay_start, sim_card.pay_cancel, sim_card.pay_performed, sim_card.info, sim_card.status_code, sim_card.operator_id, sim_card.cancel_reason, sim_card.market, sim_car
  • Index Cond: ((sim_card.iccid)::text = (sim.sim_identifier)::text)
31. 0.039 0.039 ↓ 1.1 176 1

Index Scan using pk_billing_profile_subscription_item on am.billing_profile_subscription_item bpsi (cost=0.14..7.92 rows=165 width=16) (actual time=0.008..0.039 rows=176 loops=1)

  • Output: bpsi.billing_profile_subscription_item_id, bpsi.billing_profile_id, bpsi.subscription_item_id, bpsi.subscription_item_custom_name, bpsi.subscription_item_custom_fee_recurring, bpsi.subscription_item_custom_fee_one_off, bpsi.subscription_it
32. 0.025 0.268 ↓ 24.9 174 1

Hash (cost=28.84..28.84 rows=7 width=20) (actual time=0.268..0.268 rows=174 loops=1)

  • Output: basi.c_subscription_item_type, basi.account_subscription_item_id, bbpsi.subscription_item_id
  • Buckets: 1,024 Batches: 1 Memory Usage: 18kB
33. 0.070 0.243 ↓ 24.9 174 1

Hash Right Join (cost=23.30..28.84 rows=7 width=20) (actual time=0.162..0.243 rows=174 loops=1)

  • Output: basi.c_subscription_item_type, basi.account_subscription_item_id, bbpsi.subscription_item_id
  • Hash Cond: (bbpsi.billing_profile_subscription_item_id = basi.billing_profile_subscription_item_id)
34. 0.023 0.023 ↓ 1.1 176 1

Seq Scan on am.billing_profile_subscription_item bbpsi (cost=0.00..4.65 rows=165 width=16) (actual time=0.005..0.023 rows=176 loops=1)

  • Output: bbpsi.billing_profile_subscription_item_id, bbpsi.billing_profile_id, bbpsi.subscription_item_id, bbpsi.subscription_item_custom_name, bbpsi.subscription_item_custom_fee_recurring, bbpsi.subscription_item_custom_fee_one_off, bbpsi.su
35. 0.026 0.150 ↓ 24.9 174 1

Hash (cost=23.21..23.21 rows=7 width=20) (actual time=0.150..0.150 rows=174 loops=1)

  • Output: basi.c_subscription_item_type, basi.account_subscription_item_id, basi.billing_profile_subscription_item_id
  • Buckets: 1,024 Batches: 1 Memory Usage: 18kB
36. 0.124 0.124 ↓ 24.9 174 1

Seq Scan on am.account_subscription_item basi (cost=0.00..23.21 rows=7 width=20) (actual time=0.006..0.124 rows=174 loops=1)

  • Output: basi.c_subscription_item_type, basi.account_subscription_item_id, basi.billing_profile_subscription_item_id
  • Filter: ((basi.c_subscription_item_type)::text = ANY ('{PRICEPLAN-NETWORKPRICELIST,PRICEPLAN-POSTPAID-FLEX,PRICEPLAN-POSTPAID-INDIVIDUAL,PRICEPLAN-POSTPAID-STATIC,PRICEPLAN-PREPAID-FLEX,PRICEPLAN-PREPAID-INDIVIDUAL,PRICEPLAN-PREPAID-ST
  • Rows Removed by Filter: 16
37. 0.030 1.192 ↓ 3.2 177 1

Hash (cost=4,165.73..4,165.73 rows=55 width=72) (actual time=1.192..1.192 rows=177 loops=1)

  • Output: sil.subscription_item_name, sil.uuid, sil.subscription_item_id
  • Buckets: 1,024 Batches: 1 Memory Usage: 28kB
38. 0.027 1.162 ↓ 3.2 177 1

Subquery Scan on sil (cost=4,164.63..4,165.73 rows=55 width=72) (actual time=1.092..1.162 rows=177 loops=1)

  • Output: sil.subscription_item_name, sil.uuid, sil.subscription_item_id
39. 0.143 1.135 ↓ 3.2 177 1

HashAggregate (cost=4,164.63..4,165.18 rows=55 width=144) (actual time=1.092..1.135 rows=177 loops=1)

  • Output: si_1.subscription_item_id, hm.name, ((si_1.subscription_item_type)::text), (CASE WHEN ((hm.status)::text = 'DR'::text) THEN 'DRAFT'::text WHEN ((hm.status)::text = 'AP'::text) THEN 'READY-FOR-APPROVAL'::text WHEN ((hm.status)::text = 'OK':
  • Group Key: si_1.subscription_item_id, hm.name, ((si_1.subscription_item_type)::text), (CASE WHEN ((hm.status)::text = 'DR'::text) THEN 'DRAFT'::text WHEN ((hm.status)::text = 'AP'::text) THEN 'READY-FOR-APPROVAL'::text WHEN ((hm.status)::text = 'O
40. 0.017 0.992 ↓ 3.2 177 1

Append (cost=33.37..4,163.80 rows=55 width=144) (actual time=0.353..0.992 rows=177 loops=1)

41. 0.132 0.769 ↓ 4.6 165 1

Hash Right Join (cost=33.37..1,038.11 rows=36 width=870) (actual time=0.352..0.769 rows=165 loops=1)

  • Output: si_1.subscription_item_id, hm.name, (si_1.subscription_item_type)::text, CASE WHEN ((hm.status)::text = 'DR'::text) THEN 'DRAFT'::text WHEN ((hm.status)::text = 'AP'::text) THEN 'READY-FOR-APPROVAL'::text WHEN ((hm.status)::tex
  • Hash Cond: (hm.id = si_1.detail_id)
42. 0.507 0.507 ↑ 5.6 180 1

Foreign Scan on bm_mysql._hardware_mobilephone hm (cost=25.00..1,025.00 rows=1,000 width=806) (actual time=0.213..0.507 rows=180 loops=1)

  • Output: hm.id, hm.bm_subscription_item_id, hm.name, hm.description, hm.type, hm.rating_type, hm.prepaid_type, hm.customer_id, hm.company_root_id, hm.rm_zone_model_id, hm.wh_cust_family_id, hm.wh_tariff_id, hm.wh_tariff_none, hm.e
  • Remote server startup cost: 25
  • Remote query: SELECT `id`, `name`, `status`, `uuid` FROM `office_nokia`.`hardware_mobilephone`
43. 0.035 0.130 ↓ 20.6 165 1

Hash (cost=8.27..8.27 rows=8 width=20) (actual time=0.130..0.130 rows=165 loops=1)

  • Output: si_1.subscription_item_id, si_1.subscription_item_type, si_1.detail_id
  • Buckets: 1,024 Batches: 1 Memory Usage: 18kB
44. 0.095 0.095 ↓ 20.6 165 1

Seq Scan on bm.subscription_item si_1 (cost=0.00..8.27 rows=8 width=20) (actual time=0.005..0.095 rows=165 loops=1)

  • Output: si_1.subscription_item_id, si_1.subscription_item_type, si_1.detail_id
  • Filter: ((si_1.subscription_item_type)::text = ANY ('{PRICEPLAN-POSTPAID-INDIVIDUAL,PRICEPLAN-PREPAID-INDIVIDUAL,PRICEPLAN-POSTPAID-FLEX,PRICEPLAN-PREPAID-FLEX,PRICEPLAN-POSTPAID-STATIC,PRICEPLAN-PREPAID-STATIC,PRICEPLAN-TO
  • Rows Removed by Filter: 12
45. 0.000 0.037 ↓ 0.0 0 1

Nested Loop Left Join (cost=25.00..1,044.47 rows=5 width=870) (actual time=0.037..0.037 rows=0 loops=1)

  • Output: si_2.subscription_item_id, hm_1.name, (si_2.subscription_item_type)::text, CASE WHEN ((hm_1.status)::text = 'DR'::text) THEN 'DRAFT'::text WHEN ((hm_1.status)::text = 'AP'::text) THEN 'READY-FOR-APPROVAL'::text WHEN ((hm_1.stat
  • Join Filter: (hm_1.id = si_2.detail_id)
46. 0.037 0.037 ↓ 0.0 0 1

Seq Scan on bm.subscription_item si_2 (cost=0.00..6.88 rows=1 width=20) (actual time=0.037..0.037 rows=0 loops=1)

  • Output: si_2.subscription_item_id, si_2.subscription_item_type, si_2.roaming_profile_id, si_2.detail_id, si_2.priceplan_msub_networkpricelist_subscription_item_id, si_2.assigned_to_billing_profile_id, si_2.status, si_2.network_in
  • Filter: ((si_2.subscription_item_type)::text = 'PRICEPLAN-NETWORKPRICELIST'::text)
  • Rows Removed by Filter: 177
47. 0.000 0.000 ↓ 0.0 0

Foreign Scan on bm_mysql._hardware_mobilephone_nwtariff hm_1 (cost=25.00..1,025.00 rows=1,000 width=806) (never executed)

  • Output: hm_1.id, hm_1.bm_subscription_item_id, hm_1.master_id, hm_1.name, hm_1.description, hm_1.customer_id, hm_1.vk_once, hm_1.vk_monthly, hm_1.vk_bail, hm_1.vk_network_access, hm_1.binding, hm_1.notice_period_customer, hm_1.no
  • Remote server startup cost: 25
  • Remote query: SELECT `id`, `name`, `status`, `uuid` FROM `office_nokia`.`hardware_mobilephone_nwtariff`
48. 0.004 0.133 ↓ 1.3 12 1

Subquery Scan on *SELECT* 3 (cost=31.91..1,036.00 rows=9 width=144) (actual time=0.113..0.133 rows=12 loops=1)

  • Output: "*SELECT* 3".subscription_item_id, "*SELECT* 3".name, "*SELECT* 3".subscription_item_type, "*SELECT* 3".status, "*SELECT* 3".detail_id, NULL::character varying
49. 0.018 0.129 ↓ 1.3 12 1

Hash Right Join (cost=31.91..1,035.91 rows=9 width=144) (actual time=0.111..0.129 rows=12 loops=1)

  • Output: si_3.subscription_item_id, v.name, (si_3.subscription_item_type)::text, CASE WHEN ((v.status)::text = 'DR'::text) THEN 'DRAFT'::text WHEN ((v.status)::text = 'AP'::text) THEN 'READY-FOR-APPROVAL'::text WHEN ((v.status)::t
  • Hash Cond: (v.id = si_3.detail_id)
50. 0.061 0.061 ↑ 83.3 12 1

Foreign Scan on bm_mysql._hardware_hardwareservice_catalog v (cost=25.00..1,025.00 rows=1,000 width=52) (actual time=0.053..0.061 rows=12 loops=1)

  • Output: v.id, v.tree_id, v.sort_id, v.name, v.description, v.type, v.vk_base, v.is_active, v.status
  • Remote server startup cost: 25
  • Remote query: SELECT `id`, `name`, `status` FROM `office_nokia`.`hardware_hardwareservice_catalog`
51. 0.003 0.050 ↓ 6.0 12 1

Hash (cost=6.88..6.88 rows=2 width=20) (actual time=0.050..0.050 rows=12 loops=1)

  • Output: si_3.subscription_item_id, si_3.subscription_item_type, si_3.detail_id
  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
52. 0.047 0.047 ↓ 6.0 12 1

Seq Scan on bm.subscription_item si_3 (cost=0.00..6.88 rows=2 width=20) (actual time=0.006..0.047 rows=12 loops=1)

  • Output: si_3.subscription_item_id, si_3.subscription_item_type, si_3.detail_id
  • Filter: ((si_3.subscription_item_id IS NOT NULL) AND ((si_3.subscription_item_type)::text = ANY ('{ADDON-ACCOUNT,ADDON-SIM}'::text[])))
  • Rows Removed by Filter: 165
53. 0.000 0.036 ↓ 0.0 0 1

Subquery Scan on *SELECT* 4 (cost=25.00..1,044.53 rows=5 width=144) (actual time=0.036..0.036 rows=0 loops=1)

  • Output: "*SELECT* 4".subscription_item_id, "*SELECT* 4".info, "*SELECT* 4".subscription_item_type, "*SELECT* 4"."case", "*SELECT* 4".id, NULL::character varying
54. 0.001 0.036 ↓ 0.0 0 1

Nested Loop Left Join (cost=25.00..1,044.48 rows=5 width=144) (actual time=0.036..0.036 rows=0 loops=1)

  • Output: si_4.subscription_item_id, a.info, (si_4.subscription_item_type)::text, CASE WHEN (a.status = 'DR'::text) THEN 'DRAFT'::text WHEN (a.status = 'AP'::text) THEN 'READY-FOR-APPROVAL'::text WHEN (a.status = 'OK'::text) THEN '
  • Join Filter: (a.id = si_4.detail_id)
55. 0.035 0.035 ↓ 0.0 0 1

Seq Scan on bm.subscription_item si_4 (cost=0.00..6.88 rows=1 width=20) (actual time=0.035..0.035 rows=0 loops=1)

  • Output: si_4.subscription_item_id, si_4.subscription_item_type, si_4.roaming_profile_id, si_4.detail_id, si_4.priceplan_msub_networkpricelist_subscription_item_id, si_4.assigned_to_billing_profile_id, si_4.status, si_4.netw
  • Filter: ((si_4.subscription_item_type)::text = 'ADDON-TOPOFF'::text)
  • Rows Removed by Filter: 177
56. 0.000 0.000 ↓ 0.0 0

Foreign Scan on bm_mysql._products_hardwareservice_topoff a (cost=25.00..1,025.00 rows=1,000 width=72) (never executed)

  • Output: a.id, a.customer_id, a.product_vk_once, a.info, a.volume_self_after, a.volume_self_after_unit, a.validity, a.status
  • Remote server startup cost: 25
  • Remote query: SELECT `id`, `info`, `status` FROM `office_nokia`.`products_hardwareservice_topoff`
57. 0.022 0.054 ↑ 1.3 177 1

Hash (cost=5.22..5.22 rows=222 width=8) (actual time=0.053..0.054 rows=177 loops=1)

  • Output: si.subscription_item_id
  • Buckets: 1,024 Batches: 1 Memory Usage: 15kB
58. 0.032 0.032 ↑ 1.3 177 1

Seq Scan on bm.subscription_item si (cost=0.00..5.22 rows=222 width=8) (actual time=0.008..0.032 rows=177 loops=1)

  • Output: si.subscription_item_id
59. 0.022 0.061 ↑ 1.3 177 1

Hash (cost=5.22..5.22 rows=222 width=8) (actual time=0.061..0.061 rows=177 loops=1)

  • Output: bsi.subscription_item_id
  • Buckets: 1,024 Batches: 1 Memory Usage: 15kB
60. 0.039 0.039 ↑ 1.3 177 1

Seq Scan on bm.subscription_item bsi (cost=0.00..5.22 rows=222 width=8) (actual time=0.013..0.039 rows=177 loops=1)

  • Output: bsi.subscription_item_id
61. 0.030 1.183 ↓ 3.2 177 1

Hash (cost=4,165.73..4,165.73 rows=55 width=72) (actual time=1.183..1.183 rows=177 loops=1)

  • Output: bsil.subscription_item_name, bsil.uuid, bsil.subscription_item_id
  • Buckets: 1,024 Batches: 1 Memory Usage: 28kB
62. 0.029 1.153 ↓ 3.2 177 1

Subquery Scan on bsil (cost=4,164.63..4,165.73 rows=55 width=72) (actual time=1.082..1.153 rows=177 loops=1)

  • Output: bsil.subscription_item_name, bsil.uuid, bsil.subscription_item_id
63. 0.147 1.124 ↓ 3.2 177 1

HashAggregate (cost=4,164.63..4,165.18 rows=55 width=144) (actual time=1.081..1.124 rows=177 loops=1)

  • Output: si_5.subscription_item_id, hm_2.name, ((si_5.subscription_item_type)::text), (CASE WHEN ((hm_2.status)::text = 'DR'::text) THEN 'DRAFT'::text WHEN ((hm_2.status)::text = 'AP'::text) THEN 'READY-FOR-APPROVAL'::text WHEN ((hm_2.status)::text = 'OK'::text) THEN 'APPROVED'::text WHEN
  • Group Key: si_5.subscription_item_id, hm_2.name, ((si_5.subscription_item_type)::text), (CASE WHEN ((hm_2.status)::text = 'DR'::text) THEN 'DRAFT'::text WHEN ((hm_2.status)::text = 'AP'::text) THEN 'READY-FOR-APPROVAL'::text WHEN ((hm_2.status)::text = 'OK'::text) THEN 'APPROVED'::text WH
64. 0.017 0.977 ↓ 3.2 177 1

Append (cost=33.37..4,163.80 rows=55 width=144) (actual time=0.325..0.977 rows=177 loops=1)

65. 0.132 0.748 ↓ 4.6 165 1

Hash Right Join (cost=33.37..1,038.11 rows=36 width=870) (actual time=0.324..0.748 rows=165 loops=1)

  • Output: si_5.subscription_item_id, hm_2.name, (si_5.subscription_item_type)::text, CASE WHEN ((hm_2.status)::text = 'DR'::text) THEN 'DRAFT'::text WHEN ((hm_2.status)::text = 'AP'::text) THEN 'READY-FOR-APPROVAL'::text WHEN ((hm_2.status)::text = 'OK'::text) THEN 'APPROVED'::t
  • Hash Cond: (hm_2.id = si_5.detail_id)
66. 0.516 0.516 ↑ 5.6 180 1

Foreign Scan on bm_mysql._hardware_mobilephone hm_2 (cost=25.00..1,025.00 rows=1,000 width=806) (actual time=0.214..0.516 rows=180 loops=1)

  • Output: hm_2.id, hm_2.bm_subscription_item_id, hm_2.name, hm_2.description, hm_2.type, hm_2.rating_type, hm_2.prepaid_type, hm_2.customer_id, hm_2.company_root_id, hm_2.rm_zone_model_id, hm_2.wh_cust_family_id, hm_2.wh_tariff_id, hm_2.wh_tariff_none, hm_2.ek_once, hm_2.e
  • Remote server startup cost: 25
  • Remote query: SELECT `id`, `name`, `status`, `uuid` FROM `office_nokia`.`hardware_mobilephone`
67. 0.025 0.100 ↓ 20.6 165 1

Hash (cost=8.27..8.27 rows=8 width=20) (actual time=0.100..0.100 rows=165 loops=1)

  • Output: si_5.subscription_item_id, si_5.subscription_item_type, si_5.detail_id
  • Buckets: 1,024 Batches: 1 Memory Usage: 18kB
68. 0.075 0.075 ↓ 20.6 165 1

Seq Scan on bm.subscription_item si_5 (cost=0.00..8.27 rows=8 width=20) (actual time=0.008..0.075 rows=165 loops=1)

  • Output: si_5.subscription_item_id, si_5.subscription_item_type, si_5.detail_id
  • Filter: ((si_5.subscription_item_type)::text = ANY ('{PRICEPLAN-POSTPAID-INDIVIDUAL,PRICEPLAN-PREPAID-INDIVIDUAL,PRICEPLAN-POSTPAID-FLEX,PRICEPLAN-PREPAID-FLEX,PRICEPLAN-POSTPAID-STATIC,PRICEPLAN-PREPAID-STATIC,PRICEPLAN-TOPOFF}'::text[]))
  • Rows Removed by Filter: 12
69. 0.001 0.038 ↓ 0.0 0 1

Nested Loop Left Join (cost=25.00..1,044.47 rows=5 width=870) (actual time=0.038..0.038 rows=0 loops=1)

  • Output: si_6.subscription_item_id, hm_3.name, (si_6.subscription_item_type)::text, CASE WHEN ((hm_3.status)::text = 'DR'::text) THEN 'DRAFT'::text WHEN ((hm_3.status)::text = 'AP'::text) THEN 'READY-FOR-APPROVAL'::text WHEN ((hm_3.status)::text = 'OK'::text) THEN 'APPROVED'::t
  • Join Filter: (hm_3.id = si_6.detail_id)
70. 0.037 0.037 ↓ 0.0 0 1

Seq Scan on bm.subscription_item si_6 (cost=0.00..6.88 rows=1 width=20) (actual time=0.037..0.037 rows=0 loops=1)

  • Output: si_6.subscription_item_id, si_6.subscription_item_type, si_6.roaming_profile_id, si_6.detail_id, si_6.priceplan_msub_networkpricelist_subscription_item_id, si_6.assigned_to_billing_profile_id, si_6.status, si_6.network_interface_id, si_6.creation_timestamp, si_6.
  • Filter: ((si_6.subscription_item_type)::text = 'PRICEPLAN-NETWORKPRICELIST'::text)
  • Rows Removed by Filter: 177
71. 0.000 0.000 ↓ 0.0 0

Foreign Scan on bm_mysql._hardware_mobilephone_nwtariff hm_3 (cost=25.00..1,025.00 rows=1,000 width=806) (never executed)

  • Output: hm_3.id, hm_3.bm_subscription_item_id, hm_3.master_id, hm_3.name, hm_3.description, hm_3.customer_id, hm_3.vk_once, hm_3.vk_monthly, hm_3.vk_bail, hm_3.vk_network_access, hm_3.binding, hm_3.notice_period_customer, hm_3.notice_period_provider, hm_3.pool_phone_ifre
  • Remote server startup cost: 25
  • Remote query: SELECT `id`, `name`, `status`, `uuid` FROM `office_nokia`.`hardware_mobilephone_nwtariff`
72. 0.004 0.137 ↓ 1.3 12 1

Subquery Scan on *SELECT* 3_1 (cost=31.91..1,036.00 rows=9 width=144) (actual time=0.117..0.137 rows=12 loops=1)

  • Output: "*SELECT* 3_1".subscription_item_id, "*SELECT* 3_1".name, "*SELECT* 3_1".subscription_item_type, "*SELECT* 3_1".status, "*SELECT* 3_1".detail_id, NULL::character varying
73. 0.019 0.133 ↓ 1.3 12 1

Hash Right Join (cost=31.91..1,035.91 rows=9 width=144) (actual time=0.115..0.133 rows=12 loops=1)

  • Output: si_7.subscription_item_id, v_1.name, (si_7.subscription_item_type)::text, CASE WHEN ((v_1.status)::text = 'DR'::text) THEN 'DRAFT'::text WHEN ((v_1.status)::text = 'AP'::text) THEN 'READY-FOR-APPROVAL'::text WHEN ((v_1.status)::text = 'OK'::text) THEN 'APPROVED':
  • Hash Cond: (v_1.id = si_7.detail_id)
74. 0.065 0.065 ↑ 83.3 12 1

Foreign Scan on bm_mysql._hardware_hardwareservice_catalog v_1 (cost=25.00..1,025.00 rows=1,000 width=52) (actual time=0.056..0.065 rows=12 loops=1)

  • Output: v_1.id, v_1.tree_id, v_1.sort_id, v_1.name, v_1.description, v_1.type, v_1.vk_base, v_1.is_active, v_1.status
  • Remote server startup cost: 25
  • Remote query: SELECT `id`, `name`, `status` FROM `office_nokia`.`hardware_hardwareservice_catalog`
75. 0.003 0.049 ↓ 6.0 12 1

Hash (cost=6.88..6.88 rows=2 width=20) (actual time=0.049..0.049 rows=12 loops=1)

  • Output: si_7.subscription_item_id, si_7.subscription_item_type, si_7.detail_id
  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
76. 0.046 0.046 ↓ 6.0 12 1

Seq Scan on bm.subscription_item si_7 (cost=0.00..6.88 rows=2 width=20) (actual time=0.006..0.046 rows=12 loops=1)

  • Output: si_7.subscription_item_id, si_7.subscription_item_type, si_7.detail_id
  • Filter: ((si_7.subscription_item_id IS NOT NULL) AND ((si_7.subscription_item_type)::text = ANY ('{ADDON-ACCOUNT,ADDON-SIM}'::text[])))
  • Rows Removed by Filter: 165
77. 0.001 0.037 ↓ 0.0 0 1

Subquery Scan on *SELECT* 4_1 (cost=25.00..1,044.53 rows=5 width=144) (actual time=0.037..0.037 rows=0 loops=1)

  • Output: "*SELECT* 4_1".subscription_item_id, "*SELECT* 4_1".info, "*SELECT* 4_1".subscription_item_type, "*SELECT* 4_1"."case", "*SELECT* 4_1".id, NULL::character varying
78. 0.001 0.036 ↓ 0.0 0 1

Nested Loop Left Join (cost=25.00..1,044.48 rows=5 width=144) (actual time=0.036..0.036 rows=0 loops=1)

  • Output: si_8.subscription_item_id, a_1.info, (si_8.subscription_item_type)::text, CASE WHEN (a_1.status = 'DR'::text) THEN 'DRAFT'::text WHEN (a_1.status = 'AP'::text) THEN 'READY-FOR-APPROVAL'::text WHEN (a_1.status = 'OK'::text) THEN 'APPROVED'::text WHEN (a_1.status =
  • Join Filter: (a_1.id = si_8.detail_id)
79. 0.035 0.035 ↓ 0.0 0 1

Seq Scan on bm.subscription_item si_8 (cost=0.00..6.88 rows=1 width=20) (actual time=0.035..0.035 rows=0 loops=1)

  • Output: si_8.subscription_item_id, si_8.subscription_item_type, si_8.roaming_profile_id, si_8.detail_id, si_8.priceplan_msub_networkpricelist_subscription_item_id, si_8.assigned_to_billing_profile_id, si_8.status, si_8.network_interface_id, si_8.creation_timestamp,
  • Filter: ((si_8.subscription_item_type)::text = 'ADDON-TOPOFF'::text)
  • Rows Removed by Filter: 177
80. 0.000 0.000 ↓ 0.0 0

Foreign Scan on bm_mysql._products_hardwareservice_topoff a_1 (cost=25.00..1,025.00 rows=1,000 width=72) (never executed)

  • Output: a_1.id, a_1.customer_id, a_1.product_vk_once, a_1.info, a_1.volume_self_after, a_1.volume_self_after_unit, a_1.validity, a_1.status
  • Remote server startup cost: 25
  • Remote query: SELECT `id`, `info`, `status` FROM `office_nokia`.`products_hardwareservice_topoff`
Planning time : 6.029 ms
Execution time : 9,148.545 ms