explain.depesz.com

PostgreSQL's explain analyze made readable

Result: u52Q

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 7,058.106 ↓ 18.0 18 1

Limit (cost=7,733.20..7,733.24 rows=1 width=97) (actual time=7,058.086..7,058.106 rows=18 loops=1)

2. 0.027 7,058.104 ↓ 18.0 18 1

GroupAggregate (cost=7,733.20..7,733.24 rows=1 width=97) (actual time=7,058.086..7,058.104 rows=18 loops=1)

  • Group Key: (timezone('PST'::text, c.provisioned_date)), d.name, ac.name
3. 0.332 7,058.077 ↓ 19.0 19 1

Sort (cost=7,733.20..7,733.21 rows=1 width=97) (actual time=7,058.077..7,058.077 rows=19 loops=1)

  • Sort Key: (timezone('PST'::text, c.provisioned_date)), d.name, ac.name
  • Sort Method: quicksort Memory: 46kB
4. 17.236 7,057.745 ↓ 153.0 153 1

Nested Loop (cost=304.34..7,733.19 rows=1 width=97) (actual time=10.037..7,057.745 rows=153 loops=1)

  • Join Filter: (c.id = ec.id)
  • Rows Removed by Join Filter: 56239
5. 0.361 21.149 ↓ 152.0 152 1

Nested Loop Left Join (cost=59.45..150.56 rows=1 width=49) (actual time=0.153..21.149 rows=152 loops=1)

6. 0.258 13.492 ↓ 152.0 152 1

Nested Loop Left Join (cost=36.55..127.63 rows=1 width=53) (actual time=0.109..13.492 rows=152 loops=1)

7. 0.284 12.322 ↓ 152.0 152 1

Nested Loop Left Join (cost=29.38..120.43 rows=1 width=53) (actual time=0.102..12.322 rows=152 loops=1)

8. 0.272 7.630 ↓ 152.0 152 1

Nested Loop Left Join (cost=2.56..93.58 rows=1 width=53) (actual time=0.073..7.630 rows=152 loops=1)

9. 0.235 3.406 ↓ 152.0 152 1

Nested Loop (cost=1.29..79.39 rows=1 width=53) (actual time=0.045..3.406 rows=152 loops=1)

10. 0.337 2.563 ↓ 152.0 152 1

Nested Loop (cost=1.14..75.68 rows=1 width=57) (actual time=0.040..2.563 rows=152 loops=1)

11. 0.278 1.618 ↓ 152.0 152 1

Nested Loop (cost=0.71..68.27 rows=1 width=39) (actual time=0.033..1.618 rows=152 loops=1)

12. 0.297 0.428 ↓ 152.0 152 1

Nested Loop (cost=0.15..60.68 rows=1 width=28) (actual time=0.023..0.428 rows=152 loops=1)

  • Join Filter: (ppc.id = c.provider_contract_id)
  • Rows Removed by Join Filter: 187
13. 0.006 0.019 ↑ 1.0 1 1

Nested Loop (cost=0.15..8.21 rows=1 width=4) (actual time=0.018..0.019 rows=1 loops=1)

  • Join Filter: (ppc.type_id = t.id)
  • Rows Removed by Join Filter: 1
14. 0.011 0.011 ↑ 1.0 1 1

Index Scan using product_type_name_key on product_type t (cost=0.15..7.17 rows=1 width=4) (actual time=0.011..0.011 rows=1 loops=1)

  • Index Cond: (name = 'postboard'::text)
15. 0.002 0.002 ↑ 1.0 2 1

Seq Scan on product_provider_contract ppc (cost=0.00..1.02 rows=2 width=8) (actual time=0.002..0.002 rows=2 loops=1)

16. 0.112 0.112 ↓ 1.0 339 1

Seq Scan on product_contract c (cost=0.00..48.32 rows=332 width=28) (actual time=0.003..0.112 rows=339 loops=1)

17. 0.912 0.912 ↑ 1.0 1 152

Index Scan using reg_domain_pkey on reg_domain d (cost=0.56..7.58 rows=1 width=19) (actual time=0.005..0.006 rows=1 loops=152)

  • Index Cond: (id = c.reg_domain_id)
18. 0.608 0.608 ↑ 1.0 1 152

Index Scan using account_market_enabled on account ac (cost=0.43..7.40 rows=1 width=26) (actual time=0.004..0.004 rows=1 loops=152)

  • Index Cond: (id = c.account_id)
