explain.depesz.com

PostgreSQL's explain analyze made readable

Result: teD4

Settings
# exclusive inclusive rows x rows loops node
1. 0.005 4,369.862 ↑ 1.0 45 1

Limit (cost=16,151.46..16,157.76 rows=45 width=3,049) (actual time=4,369.808..4,369.862 rows=45 loops=1)

2. 0.049 4,369.857 ↑ 119.8 45 1

Unique (cost=16,151.46..16,905.92 rows=5,389 width=3,049) (actual time=4,369.807..4,369.857 rows=45 loops=1)

3. 8.089 4,369.808 ↑ 119.8 45 1

Sort (cost=16,151.46..16,164.94 rows=5,389 width=3,049) (actual time=4,369.801..4,369.808 rows=45 loops=1)

  • 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_.
  • Sort Method: external merge Disk: 2,744kB
4. 2,322.063 4,361.719 ↑ 1.0 5,389 1

Nested Loop Left Join (cost=11,103.36..11,662.11 rows=5,389 width=3,049) (actual time=500.273..4,361.719 rows=5,389 loops=1)

  • Join Filter: (simcard0_.profile_id = q.id)
  • Rows Removed by Join Filter: 29,041,321
5. 2.614 2.614 ↑ 1.0 5,389 1

Seq Scan on sim_cards simcard0_ (cost=0.00..472.38 rows=5,389 width=2,837) (actual time=0.012..2.614 rows=5,389 loops=1)

  • Filter: (deleted = 0)
  • Rows Removed by Filter: 1
6. 1,509.389 2,037.042 ↓ 5,390.0 5,390 5,389

Materialize (cost=11,103.36..11,108.90 rows=1 width=212) (actual time=0.076..0.378 rows=5,390 loops=5,389)

7. 8.600 527.653 ↓ 5,390.0 5,390 1

Subquery Scan on q (cost=11,103.36..11,108.90 rows=1 width=212) (actual time=407.687..527.653 rows=5,390 loops=1)

  • Filter: (q.rank = 1)
  • Rows Removed by Filter: 78,077
8. 96.863 519.053 ↓ 678.6 83,467 1

WindowAgg (cost=11,103.36..11,107.36 rows=123 width=244) (actual time=407.685..519.053 rows=83,467 loops=1)

9. 72.760 422.190 ↓ 678.6 83,467 1

Sort (cost=11,103.36..11,103.67 rows=123 width=176) (actual time=407.672..422.190 rows=83,467 loops=1)

  • Sort Key: sim.sim_id, (COALESCE(ptm.prepaid_notify, 0)), (COALESCE(ptm.id, ptmn.id)) DESC
  • Sort Method: external merge Disk: 17,328kB
10. 30.705 349.430 ↓ 678.6 83,467 1

Hash Left Join (cost=10,093.90..11,099.09 rows=123 width=176) (actual time=263.656..349.430 rows=83,467 loops=1)

  • Hash Cond: (bsi.subscription_item_id = bsil.subscription_item_id)
11. 23.859 317.760 ↓ 678.6 83,467 1

Hash Left Join (cost=5,937.75..6,942.00 rows=123 width=128) (actual time=262.686..317.760 rows=83,467 loops=1)

  • Hash Cond: (bbpsi.subscription_item_id = bsi.subscription_item_id)
12. 31.229 293.856 ↓ 678.6 83,467 1

Hash Right Join (cost=5,932.60..6,936.52 rows=123 width=128) (actual time=262.633..293.856 rows=83,467 loops=1)

  • Hash Cond: (ptmn.bm_sim_subscription_item_id = sim_sub.sim_subscription_id)
13. 0.045 0.045 ↓ 0.0 0 1

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

14. 30.520 262.582 ↓ 678.6 83,467 1

Hash (cost=5,906.06..5,906.06 rows=123 width=128) (actual time=262.582..262.582 rows=83,467 loops=1)

  • Buckets: 32,768 (originally 1024) Batches: 4 (originally 1) Memory Usage: 3,841kB
