explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6gfd

Settings
# exclusive inclusive rows x rows loops node
1. 0.010 1,643.368 ↑ 1.0 45 1

Limit (cost=11,784.61..11,790.91 rows=45 width=3,049) (actual time=1,643.321..1,643.368 rows=45 loops=1)

2. 0.148 1,643.358 ↑ 9.9 135 1

Unique (cost=11,772.01..11,959.05 rows=1,336 width=3,049) (actual time=1,643.199..1,643.358 rows=135 loops=1)

3. 1.242 1,643.210 ↑ 9.9 135 1

Sort (cost=11,772.01..11,775.35 rows=1,336 width=3,049) (actual time=1,643.198..1,643.210 rows=135 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: quicksort Memory: 792kB
4. 659.943 1,641.968 ↓ 1.0 1,370 1

Nested Loop Left Join (cost=11,174.87..11,702.65 rows=1,336 width=3,049) (actual time=518.313..1,641.968 rows=1,370 loops=1)

  • Join Filter: (simcard0_.profile_id = q.id)
  • Rows Removed by Join Filter: 7,382,930
5. 0.602 5.215 ↓ 1.0 1,370 1

Nested Loop (cost=73.57..575.68 rows=1,336 width=2,837) (actual time=0.791..5.215 rows=1,370 loops=1)

6. 0.051 0.051 ↑ 1.0 1 1

Index Only Scan using usr_orgunit_pkey on usr_orgunit organizati2_ (cost=0.27..2.49 rows=1 width=4) (actual time=0.048..0.051 rows=1 loops=1)

  • Index Cond: (id = 8)
  • Heap Fetches: 1
7. 1.691 4.562 ↓ 1.0 1,370 1

Hash Join (cost=73.30..559.83 rows=1,336 width=2,841) (actual time=0.739..4.562 rows=1,370 loops=1)

  • Hash Cond: (simcard0_.id = organizati1_.sim_id)
8. 2.157 2.157 ↑ 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.007..2.157 rows=5,389 loops=1)

  • Filter: (deleted = 0)
  • Rows Removed by Filter: 1
9. 0.246 0.714 ↓ 1.0 1,370 1

Hash (cost=56.60..56.60 rows=1,336 width=8) (actual time=0.714..0.714 rows=1,370 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 70kB
10. 0.468 0.468 ↓ 1.0 1,370 1

Index Only Scan using sim_card_org_relation_i_1 on sim_card_org_relation organizati1_ (cost=0.28..56.60 rows=1,336 width=8) (actual time=0.042..0.468 rows=1,370 loops=1)

  • Index Cond: (org_id = 8)
  • Heap Fetches: 1,370
11. 431.431 976.810 ↓ 5,390.0 5,390 1,370

Materialize (cost=11,101.30..11,106.93 rows=1 width=212) (actual time=0.305..0.713 rows=5,390 loops=1,370)

12. 9.341 545.379 ↓ 5,390.0 5,390 1

Subquery Scan on q (cost=11,101.30..11,106.93 rows=1 width=212) (actual time=417.286..545.379 rows=5,390 loops=1)

  • Filter: (q.rank = 1)
  • Rows Removed by Filter: 71,484
13. 102.611 536.038 ↓ 615.0 76,874 1

WindowAgg (cost=11,101.30..11,105.37 rows=125 width=244) (actual time=417.284..536.038 rows=76,874 loops=1)

14. 78.629 433.427 ↓ 615.0 76,874 1

Sort (cost=11,101.30..11,101.62 rows=125 width=176) (actual time=417.270..433.427 rows=76,874 loops=1)

  • Sort Key: sim.sim_id, (COALESCE(ptm.prepaid_notify, 0)), (COALESCE(ptm.id, ptmn.id)) DESC
  • Sort Method: external merge Disk: 15,960kB
15. 32.205 354.798 ↓ 615.0 76,874 1

Hash Left Join (cost=10,091.72..11,096.95 rows=125 width=176) (actual time=263.891..354.798 rows=76,874 loops=1)

  • Hash Cond: (bsi.subscription_item_id = bsil.subscription_item_id)
16. 25.298 321.563 ↓ 615.0 76,874 1

Hash Left Join (cost=5,935.57..6,939.84 rows=125 width=128) (actual time=262.853..321.563 rows=76,874 loops=1)

  • Hash Cond: (bbpsi.subscription_item_id = bsi.subscription_item_id)
17. 33.434 296.220 ↓ 615.0 76,874 1

Hash Right Join (cost=5,930.42..6,934.35 rows=125 width=128) (actual time=262.800..296.220 rows=76,874 loops=1)

  • Hash Cond: (ptmn.bm_sim_subscription_item_id = sim_sub.sim_subscription_id)
18. 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)

