explain.depesz.com

PostgreSQL's explain analyze made readable

Result: okFD

Settings
# exclusive inclusive rows x rows loops node
1. 3.929 987.093 ↑ 1.0 484 1

Hash Join (cost=18,703.46..2,235,312.67 rows=485 width=124) (actual time=4.173..987.093 rows=484 loops=1)

  • Hash Cond: ((m.brand)::text = (top.brand)::text)
2.          

CTE managers

3. 0.357 2.197 ↑ 1.0 484 1

Nested Loop (cost=0.41..1,552.45 rows=485 width=40) (actual time=0.028..2.197 rows=484 loops=1)

4. 0.388 0.388 ↑ 1.0 484 1

Seq Scan on partners_partnermanager m_1 (cost=0.00..32.45 rows=485 width=8) (actual time=0.011..0.388 rows=484 loops=1)

  • Filter: (is_hired AND (user_id < 5000))
  • Rows Removed by Filter: 441
5. 1.452 1.452 ↑ 1.0 1 484

Index Scan using profile_userconfig_user_id_key on profile_userconfig p (cost=0.41..3.13 rows=1 width=10) (actual time=0.003..0.003 rows=1 loops=484)

  • Index Cond: (user_id = m_1.user_id)
6.          

CTE top_performer

7. 0.012 3.250 ↑ 100.0 2 1

Nested Loop Left Join (cost=94.52..17,144.51 rows=200 width=72) (actual time=2.935..3.250 rows=2 loops=1)

8. 0.002 3.228 ↑ 100.0 2 1

Nested Loop Left Join (cost=94.24..16,684.51 rows=200 width=44) (actual time=2.919..3.228 rows=2 loops=1)

9. 0.198 2.590 ↑ 100.0 2 1

HashAggregate (cost=10.91..12.91 rows=200 width=32) (actual time=2.588..2.590 rows=2 loops=1)

  • Group Key: managers.brand
10. 2.392 2.392 ↑ 1.0 484 1

CTE Scan on managers (cost=0.00..9.70 rows=485 width=32) (actual time=0.000..2.392 rows=484 loops=1)

11. 0.006 0.636 ↑ 1.0 1 2

Limit (cost=83.33..83.33 rows=1 width=12) (actual time=0.316..0.318 rows=1 loops=2)

12. 0.024 0.630 ↑ 14.0 1 2

Sort (cost=83.33..83.36 rows=14 width=12) (actual time=0.315..0.315 rows=1 loops=2)

  • Sort Key: (count(*)) DESC
  • Sort Method: top-N heapsort Memory: 25kB
13. 0.012 0.606 ↑ 1.8 8 2

GroupAggregate (cost=83.01..83.26 rows=14 width=12) (actual time=0.298..0.303 rows=8 loops=2)

  • Group Key: u_1.id
14. 0.030 0.594 ↑ 1.0 14 2

Sort (cost=83.01..83.05 rows=14 width=4) (actual time=0.296..0.297 rows=14 loops=2)

  • Sort Key: u_1.id
  • Sort Method: quicksort Memory: 26kB
15. 0.003 0.564 ↑ 1.0 14 2

Nested Loop (cost=0.98..82.74 rows=14 width=4) (actual time=0.084..0.282 rows=14 loops=2)

16. 0.036 0.456 ↓ 1.3 18 2

Nested Loop (cost=0.70..76.95 rows=14 width=4) (actual time=0.054..0.228 rows=18 loops=2)

17. 0.018 0.350 ↓ 1.3 18 2

Nested Loop (cost=0.43..72.84 rows=14 width=4) (actual time=0.046..0.175 rows=18 loops=2)

18. 0.062 0.062 ↓ 1.2 27 2

Index Only Scan using idx_reactivated_partner_stat_data on partners_partnerstatdata sd (cost=0.14..19.48 rows=23 width=4) (actual time=0.011..0.031 rows=27 loops=2)

  • Heap Fetches: 34
19. 0.270 0.270 ↑ 1.0 1 54

Index Scan using idx_account_partner_contract on partners_partnercontract c (cost=0.29..2.31 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=54)

  • Index Cond: (partner_id = sd.partner_id)
  • Filter: ((brand)::text = (managers.brand)::text)
  • Rows Removed by Filter: 1
20. 0.070 0.070 ↑ 1.0 1 35

Index Scan using partners_partnermanager_pkey on partners_partnermanager manager (cost=0.28..0.29 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=35)

  • Index Cond: (id = c.account_manager_id)
21. 0.105 0.105 ↑ 1.0 1 35

Index Only Scan using auth_user_pkey on auth_user u_1 (cost=0.28..0.41 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=35)

  • Index Cond: (id = manager.user_id)
  • Filter: (id <> 3)
  • Rows Removed by Filter: 0
  • Heap Fetches: 0