15. 34.444 232.062 ↓ 678.6 83,467 1

Hash Right Join (cost=4,902.14..5,906.06 rows=123 width=128) (actual time=48.606..232.062 rows=83,467 loops=1)

  • Hash Cond: (ptm.bm_sim_subscription_item_id = sim_sub.sim_subscription_id)
16. 149.222 149.222 ↓ 84.0 84,050 1

Foreign Scan on _products_telephone_mobilephone ptm (cost=25.00..1,025.00 rows=1,000 width=20) (actual time=0.200..149.222 rows=84,050 loops=1)

17. 1.487 48.396 ↓ 43.9 5,399 1

Hash (cost=4,875.60..4,875.60 rows=123 width=116) (actual time=48.396..48.396 rows=5,399 loops=1)

  • Buckets: 8,192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 898kB
18. 1.688 46.909 ↓ 43.9 5,399 1

Hash Join (cost=4,392.15..4,875.60 rows=123 width=116) (actual time=37.812..46.909 rows=5,399 loops=1)

  • Hash Cond: (bpsi.subscription_item_id = si.subscription_item_id)
19. 1.934 45.180 ↓ 33.3 5,399 1

Hash Join (cost=4,387.00..4,870.02 rows=162 width=132) (actual time=37.767..45.180 rows=5,399 loops=1)

  • Hash Cond: (bpsi.subscription_item_id = sil.subscription_item_id)
20. 1.671 42.102 ↓ 17.2 5,399 1

Hash Left Join (cost=230.85..711.07 rows=313 width=60) (actual time=36.619..42.102 rows=5,399 loops=1)

  • Hash Cond: (basi.billing_profile_subscription_item_id = bbpsi.billing_profile_subscription_item_id)
21. 2.952 40.388 ↓ 17.2 5,399 1

Hash Right Join (cost=226.37..705.75 rows=313 width=60) (actual time=36.569..40.388 rows=5,399 loops=1)

  • Hash Cond: ((sim_card.iccid)::text = (sim.sim_identifier)::text)
22. 0.881 0.881 ↑ 1.0 5,390 1

Seq Scan on sim_cards sim_card (cost=0.00..458.90 rows=5,390 width=24) (actual time=0.007..0.881 rows=5,390 loops=1)

23. 1.677 36.555 ↓ 17.2 5,399 1

Hash (cost=222.46..222.46 rows=313 width=76) (actual time=36.554..36.555 rows=5,399 loops=1)

  • Buckets: 8,192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 655kB
24. 1.596 34.878 ↓ 17.2 5,399 1