19. 30.898 262.741 ↓ 615.0 76,874 1

Hash (cost=5,903.86..5,903.86 rows=125 width=128) (actual time=262.741..262.741 rows=76,874 loops=1)

  • Buckets: 32,768 (originally 1024) Batches: 4 (originally 1) Memory Usage: 3,841kB
20. 34.377 231.843 ↓ 615.0 76,874 1

Hash Right Join (cost=4,899.93..5,903.86 rows=125 width=128) (actual time=46.543..231.843 rows=76,874 loops=1)

  • Hash Cond: (ptm.bm_sim_subscription_item_id = sim_sub.sim_subscription_id)
21. 151.073 151.073 ↓ 77.5 77,452 1

Foreign Scan on _products_telephone_mobilephone ptm (cost=25.00..1,025.00 rows=1,000 width=20) (actual time=0.128..151.073 rows=77,452 loops=1)

22. 2.079 46.393 ↓ 43.2 5,399 1

Hash (cost=4,873.37..4,873.37 rows=125 width=116) (actual time=46.393..46.393 rows=5,399 loops=1)

  • Buckets: 8,192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 898kB
23. 1.850 44.314 ↓ 43.2 5,399 1

Hash Join (cost=4,389.34..4,873.37 rows=125 width=116) (actual time=33.519..44.314 rows=5,399 loops=1)

  • Hash Cond: (bpsi.subscription_item_id = si.subscription_item_id)
24. 2.267 42.411 ↓ 32.9 5,399 1

Hash Join (cost=4,384.19..4,867.78 rows=164 width=132) (actual time=33.450..42.411 rows=5,399 loops=1)

  • Hash Cond: (bpsi.subscription_item_id = sil.subscription_item_id)
25. 2.194 38.807 ↓ 17.0 5,399 1

Hash Left Join (cost=228.04..708.80 rows=317 width=60) (actual time=32.098..38.807 rows=5,399 loops=1)

  • Hash Cond: (sub.base_account_subscription_item_id = basi.account_subscription_item_id)
26. 3.055 36.383 ↓ 17.0 5,399 1

Hash Right Join (cost=215.32..694.75 rows=317 width=48) (actual time=31.847..36.383 rows=5,399 loops=1)

  • Hash Cond: ((sim_card.iccid)::text = (sim.sim_identifier)::text)
27. 1.502 1.502 ↑ 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.006..1.502 rows=5,390 loops=1)

28. 2.057 31.826 ↓ 17.0 5,399 1

Hash (cost=211.36..211.36 rows=317 width=64) (actual time=31.826..31.826 rows=5,399 loops=1)

  • Buckets: 8,192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 613kB
29. 1.574 29.769 ↓ 17.0 5,399 1

