explain.depesz.com

PostgreSQL's explain analyze made readable

Result: yHn8

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

Limit (cost=16,144.05..16,150.35 rows=45 width=3,049) (actual time=4,318.277..4,318.326 rows=45 loops=1)

2. 0.040 4,318.321 ↑ 119.8 45 1

Unique (cost=16,144.05..16,898.51 rows=5,389 width=3,049) (actual time=4,318.276..4,318.321 rows=45 loops=1)

3. 8.026 4,318.281 ↑ 119.8 45 1

Sort (cost=16,144.05..16,157.52 rows=5,389 width=3,049) (actual time=4,318.275..4,318.281 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,319.219 4,310.255 ↑ 1.0 5,389 1

Nested Loop Left Join (cost=11,095.86..11,654.70 rows=5,389 width=3,049) (actual time=468.383..4,310.255 rows=5,389 loops=1)

  • Join Filter: (simcard0_.profile_id = q.id)
  • Rows Removed by Join Filter: 29,041,321
5. 2.495 2.495 ↑ 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.017..2.495 rows=5,389 loops=1)

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

Materialize (cost=11,095.86..11,101.49 rows=1 width=212) (actual time=0.071..0.369 rows=5,390 loops=5,389)

7. 7.974 490.611 ↓ 5,390.0 5,390 1

Subquery Scan on q (cost=11,095.86..11,101.49 rows=1 width=212) (actual time=379.801..490.611 rows=5,390 loops=1)

  • Filter: (q.rank = 1)
  • Rows Removed by Filter: 71,484
8. 89.011 482.637 ↓ 615.0 76,874 1

WindowAgg (cost=11,095.86..11,099.92 rows=125 width=244) (actual time=379.799..482.637 rows=76,874 loops=1)

9. 69.293 393.626 ↓ 615.0 76,874 1

Sort (cost=11,095.86..11,096.17 rows=125 width=176) (actual time=379.785..393.626 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
10. 28.726 324.333 ↓ 615.0 76,874 1

Hash Left Join (cost=10,086.28..11,091.51 rows=125 width=176) (actual time=242.573..324.333 rows=76,874 loops=1)

  • Hash Cond: (bsi.subscription_item_id = bsil.subscription_item_id)
11. 22.593 294.611 ↓ 615.0 76,874 1

Hash Left Join (cost=5,930.13..6,934.40 rows=125 width=128) (actual time=241.568..294.611 rows=76,874 loops=1)

  • Hash Cond: (bbpsi.subscription_item_id = bsi.subscription_item_id)
12. 30.472 271.970 ↓ 615.0 76,874 1

Hash Right Join (cost=5,924.98..6,928.91 rows=125 width=128) (actual time=241.511..271.970 rows=76,874 loops=1)

  • Hash Cond: (ptmn.bm_sim_subscription_item_id = sim_sub.sim_subscription_id)
13. 0.046 0.046 ↓ 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.045..0.046 rows=0 loops=1)

14. 28.876 241.452 ↓ 615.0 76,874 1

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

  • Buckets: 32,768 (originally 1024) Batches: 4 (originally 1) Memory Usage: 3,841kB
15. 31.740 212.576 ↓ 615.0 76,874 1

Hash Right Join (cost=4,894.49..5,898.42 rows=125 width=128) (actual time=39.261..212.576 rows=76,874 loops=1)

  • Hash Cond: (ptm.bm_sim_subscription_item_id = sim_sub.sim_subscription_id)
16. 141.737 141.737 ↓ 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.139..141.737 rows=77,452 loops=1)

17. 1.884 39.099 ↓ 43.2 5,399 1

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

  • Buckets: 8,192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 898kB
18. 1.635 37.215 ↓ 43.2 5,399 1

Hash Join (cost=4,383.89..4,867.92 rows=125 width=116) (actual time=28.010..37.215 rows=5,399 loops=1)

  • Hash Cond: (bpsi.subscription_item_id = si.subscription_item_id)
19. 1.975 35.534 ↓ 32.9 5,399 1

Hash Join (cost=4,378.74..4,862.33 rows=164 width=132) (actual time=27.949..35.534 rows=5,399 loops=1)

  • Hash Cond: (bpsi.subscription_item_id = sil.subscription_item_id)
20. 1.933 32.385 ↓ 17.0 5,399 1

Hash Left Join (cost=222.59..703.35 rows=317 width=60) (actual time=26.759..32.385 rows=5,399 loops=1)

  • Hash Cond: (sub.base_account_subscription_item_id = basi.account_subscription_item_id)