Merge Join (cost=1.41..222.46 rows=313 width=76) (actual time=0.065..34.878 rows=5,399 loops=1)

  • Merge Cond: (asi.billing_profile_subscription_item_id = bpsi.billing_profile_subscription_item_id)
  • -> Index Scan using pk_billing_profile_subscription_item on billing_profile_subscription_item bpsi (cost=0.14..6.09 rows=110 width=16) (actual time=
25. 4.881 33.282 ↓ 17.2 5,399 1

Nested Loop (cost=1.27..554.78 rows=313 width=76) (actual time=0.056..33.282 rows=5,399 loops=1)

26. 10.129 23.002 ↓ 17.2 5,399 1

Nested Loop (cost=0.85..298.85 rows=313 width=56) (actual time=0.045..23.002 rows=5,399 loops=1)

  • -> Index Scan using ix_fk_subscription__sim_subscription on sim_subscription sim_sub (cost=0.28..0.33 rows=1 width=24) (actual time=0.00
27. 9.877 12.873 ↓ 22.6 5,525 1

Nested Loop Left Join (cost=0.57..216.53 rows=245 width=48) (actual time=0.031..12.873 rows=5,525 loops=1)

  • -> Index Scan using pk_account_subscription_item on account_subscription_item basi (cost=0.14..0.18 rows=1 width=20) (actual time=
  • Index Cond: (subscription_id = sub.subscription_id)
28. 2.996 2.996 ↓ 22.6 5,525 1

Nested Loop (cost=0.43..172.42 rows=245 width=36) (actual time=0.024..2.996 rows=5,525 loops=1)

  • -> Index Scan using ix_fk_account_si__subscription on subscription sub (cost=0.28..20.48 rows=43 width=24) (actual time=0.00
  • Index Cond: (account_subscription_item_id = sub.base_account_subscription_item_id)
  • Filter: ((c_subscription_item_type)::text = ANY ('{PRICEPLAN-NETWORKPRICELIST,PRICEPLAN-POSTPAID-FLEX,PRICEPLAN-POSTPAID-INDIV
29. 0.000 0.000 ↓ 0.0

Index Scan using ix_fk_billing_profile_si__account_si on account_subscription_item asi (cost=0.14..26.04 rows=7 width=20) (actual rows= loops=)

  • Filter: ((c_subscription_item_type)::text = ANY ('{PRICEPLAN-NETWORKPRICELIST,PRICEPLAN-POSTPAID-FLEX,PRICEPLAN-POSTPAID
  • Rows Removed by Filter: 16
  • Index Cond: (account_subscription_item_id = asi.account_subscription_item_id)
30. 5.399 5.399 ↑ 1.0 1 5,399

Index Scan using pk_sim on sim (cost=0.41..0.82 rows=1 width=28) (actual time=0.001..0.001 rows=1 loops=5,399)

  • Index Cond: (sim_id = sim_sub.sim_id)
31. 0.019 0.043 ↓ 1.4 149 1

Hash (cost=3.10..3.10 rows=110 width=16) (actual time=0.043..0.043 rows=149 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 15kB
32. 0.024 0.024 ↓ 1.4 149 1

Seq Scan on billing_profile_subscription_item bbpsi (cost=0.00..3.10 rows=110 width=16) (actual time=0.005..0.024 rows=149 loops=1)

33. 0.026 1.144 ↓ 2.7 148 1

Hash (cost=4,155.46..4,155.46 rows=55 width=72) (actual time=1.144..1.144 rows=148 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 25kB
34. 0.023 1.118 ↓ 2.7 148 1

Subquery Scan on sil (cost=4,154.36..4,155.46 rows=55 width=72) (actual time=1.059..1.118 rows=148 loops=1)

35. 0.123 1.095 ↓ 2.7 148 1

HashAggregate (cost=4,154.36..4,154.91 rows=55 width=144) (actual time=1.058..1.095 rows=148 loops=1)

  • 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 ((
36. 0.013 0.972 ↓ 2.7 148 1

Append (cost=30.41..4,153.54 rows=55 width=144) (actual time=0.424..0.972 rows=148 loops=1)

37. 0.110 0.766 ↓ 3.9 136 1

Hash Right Join (cost=30.41..1,035.12 rows=35 width=870) (actual time=0.423..0.766 rows=136 loops=1)

  • Hash Cond: (hm.id = si_1.detail_id)
38. 0.574 0.574 ↑ 6.7 149 1

Foreign Scan on _hardware_mobilephone hm (cost=25.00..1,025.00 rows=1,000 width=806) (actual time=0.332..0.574 rows=149 loops=1)

39. 0.019 0.082 ↓ 19.4 136 1

Hash (cost=5.33..5.33 rows=7 width=20) (actual time=0.082..0.082 rows=136 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 16kB
40. 0.063 0.063 ↓ 19.4 136 1

Seq Scan on subscription_item si_1 (cost=0.00..5.33 rows=7 width=20) (actual time=0.007..0.063 rows=136 loops=1)

  • Filter: ((subscription_item_type)::text = ANY ('{PRICEPLAN-POSTPAID-INDIVIDUAL,PRICEPLAN-PREPAID-INDIVIDUAL,PRICEPLAN-POSTPAID-FLEX,
  • Rows Removed by Filter: 12
41. 0.001 0.032 ↓ 0.0 0 1

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

  • Join Filter: (hm_1.id = si_2.detail_id)
42. 0.031 0.031 ↓ 0.0 0 1

Seq Scan on subscription_item si_2 (cost=0.00..4.45 rows=1 width=20) (actual time=0.031..0.031 rows=0 loops=1)

  • Filter: ((subscription_item_type)::text = 'PRICEPLAN-NETWORKPRICELIST'::text)
  • Rows Removed by Filter: 148
43. 0.000 0.000 ↓ 0.0 0

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

44. 0.004 0.130 ↓ 1.2 12 1

Subquery Scan on *SELECT* 3 (cost=29.47..1,033.60 rows=10 width=144) (actual time=0.109..0.130 rows=12 loops=1)

45. 0.085 0.126 ↓ 1.2 12 1

Hash Right Join (cost=29.47..1,033.50 rows=10 width=144) (actual time=0.108..0.126 rows=12 loops=1)

  • Hash Cond: (v.id = si_3.detail_id)
  • -> Foreign Scan on _hardware_hardwareservice_catalog v (cost=25.00..1025.00 rows=1,000 width=52) (actual time=0.059..0.068 rows=12 loops=
46. 0.002 0.041 ↓ 6.0 12 1

Hash (cost=4.45..4.45 rows=2 width=20) (actual time=0.041..0.041 rows=12 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
47. 0.039 0.039 ↓ 6.0 12 1

Seq Scan on subscription_item si_3 (cost=0.00..4.45 rows=2 width=20) (actual time=0.005..0.039 rows=12 loops=1)

  • Filter: ((subscription_item_id IS NOT NULL) AND ((subscription_item_type)::text = ANY ('{ADDON-ACCOUNT,ADDON-SIM}'::text[])))
  • Rows Removed by Filter: 136
48. 0.001 0.031 ↓ 0.0 0 1

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

49. 0.000 0.030 ↓ 0.0 0 1

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

  • Join Filter: (a.id = si_4.detail_id)
50. 0.030 0.030 ↓ 0.0 0 1

Seq Scan on subscription_item si_4 (cost=0.00..4.45 rows=1 width=20) (actual time=0.030..0.030 rows=0 loops=1)

  • Filter: ((subscription_item_type)::text = 'ADDON-TOPOFF'::text)
  • Rows Removed by Filter: 148
51. 0.000 0.000 ↓ 0.0 0

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

52. 0.019 0.041 ↓ 1.1 148 1

Hash (cost=3.40..3.40 rows=140 width=8) (actual time=0.041..0.041 rows=148 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 14kB
53. 0.022 0.022 ↓ 1.1 148 1

Seq Scan on subscription_item si (cost=0.00..3.40 rows=140 width=8) (actual time=0.007..0.022 rows=148 loops=1)

54. 0.018 0.045 ↓ 1.1 148 1

Hash (cost=3.40..3.40 rows=140 width=8) (actual time=0.045..0.045 rows=148 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 14kB
55. 0.027 0.027 ↓ 1.1 148 1

Seq Scan on subscription_item bsi (cost=0.00..3.40 rows=140 width=8) (actual time=0.011..0.027 rows=148 loops=1)

56. 0.024 0.965 ↓ 2.7 148 1

Hash (cost=4,155.46..4,155.46 rows=55 width=72) (actual time=0.965..0.965 rows=148 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 25kB
57. 0.021 0.941 ↓ 2.7 148 1

Subquery Scan on bsil (cost=4,154.36..4,155.46 rows=55 width=72) (actual time=0.887..0.941 rows=148 loops=1)

58. 0.117 0.920 ↓ 2.7 148 1

HashAggregate (cost=4,154.36..4,154.91 rows=55 width=144) (actual time=0.886..0.920 rows=148 loops=1)

  • 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
59. 0.015 0.803 ↓ 2.7 148 1

Append (cost=30.41..4,153.54 rows=55 width=144) (actual time=0.272..0.803 rows=148 loops=1)

60. 0.107 0.616 ↓ 3.9 136 1

Hash Right Join (cost=30.41..1,035.12 rows=35 width=870) (actual time=0.271..0.616 rows=136 loops=1)

  • Hash Cond: (hm_2.id = si_5.detail_id)
61. 0.429 0.429 ↑ 6.7 149 1

Foreign Scan on _hardware_mobilephone hm_2 (cost=25.00..1,025.00 rows=1,000 width=806) (actual time=0.184..0.429 rows=149 loops=1)

62. 0.019 0.080 ↓ 19.4 136 1

Hash (cost=5.33..5.33 rows=7 width=20) (actual time=0.080..0.080 rows=136 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 16kB
63. 0.061 0.061 ↓ 19.4 136 1

Seq Scan on subscription_item si_5 (cost=0.00..5.33 rows=7 width=20) (actual time=0.007..0.061 rows=136 loops=1)

  • Filter: ((subscription_item_type)::text = ANY ('{PRICEPLAN-POSTPAID-INDIVIDUAL,PRICEPLAN-PREPAID-INDIVIDUAL,PRICEPLAN-POSTPAID-FLEX,PRICEPLAN-PREPAID-FLEX,PRICEPLAN-POSTPAID-
  • Rows Removed by Filter: 12
64. 0.000 0.029 ↓ 0.0 0 1

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

  • Join Filter: (hm_3.id = si_6.detail_id)
65. 0.029 0.029 ↓ 0.0 0 1

Seq Scan on subscription_item si_6 (cost=0.00..4.45 rows=1 width=20) (actual time=0.029..0.029 rows=0 loops=1)

  • Filter: ((subscription_item_type)::text = 'PRICEPLAN-NETWORKPRICELIST'::text)
  • Rows Removed by Filter: 148
66. 0.000 0.000 ↓ 0.0 0

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

67. 0.004 0.114 ↓ 1.2 12 1

Subquery Scan on *SELECT* 3_1 (cost=29.47..1,033.60 rows=10 width=144) (actual time=0.095..0.114 rows=12 loops=1)

68. 0.016 0.110 ↓ 1.2 12 1

Hash Right Join (cost=29.47..1,033.50 rows=10 width=144) (actual time=0.094..0.110 rows=12 loops=1)

  • Hash Cond: (v_1.id = si_7.detail_id)
69. 0.056 0.056 ↑ 83.3 12 1

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

70. 0.002 0.038 ↓ 6.0 12 1

Hash (cost=4.45..4.45 rows=2 width=20) (actual time=0.038..0.038 rows=12 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
71. 0.036 0.036 ↓ 6.0 12 1

Seq Scan on subscription_item si_7 (cost=0.00..4.45 rows=2 width=20) (actual time=0.005..0.036 rows=12 loops=1)

  • Filter: ((subscription_item_id IS NOT NULL) AND ((subscription_item_type)::text = ANY ('{ADDON-ACCOUNT,ADDON-SIM}'::text[])))
  • Rows Removed by Filter: 136
72. 0.001 0.029 ↓ 0.0 0 1

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

73. 0.000 0.028 ↓ 0.0 0 1

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

  • Join Filter: (a_1.id = si_8.detail_id)
74. 0.028 0.028 ↓ 0.0 0 1

Seq Scan on subscription_item si_8 (cost=0.00..4.45 rows=1 width=20) (actual time=0.028..0.028 rows=0 loops=1)

  • Filter: ((subscription_item_type)::text = 'ADDON-TOPOFF'::text)
  • Rows Removed by Filter: 148
75. 0.000 0.000 ↓ 0.0 0

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

Planning time : 5.731 ms
Execution time : 4,512.164 ms