Merge Join (cost=1.27..211.36 rows=317 width=64) (actual time=0.099..29.769 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=
30. 1.623 28.195 ↓ 17.0 5,399 1

Nested Loop (cost=1.12..515.44 rows=317 width=64) (actual time=0.088..28.195 rows=5,399 loops=1)

31. 11.944 15.774 ↓ 17.0 5,399 1

Nested Loop (cost=0.71..256.24 rows=317 width=44) (actual time=0.058..15.774 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
32. 3.830 3.830 ↓ 22.3 5,525 1

Nested Loop (cost=0.43..172.90 rows=248 width=36) (actual time=0.044..3.830 rows=5,525 loops=1)

  • -> Index Scan using ix_fk_billing_profile_si__account_si on account_subscription_item asi (cost=0.14..12.18 rows=7 width=20) (actu
  • Filter: ((c_subscription_item_type)::text = ANY ('{PRICEPLAN-NETWORKPRICELIST,PRICEPLAN-POSTPAID-FLEX,PRICEPLAN-POSTPAID-INDIV
  • Rows Removed by Filter: 16
  • -> Index Scan using ix_fk_account_si__subscription on subscription sub (cost=0.28..22.52 rows=44 width=24) (actual time=0.002..0.0
  • Index Cond: (account_subscription_item_id = asi.account_subscription_item_id)
  • Index Cond: (subscription_id = sub.subscription_id)
33. 10.798 10.798 ↑ 1.0 1 5,399

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

  • Index Cond: (sim_id = sim_sub.sim_id)
34. 0.028 0.230 ↓ 20.3 142 1

Hash (cost=12.63..12.63 rows=7 width=20) (actual time=0.230..0.230 rows=142 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 16kB
35. 0.073 0.202 ↓ 20.3 142 1

Hash Right Join (cost=8.79..12.63 rows=7 width=20) (actual time=0.129..0.202 rows=142 loops=1)

  • Hash Cond: (bbpsi.billing_profile_subscription_item_id = basi.billing_profile_subscription_item_id)
36. 0.019 0.019 ↓ 1.4 149 1

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

37. 0.029 0.110 ↓ 20.3 142 1

Hash (cost=8.71..8.71 rows=7 width=20) (actual time=0.110..0.110 rows=142 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 16kB
38. 0.081 0.081 ↓ 20.3 142 1

Seq Scan on account_subscription_item basi (cost=0.00..8.71 rows=7 width=20) (actual time=0.006..0.081 rows=142 loops=1)

  • Filter: ((c_subscription_item_type)::text = ANY ('{PRICEPLAN-NETWORKPRICELIST,PRICEPLAN-POSTPAID-FLEX,PRICEPLAN-POSTPAID-INDIVIDUAL,PRICEPLAN-PO
  • Rows Removed by Filter: 16
39. 0.035 1.337 ↓ 2.7 148 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 25kB
40. 0.026 1.302 ↓ 2.7 148 1

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

41. 0.149 1.276 ↓ 2.7 148 1

HashAggregate (cost=4,154.36..4,154.91 rows=55 width=144) (actual time=1.232..1.276 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 ((
42. 0.016 1.127 ↓ 2.7 148 1

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

43. 0.136 0.866 ↓ 3.9 136 1

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

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

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

45. 0.027 0.097 ↓ 19.4 136 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 16kB
46. 0.070 0.070 ↓ 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.070 rows=136 loops=1)

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

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

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

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

  • Filter: ((subscription_item_type)::text = 'PRICEPLAN-NETWORKPRICELIST'::text)
  • Rows Removed by Filter: 148
49. 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)

50. 0.004 0.176 ↓ 1.2 12 1

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

51. 0.120 0.172 ↓ 1.2 12 1

Hash Right Join (cost=29.47..1,033.50 rows=10 width=144) (actual time=0.151..0.172 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.064..0.074 rows=12 loops=
52. 0.006 0.052 ↓ 6.0 12 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
53. 0.046 0.046 ↓ 6.0 12 1

Seq Scan on subscription_item si_3 (cost=0.00..4.45 rows=2 width=20) (actual time=0.007..0.046 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
54. 0.001 0.035 ↓ 0.0 0 1

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

55. 0.000 0.034 ↓ 0.0 0 1

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

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

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

  • Filter: ((subscription_item_type)::text = 'ADDON-TOPOFF'::text)
  • Rows Removed by Filter: 148
57. 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)

58. 0.024 0.053 ↓ 1.1 148 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 14kB
59. 0.029 0.029 ↓ 1.1 148 1

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

60. 0.019 0.045 ↓ 1.1 148 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 14kB
61. 0.026 0.026 ↓ 1.1 148 1

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

62. 0.028 1.030 ↓ 2.7 148 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 25kB
63. 0.026 1.002 ↓ 2.7 148 1

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

64. 0.149 0.976 ↓ 2.7 148 1

HashAggregate (cost=4,154.36..4,154.91 rows=55 width=144) (actual time=0.919..0.976 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
65. 0.013 0.827 ↓ 2.7 148 1

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

66. 0.110 0.621 ↓ 3.9 136 1

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

  • Hash Cond: (hm_2.id = si_5.detail_id)
67. 0.431 0.431 ↑ 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.187..0.431 rows=149 loops=1)

68. 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
69. 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
70. 0.001 0.031 ↓ 0.0 0 1

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

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

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

  • Filter: ((subscription_item_type)::text = 'PRICEPLAN-NETWORKPRICELIST'::text)
  • Rows Removed by Filter: 148
72. 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)

73. 0.004 0.124 ↓ 1.2 12 1

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

74. 0.016 0.120 ↓ 1.2 12 1

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

  • Hash Cond: (v_1.id = si_7.detail_id)
75. 0.061 0.061 ↑ 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.052..0.061 rows=12 loops=1)

76. 0.003 0.043 ↓ 6.0 12 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
77. 0.040 0.040 ↓ 6.0 12 1

Seq Scan on subscription_item si_7 (cost=0.00..4.45 rows=2 width=20) (actual time=0.007..0.040 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
78. 0.007 0.038 ↓ 0.0 0 1

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

79. 0.001 0.031 ↓ 0.0 0 1

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

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

Seq Scan on subscription_item si_8 (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
81. 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 : 30.195 ms
Execution time : 1,799.111 ms