19. 0.608 0.608 ↑ 1.0 1 152

Index Only Scan using product_term_pkey on product_term pt (cost=0.15..3.70 rows=1 width=4) (actual time=0.003..0.004 rows=1 loops=152)

  • Index Cond: (id = c.term_id)
  • Heap Fetches: 152
20. 0.304 3.952 ↑ 1.0 1 152

Nested Loop Left Join (cost=1.27..14.18 rows=1 width=4) (actual time=0.022..0.026 rows=1 loops=152)

21. 0.456 2.280 ↑ 1.0 1 152

Nested Loop Left Join (cost=0.71..11.38 rows=1 width=8) (actual time=0.013..0.015 rows=1 loops=152)

22. 0.456 0.456 ↑ 1.0 1 152

Index Scan using product_contract_pkey on product_contract (cost=0.15..3.79 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=152)

  • Index Cond: (id = c.id)
23. 1.368 1.368 ↑ 1.0 1 152

Index Scan using job_pkey on job j (cost=0.56..7.58 rows=1 width=8) (actual time=0.007..0.009 rows=1 loops=152)

  • Index Cond: (id = product_contract.last_job_id)
24. 1.368 1.368 ↑ 1.0 1 152

Index Only Scan using job_detail_parent_id on job_detail jd (cost=0.56..2.79 rows=1 width=4) (actual time=0.007..0.009 rows=1 loops=152)

  • Index Cond: (job_id = j.id)
  • Heap Fetches: 160
25. 1.064 4.408 ↑ 1.0 1 152

Aggregate (cost=26.82..26.83 rows=1 width=16) (actual time=0.028..0.029 rows=1 loops=152)

26. 1.682 3.344 ↑ 1.0 1 152

Hash Join (cost=7.18..26.82 rows=1 width=16) (actual time=0.021..0.022 rows=1 loops=152)

  • Hash Cond: ((pp2.account_type_id = pa2.account_type_id) AND (pp2.plan_id = pa2.product_plan_id))
27. 0.294 0.294 ↑ 183.3 3 147

Seq Scan on product_pricing pp2 (cost=0.00..15.50 rows=550 width=24) (actual time=0.001..0.002 rows=3 loops=147)

28. 0.304 1.368 ↑ 1.0 1 152

Hash (cost=7.17..7.17 rows=1 width=8) (actual time=0.009..0.009 rows=1 loops=152)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
29. 1.064 1.064 ↑ 1.0 1 152

Index Scan using product_account_contract_idx on product_account pa2 (cost=0.15..7.17 rows=1 width=8) (actual time=0.006..0.007 rows=1 loops=152)

  • Index Cond: (contract_id = c.id)
  • Filter: ((canceled_date IS NULL) AND (provisioned_date IS NOT NULL))
  • Rows Removed by Filter: 0
30. 0.456 0.912 ↑ 1.0 1 152

Aggregate (cost=7.17..7.18 rows=1 width=0) (actual time=0.006..0.006 rows=1 loops=152)

31. 0.456 0.456 ↑ 1.0 1 152

Index Scan using product_account_contract_idx on product_account pa2_1 (cost=0.15..7.17 rows=1 width=0) (actual time=0.003..0.003 rows=1 loops=152)

  • Index Cond: (contract_id = c.id)
  • Filter: ((canceled_date IS NULL) AND (provisioned_date IS NOT NULL))
  • Rows Removed by Filter: 0
32. 0.152 7.296 ↑ 1.0 1 152

Aggregate (cost=22.90..22.91 rows=1 width=32) (actual time=0.048..0.048 rows=1 loops=152)

33. 0.760 7.144 ↓ 0.0 0 152

Nested Loop (cost=7.73..22.90 rows=1 width=32) (actual time=0.047..0.047 rows=0 loops=152)

34. 0.912 5.776 ↑ 1.0 1 152

Nested Loop (cost=7.60..22.72 rows=1 width=40) (actual time=0.036..0.038 rows=1 loops=152)

35. 0.304 1.824 ↑ 1.0 1 152

Nested Loop (cost=0.30..15.37 rows=1 width=48) (actual time=0.010..0.012 rows=1 loops=152)

36. 0.304 1.216 ↑ 1.0 1 152

Nested Loop (cost=0.15..8.19 rows=1 width=12) (actual time=0.007..0.008 rows=1 loops=152)

37. 0.304 0.304 ↑ 1.0 1 152