21. 2.712 30.255 ↓ 17.0 5,399 1

Hash Right Join (cost=209.87..689.31 rows=317 width=48) (actual time=26.542..30.255 rows=5,399 loops=1)

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

23. 1.806 26.520 ↓ 17.0 5,399 1

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

  • Buckets: 8,192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 613kB
24. 1.438 24.714 ↓ 17.0 5,399 1

Merge Join (cost=1.27..205.91 rows=317 width=64) (actual time=0.078..24.714 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.919 23.276 ↓ 17.0 5,399 1

Nested Loop (cost=1.12..501.52 rows=317 width=64) (actual time=0.067..23.276 rows=5,399 loops=1)

26. 9.804 12.958 ↓ 17.0 5,399 1

Nested Loop (cost=0.71..242.32 rows=317 width=44) (actual time=0.051..12.958 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. 3.154 3.154 ↓ 22.2 5,525 1

Nested Loop (cost=0.43..158.65 rows=249 width=36) (actual time=0.034..3.154 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..20.49 rows=43 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)
28. 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)
29. 0.024 0.197 ↓ 20.3 142 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 16kB
30. 0.063 0.173 ↓ 20.3 142 1

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

  • Hash Cond: (bbpsi.billing_profile_subscription_item_id = basi.billing_profile_subscription_item_id)
31. 0.016 0.016 ↓ 1.4 149 1

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

32. 0.025 0.094 ↓ 20.3 142 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 16kB
33. 0.069 0.069 ↓ 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.069 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
34. 0.029 1.174 ↓ 2.7 148 1

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

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

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

36. 0.127 1.122 ↓ 2.7 148 1

HashAggregate (cost=4,154.36..4,154.91 rows=55 width=144) (actual time=1.085..1.122 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 ((
37. 0.014 0.995 ↓ 2.7 148 1

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

38. 0.120 0.774 ↓ 3.9 136 1

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

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

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

40. 0.023 0.085 ↓ 19.4 136 1

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

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

  • Filter: ((subscription_item_type)::text = ANY ('{PRICEPLAN-POSTPAID-INDIVIDUAL,PRICEPLAN-PREPAID-INDIVIDUAL,PRICEPLAN-POSTPAID-FLEX,
  • Rows Removed by Filter: 12
42. 0.000 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_1.id = si_2.detail_id)
43. 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
44. 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)

45. 0.003 0.146 ↓ 1.2 12 1

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

46. 0.099 0.143 ↓ 1.2 12 1

Hash Right Join (cost=29.47..1,033.50 rows=10 width=144) (actual time=0.124..0.143 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.062..0.070 rows=12 loops=
47. 0.004 0.044 ↓ 6.0 12 1

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

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

Seq Scan on subscription_item si_3 (cost=0.00..4.45 rows=2 width=20) (actual time=0.006..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
49. 0.000 0.030 ↓ 0.0 0 1

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

50. 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)
51. 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
52. 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)

53. 0.022 0.046 ↓ 1.1 148 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 14kB
54. 0.024 0.024 ↓ 1.1 148 1

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

55. 0.019 0.048 ↓ 1.1 148 1

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

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

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

57. 0.026 0.996 ↓ 2.7 148 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 25kB
58. 0.024 0.970 ↓ 2.7 148 1

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

59. 0.130 0.946 ↓ 2.7 148 1

HashAggregate (cost=4,154.36..4,154.91 rows=55 width=144) (actual time=0.909..0.946 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
60. 0.014 0.816 ↓ 2.7 148 1

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

61. 0.110 0.615 ↓ 3.9 136 1

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

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

63. 0.020 0.082 ↓ 19.4 136 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 16kB
64. 0.062 0.062 ↓ 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.062 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
65. 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)
66. 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
67. 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)

68. 0.003 0.125 ↓ 1.2 12 1

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

69. 0.016 0.122 ↓ 1.2 12 1

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

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

71. 0.002 0.044 ↓ 6.0 12 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
72. 0.042 0.042 ↓ 6.0 12 1

Seq Scan on subscription_item si_7 (cost=0.00..4.45 rows=2 width=20) (actual time=0.008..0.042 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
73. 0.000 0.031 ↓ 0.0 0 1

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

74. 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.030..0.031 rows=0 loops=1)

  • Join Filter: (a_1.id = si_8.detail_id)
75. 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
76. 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 : 6.031 ms
Execution time : 4,459.460 ms