22. 0.010 0.010 ↑ 1.0 1 2

Index Scan using auth_user_pkey on auth_user u (cost=0.28..2.30 rows=1 width=20) (actual time=0.005..0.005 rows=1 loops=2)

  • Index Cond: (id = u_1.id)
23. 0.290 0.290 ↑ 1.0 484 1

CTE Scan on managers m (cost=0.00..9.70 rows=485 width=40) (actual time=0.029..0.290 rows=484 loops=1)

24. 0.005 3.258 ↑ 100.0 2 1

Hash (cost=4.00..4.00 rows=200 width=72) (actual time=3.258..3.258 rows=2 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
25. 3.253 3.253 ↑ 100.0 2 1

CTE Scan on top_performer top (cost=0.00..4.00 rows=200 width=72) (actual time=2.937..3.253 rows=2 loops=1)

26.          

SubPlan (for Hash Join)

27. 8.228 225.544 ↑ 1.0 1 484

Aggregate (cost=1,269.02..1,269.03 rows=1 width=32) (actual time=0.466..0.466 rows=1 loops=484)

28. 40.651 217.316 ↑ 5.4 85 484

Nested Loop (cost=0.70..1,267.87 rows=461 width=6) (actual time=0.008..0.449 rows=85 loops=484)

29. 52.756 52.756 ↑ 5.4 85 484

Index Scan using partners_partnercontract_6bc80cbd on partners_partnercontract c_1 (cost=0.41..456.80 rows=462 width=4) (actual time=0.005..0.109 rows=85 loops=484)

  • Index Cond: (account_manager_id = m.manager_id)
30. 123.909 123.909 ↑ 1.0 1 41,303

Index Scan using partners_partnercontractstatdata_pkey on partners_partnercontractstatdata sd_1 (cost=0.29..1.76 rows=1 width=10) (actual time=0.003..0.003 rows=1 loops=41,303)

  • Index Cond: (contract_id = c_1.id)
31. 0.484 84.216 ↑ 1.0 1 484

Aggregate (cost=103.20..103.21 rows=1 width=8) (actual time=0.174..0.174 rows=1 loops=484)

32. 0.000 83.732 ↓ 0.0 0 484

Nested Loop (cost=0.72..103.14 rows=23 width=0) (actual time=0.168..0.173 rows=0 loops=484)

33. 10.164 45.496 ↓ 1.2 27 484

Nested Loop (cost=0.43..49.55 rows=23 width=4) (actual time=0.007..0.094 rows=27 loops=484)

34. 9.196 9.196 ↓ 1.2 27 484

Index Only Scan using idx_reactivated_partner_stat_data on partners_partnerstatdata sd_2 (cost=0.14..19.48 rows=23 width=4) (actual time=0.003..0.019 rows=27 loops=484)

  • Heap Fetches: 8,228
35. 26.136 26.136 ↑ 1.0 1 13,068

Index Only Scan using partners_partner_pkey on partners_partner p_1 (cost=0.29..1.31 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=13,068)

  • Index Cond: (id = sd_2.partner_id)
  • Heap Fetches: 2,904
36. 13.068 39.204 ↓ 0.0 0 13,068

Limit (cost=0.29..2.31 rows=1 width=18,493) (actual time=0.003..0.003 rows=0 loops=13,068)

37. 26.136 26.136 ↓ 0.0 0 13,068

Index Only Scan using idx_account_partner_contract on partners_partnercontract c_2 (cost=0.29..2.31 rows=1 width=18,493) (actual time=0.002..0.002 rows=0 loops=13,068)

  • Index Cond: ((partner_id = p_1.id) AND (account_manager_id = m.manager_id))
  • Heap Fetches: 22
38. 0.968 78.408 ↑ 1.0 1 484

Aggregate (cost=461.44..461.45 rows=1 width=8) (actual time=0.162..0.162 rows=1 loops=484)

39. 77.440 77.440 ↑ 4.0 2 484

Index Scan using partners_partnercontract_6bc80cbd on partners_partnercontract c_3 (cost=0.41..461.42 rows=8 width=0) (actual time=0.028..0.160 rows=2 loops=484)

  • Index Cond: (account_manager_id = m.manager_id)
  • Filter: (((punishment_rules)::text ~~ '%%rule_credit_limit_is_zero_for_mpb_calculation%%'::text) OR ((punishment_rules)::text ~~ '%%rule_cant_reserve_any_deposit_rates%%'::text) OR (((punishment_rules)::text ~~ '%%rule_cant_reserve_any_deposit_rates%%'::text) AND ((punishment_rules)::text ~~ '%%rule_cant_reserve_payment_type_now_rates%%'::text)))
  • Rows Removed by Filter: 84
40. 0.484 187.792 ↑ 1.0 1 484

Aggregate (cost=1,021.88..1,021.89 rows=1 width=8) (actual time=0.388..0.388 rows=1 loops=484)

41. 13.547 187.308 ↑ 3.0 1 484

Nested Loop (cost=0.70..1,021.87 rows=3 width=0) (actual time=0.155..0.387 rows=1 loops=484)

42. 49.852 49.852 ↑ 5.4 85 484

Index Scan using partners_partnercontract_6bc80cbd on partners_partnercontract c_4 (cost=0.41..456.80 rows=462 width=4) (actual time=0.004..0.103 rows=85 loops=484)

  • Index Cond: (account_manager_id = m.manager_id)
43. 123.909 123.909 ↓ 0.0 0 41,303

Index Scan using partners_partnercreditrisk_contract_id_key on partners_partnercreditrisk cr (cost=0.29..1.22 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=41,303)

  • Index Cond: (contract_id = c_4.id)
  • Filter: (due_30_days IS NOT NULL)
  • Rows Removed by Filter: 1
44. 0.484 64.856 ↑ 1.0 1 484

Aggregate (cost=700.55..700.56 rows=1 width=8) (actual time=0.134..0.134 rows=1 loops=484)

45. 8.479 64.372 ↑ 6.0 1 484

Hash Join (cost=242.94..700.54 rows=6 width=0) (actual time=0.024..0.133 rows=1 loops=484)

  • Hash Cond: (c_5.partner_id = sd_3.partner_id)
46. 55.176 55.176 ↑ 5.4 85 484

Index Scan using partners_partnercontract_6bc80cbd on partners_partnercontract c_5 (cost=0.41..456.80 rows=462 width=4) (actual time=0.004..0.114 rows=85 loops=484)

  • Index Cond: (account_manager_id = m.manager_id)
47. 0.144 0.717 ↓ 1.0 444 1

Hash (cost=236.99..236.99 rows=443 width=4) (actual time=0.717..0.717 rows=444 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 24kB
48. 0.573 0.573 ↓ 1.0 444 1

Index Only Scan using idx_avg_cancellation_percent_month_partner_stat_data on partners_partnerstatdata sd_3 (cost=0.28..236.99 rows=443 width=4) (actual time=0.038..0.573 rows=444 loops=1)

  • Index Cond: (avg_cancellation_percent_month >= '55'::numeric)
  • Heap Fetches: 338
49. 0.484 185.856 ↑ 1.0 1 484

Aggregate (cost=175.50..175.51 rows=1 width=8) (actual time=0.384..0.384 rows=1 loops=484)

50. 28.072 185.372 ↓ 0.0 0 484

Nested Loop (cost=0.56..175.50 rows=1 width=0) (actual time=0.366..0.383 rows=0 loops=484)

51. 35.332 35.332 ↓ 1.1 84 484

Index Only Scan using idx_credit_limit_remain_share_contract_credit_risk on partners_partnercreditrisk cr_1 (cost=0.27..6.62 rows=77 width=4) (actual time=0.003..0.073 rows=84 loops=484)

  • Index Cond: (credit_limit_remain_share <= 30)
  • Heap Fetches: 40,656
52. 121.968 121.968 ↓ 0.0 0 40,656

Index Scan using partners_partnercontract_pkey on partners_partnercontract c_6 (cost=0.29..2.19 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=40,656)

  • Index Cond: (id = cr_1.contract_id)
  • Filter: (account_manager_id = m.manager_id)
  • Rows Removed by Filter: 1
53. 1.936 152.944 ↑ 1.0 1 484

Aggregate (cost=838.60..838.61 rows=1 width=8) (actual time=0.316..0.316 rows=1 loops=484)

54. 8.386 151.008 ↑ 5.3 15 484

Nested Loop (cost=0.70..838.40 rows=80 width=0) (actual time=0.015..0.312 rows=15 loops=484)

55. 60.016 60.016 ↑ 5.4 85 484

Index Scan using partners_partnercontract_6bc80cbd on partners_partnercontract c_7 (cost=0.41..456.80 rows=462 width=4) (actual time=0.004..0.124 rows=85 loops=484)

  • Index Cond: (account_manager_id = m.manager_id)
56. 82.606 82.606 ↓ 0.0 0 41,303

Index Only Scan using idx_partner_to_reactivate on partners_partnerstatdata sd_4 (cost=0.28..0.83 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=41,303)

  • Index Cond: (partner_id = c_7.partner_id)
  • Heap Fetches: 5,698
Planning time : 5.209 ms
Execution time : 987.769 ms