Seq Scan on product_bundle b (cost=0.00..1.01 rows=1 width=8) (actual time=0.001..0.002 rows=1 loops=152)

38. 0.608 0.608 ↑ 1.0 1 152

Index Only Scan using product_type_pkey on product_type t_3 (cost=0.15..7.17 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=152)

  • Index Cond: (id = b.product_type_id)
  • Heap Fetches: 152
39. 0.304 0.304 ↑ 1.0 1 152

Index Scan using product_type_pkey on product_type bu (cost=0.15..7.17 rows=1 width=36) (actual time=0.001..0.002 rows=1 loops=152)

  • Index Cond: (id = b.bundle_product_type_id)
40. 2.584 3.040 ↑ 1.0 1 152

Index Scan using product_provider_contract_contract_range_type_id_excl on product_provider_contract buc (cost=7.30..7.34 rows=1 width=4) (actual time=0.020..0.020 rows=1 loops=152)

  • Index Cond: ((contract_range @> (now())::timestamp without time zone) AND (type_id = bu.id))
  • Filter: (NOT (hashed SubPlan 1))
41.          

SubPlan (forIndex Scan)

42. 0.456 0.456 ↓ 0.0 0 152

Index Scan using product_contract_parent_idx on product_contract product_contract_1 (cost=0.15..7.17 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=152)

  • Index Cond: (parent_contract_id = $2)
43. 0.608 0.608 ↓ 0.0 0 152

Index Scan using product_provider_contract_contract_range_type_id_excl on product_provider_contract c_1 (cost=0.13..0.17 rows=1 width=4) (actual time=0.004..0.004 rows=0 loops=152)

  • Index Cond: ((contract_range @> (now())::timestamp without time zone) AND (type_id = t_3.id))
  • Filter: (id = c.provider_contract_id)
  • Rows Removed by Filter: 1
44. 80.560 7,019.360 ↑ 1.0 371 152

Nested Loop Left Join (cost=244.89..7,577.88 rows=380 width=60) (actual time=0.141..46.180 rows=371 loops=152)

45. 80.864 1,412.384 ↑ 1.0 371 152

Nested Loop Left Join (cost=232.42..2,823.13 rows=380 width=64) (actual time=0.034..9.292 rows=371 loops=152)

  • Join Filter: (pau.product_account_id = pa.id)
  • Rows Removed by Join Filter: 3700
46. 36.476 1,275.128 ↑ 1.0 371 152

Hash Join (cost=225.09..2,788.70 rows=380 width=64) (actual time=0.031..8.389 rows=371 loops=152)

  • Hash Cond: (pa.account_type_id = eat.id)
47. 30.220 1,238.648 ↑ 1.0 371 152

Hash Left Join (cost=196.41..2,754.80 rows=380 width=68) (actual time=0.030..8.149 rows=371 loops=152)

  • Hash Cond: (pp.id = s.id)
48. 41.036 1,208.400 ↑ 1.0 371 152

Hash Left Join (cost=70.05..2,624.50 rows=380 width=72) (actual time=0.030..7.950 rows=371 loops=152)

  • Hash Cond: (pa.product_plan_id = pp.id)
49. 91.048 1,167.360 ↑ 1.0 371 152

Nested Loop (cost=41.37..2,590.60 rows=380 width=72) (actual time=0.029..7.680 rows=371 loops=152)

  • Join Filter: (pa.provision_status_id = eps.id)
  • Rows Removed by Join Filter: 1113
50. 74.936 1,076.312 ↑ 1.0 371 152

Merge Join (cost=41.37..2,566.75 rows=380 width=76) (actual time=0.028..7.081 rows=371 loops=152)

  • Merge Cond: (ec.id = pa.contract_id)
51. 67.944 982.984 ↓ 1.0 339 152

Nested Loop (cost=1.71..6,639.25 rows=332 width=51) (actual time=0.024..6.467 rows=339 loops=152)

52. 36.480 708.928 ↓ 1.0 339 152

Nested Loop (cost=1.28..5,635.03 rows=332 width=55) (actual time=0.019..4.664 rows=339 loops=152)

53. 76.152 466.336 ↓ 1.0 339 152

Nested Loop (cost=0.85..3,175.81 rows=332 width=55) (actual time=0.015..3.068 rows=339 loops=152)

54. 67.640 132.544 ↓ 1.0 339 152

Nested Loop (cost=0.30..656.76 rows=332 width=44) (actual time=0.009..0.872 rows=339 loops=152)

  • Join Filter: (epc.id = ec.provider_contract_id)
  • Rows Removed by Join Filter: 339
55. 64.904 64.904 ↓ 1.0 339 152

Index Scan using product_contract_pkey on product_contract ec (cost=0.15..631.41 rows=332 width=16) (actual time=0.008..0.427 rows=339 loops=152)

56. 0.000 0.000 ↑ 1.0 2 51,528

Materialize (cost=0.15..15.39 rows=2 width=36) (actual time=0.000..0.000 rows=2 loops=51,528)

57. 0.007 0.012 ↑ 1.0 2 1

Nested Loop (cost=0.15..15.38 rows=2 width=36) (actual time=0.005..0.012 rows=2 loops=1)

58. 0.001 0.001 ↑ 1.0 2 1

Seq Scan on product_provider_contract epc (cost=0.00..1.02 rows=2 width=8) (actual time=0.000..0.001 rows=2 loops=1)

59. 0.004 0.004 ↑ 1.0 1 2

Index Scan using product_type_pkey on product_type t_1 (cost=0.15..7.17 rows=1 width=36) (actual time=0.002..0.002 rows=1 loops=2)

  • Index Cond: (id = epc.type_id)
60. 257.640 257.640 ↑ 1.0 1 51,528

Index Scan using reg_domain_pkey on reg_domain d_1 (cost=0.56..7.58 rows=1 width=19) (actual time=0.005..0.005 rows=1 loops=51,528)

  • Index Cond: (id = ec.reg_domain_id)
61. 206.112 206.112 ↑ 1.0 1 51,528

Index Scan using account_market_enabled on account a (cost=0.43..7.40 rows=1 width=8) (actual time=0.003..0.004 rows=1 loops=51,528)

  • Index Cond: (id = ec.account_id)
62. 206.112 206.112 ↑ 1.0 1 51,528

Index Only Scan using user_pkey on "user" u (cost=0.43..3.01 rows=1 width=4) (actual time=0.003..0.004 rows=1 loops=51,528)

  • Index Cond: (id = a.primary_user_id)
  • Heap Fetches: 55632
63. 18.239 18.392 ↑ 1.2 371 152

Sort (cost=39.66..40.76 rows=439 width=25) (actual time=0.002..0.121 rows=371 loops=152)

  • Sort Key: pa.contract_id
  • Sort Method: quicksort Memory: 53kB
64. 0.153 0.153 ↑ 1.2 371 1

Seq Scan on product_account pa (cost=0.00..20.39 rows=439 width=25) (actual time=0.006..0.153 rows=371 loops=1)

65. 0.000 0.000 ↑ 1.0 4 56,392

Materialize (cost=0.00..1.06 rows=4 width=4) (actual time=0.000..0.000 rows=4 loops=56,392)

66. 0.004 0.004 ↑ 1.0 4 1

Seq Scan on product_provision_status eps (cost=0.00..1.04 rows=4 width=4) (actual time=0.002..0.004 rows=4 loops=1)

67. 0.000 0.004 ↑ 276.7 3 1

Hash (cost=18.30..18.30 rows=830 width=4) (actual time=0.004..0.004 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
68. 0.004 0.004 ↑ 276.7 3 1

Seq Scan on product_plan pp (cost=0.00..18.30 rows=830 width=4) (actual time=0.002..0.004 rows=3 loops=1)

69. 0.002 0.028 ↑ 183.3 3 1

Hash (cost=119.49..119.49 rows=550 width=4) (actual time=0.028..0.028 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
70. 0.004 0.026 ↑ 183.3 3 1

Hash Join (cost=81.30..119.49 rows=550 width=4) (actual time=0.023..0.026 rows=3 loops=1)

  • Hash Cond: (p.type_id = t_2.id)
71. 0.005 0.019 ↑ 183.3 3 1

Hash Join (cost=57.35..87.97 rows=550 width=8) (actual time=0.016..0.019 rows=3 loops=1)

  • Hash Cond: (p.plan_id = s.id)
72. 0.007 0.011 ↑ 183.3 3 1

Hash Join (cost=28.68..51.74 rows=550 width=8) (actual time=0.010..0.011 rows=3 loops=1)

  • Hash Cond: (p.account_type_id = it.id)
73. 0.001 0.001 ↑ 183.3 3 1

Seq Scan on product_pricing p (cost=0.00..15.50 rows=550 width=16) (actual time=0.001..0.001 rows=3 loops=1)

74. 0.002 0.003 ↑ 276.7 3 1

Hash (cost=18.30..18.30 rows=830 width=4) (actual time=0.003..0.003 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
75. 0.001 0.001 ↑ 276.7 3 1

Seq Scan on product_account_type it (cost=0.00..18.30 rows=830 width=4) (actual time=0.001..0.001 rows=3 loops=1)

76. 0.001 0.003 ↑ 276.7 3 1

Hash (cost=18.30..18.30 rows=830 width=4) (actual time=0.003..0.003 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
77. 0.002 0.002 ↑ 276.7 3 1

Seq Scan on product_plan s (cost=0.00..18.30 rows=830 width=4) (actual time=0.001..0.002 rows=3 loops=1)

78. 0.002 0.003 ↑ 310.0 2 1

Hash (cost=16.20..16.20 rows=620 width=4) (actual time=0.003..0.003 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
79. 0.001 0.001 ↑ 310.0 2 1

Seq Scan on product_type t_2 (cost=0.00..16.20 rows=620 width=4) (actual time=0.001..0.001 rows=2 loops=1)

80. 0.002 0.004 ↑ 276.7 3 1

Hash (cost=18.30..18.30 rows=830 width=4) (actual time=0.004..0.004 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
81. 0.002 0.002 ↑ 276.7 3 1

Seq Scan on product_account_type eat (cost=0.00..18.30 rows=830 width=4) (actual time=0.002..0.002 rows=3 loops=1)

82. 56.327 56.392 ↓ 10.0 10 56,392

Materialize (cost=7.33..28.73 rows=1 width=4) (actual time=0.000..0.001 rows=10 loops=56,392)

83. 0.003 0.065 ↓ 10.0 10 1

Nested Loop (cost=7.33..28.73 rows=1 width=4) (actual time=0.024..0.065 rows=10 loops=1)

84. 0.016 0.032 ↓ 10.0 10 1

Hash Join (cost=7.18..25.03 rows=1 width=8) (actual time=0.018..0.032 rows=10 loops=1)

  • Hash Cond: (pau.status_id = pus.id)
85. 0.006 0.006 ↑ 22.8 25 1

Seq Scan on product_account_update pau (cost=0.00..15.70 rows=570 width=12) (actual time=0.003..0.006 rows=25 loops=1)

86. 0.001 0.010 ↑ 1.0 1 1

Hash (cost=7.17..7.17 rows=1 width=4) (actual time=0.010..0.010 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
87. 0.009 0.009 ↑ 1.0 1 1

Index Scan using product_update_status_name_key on product_update_status pus (cost=0.15..7.17 rows=1 width=4) (actual time=0.008..0.009 rows=1 loops=1)

  • Index Cond: (name = 'pending'::text)
88. 0.030 0.030 ↑ 1.0 1 10

Index Only Scan using product_plan_pkey on product_plan plan (cost=0.15..3.69 rows=1 width=4) (actual time=0.002..0.003 rows=1 loops=10)

  • Index Cond: (id = pau.new_plan_id)
  • Heap Fetches: 10
89. 507.528 5,526.416 ↑ 1.0 1 56,392

Aggregate (cost=12.48..12.49 rows=1 width=20) (actual time=0.098..0.098 rows=1 loops=56,392)

90. 169.176 5,018.888 ↑ 2.0 1 56,392

Nested Loop (cost=0.00..12.47 rows=2 width=20) (actual time=0.088..0.089 rows=1 loops=56,392)

  • Join Filter: (paa.attribute_id = at.id)
  • Rows Removed by Join Filter: 6
91. 112.784 112.784 ↑ 1.0 6 56,392

Seq Scan on product_attribute at (cost=0.00..1.06 rows=6 width=17) (actual time=0.001..0.002 rows=6 loops=56,392)

92. 112.784 4,736.928 ↑ 2.0 1 338,352

Materialize (cost=0.00..11.24 rows=2 width=11) (actual time=0.010..0.014 rows=1 loops=338,352)

93. 4,624.144 4,624.144 ↑ 2.0 1 56,392

Seq Scan on product_account_attribute paa (cost=0.00..11.23 rows=2 width=11) (actual time=0.058..0.082 rows=1 loops=56,392)

  • Filter: (product_account_id = pa.id)
  • Rows Removed by Filter: 435