explain.depesz.com

PostgreSQL's explain analyze made readable

Result: JFcy8

Settings
# exclusive inclusive rows x rows loops node
1. 4.958 101.643 ↑ 1.0 61 1

CTE Scan on managers m (cost=236.31..296,559.72 rows=63 width=84) (actual time=0.115..101.643 rows=61 loops=1)

2.          

CTE managers

3. 0.167 0.606 ↑ 1.0 61 1

Nested Loop (cost=0.68..236.31 rows=63 width=36) (actual time=0.020..0.606 rows=61 loops=1)

4. 0.195 0.195 ↑ 1.0 61 1

Index Scan using partners_partnermanager_e8701ad4 on partners_partnermanager m_1 (cost=0.28..22.32 rows=63 width=8) (actual time=0.013..0.195 rows=61 loops=1)

  • Index Cond: (user_id < 500)
  • Filter: is_hired
  • Rows Removed by Filter: 55
5. 0.244 0.244 ↑ 1.0 1 61

Index Scan using profile_userconfig_user_id_key on profile_userconfig p (cost=0.41..3.39 rows=1 width=10) (actual time=0.004..0.004 rows=1 loops=61)

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

SubPlan (forCTE Scan)

7. 0.061 10.675 ↑ 1.0 1 61

Aggregate (cost=482.83..482.84 rows=1 width=8) (actual time=0.175..0.175 rows=1 loops=61)

8. 1.462 10.614 ↓ 0.0 0 61

Hash Join (cost=27.29..482.81 rows=8 width=4) (actual time=0.174..0.174 rows=0 loops=61)

  • Hash Cond: (c.id = sd.contract_id)
9. 9.150 9.150 ↑ 3.5 134 61

Index Scan using partners_partnercontract_6bc80cbd on partners_partnercontract c (cost=0.41..454.71 rows=468 width=4) (actual time=0.006..0.150 rows=134 loops=61)

  • Index Cond: (account_manager_id = m.id)
10. 0.000 0.002 ↓ 0.0 0 1

Hash (cost=17.50..17.50 rows=750 width=8) (actual time=0.002..0.002 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
11. 0.002 0.002 ↓ 0.0 0 1

Seq Scan on partners_partnercontractstatdata sd (cost=0.00..17.50 rows=750 width=8) (actual time=0.002..0.002 rows=0 loops=1)

12. 0.122 0.183 ↑ 1.0 1 61

Aggregate (cost=1,191.20..1,191.21 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=61)

13. 0.061 0.061 ↓ 0.0 0 61

Nested Loop (cost=0.58..1,190.28 rows=370 width=0) (actual time=0.001..0.001 rows=0 loops=61)

14. 0.000 0.000 ↓ 0.0 0 61

Nested Loop (cost=0.29..328.18 rows=370 width=4) (actual time=0.000..0.000 rows=0 loops=61)

15. 0.000 0.000 ↓ 0.0 0 61

Seq Scan on partners_partnerstatdata sd_1 (cost=0.00..17.40 rows=370 width=4) (actual time=0.000..0.000 rows=0 loops=61)

  • Filter: is_reactivated
16. 0.000 0.000 ↓ 0.0 0

Index Only Scan using partners_partner_pkey on partners_partner p_1 (cost=0.29..0.84 rows=1 width=4) (never executed)

  • Index Cond: (id = sd_1.partner_id)
  • Heap Fetches: 0
17. 0.000 0.000 ↓ 0.0 0

Limit (cost=0.29..2.31 rows=1 width=18,493) (never executed)

18. 0.000 0.000 ↓ 0.0 0

Index Scan using partners_partnercontract_4e98b6eb on partners_partnercontract c_1 (cost=0.29..2.31 rows=1 width=18,493) (never executed)

  • Index Cond: (partner_id = p_1.id)
  • Filter: (account_manager_id = m.id)
19. 0.061 0.488 ↓ 0.0 0 61

Nested Loop (cost=510.37..512.99 rows=1 width=32) (actual time=0.008..0.008 rows=0 loops=61)

20. 0.000 0.427 ↓ 0.0 0 61

Nested Loop (cost=510.09..512.12 rows=1 width=4) (actual time=0.007..0.007 rows=0 loops=61)

21. 0.061 0.427 ↓ 0.0 0 61

Limit (cost=509.81..509.82 rows=1 width=12) (actual time=0.007..0.007 rows=0 loops=61)

22. 0.244 0.366 ↓ 0.0 0 61

Sort (cost=509.81..510.04 rows=90 width=12) (actual time=0.006..0.006 rows=0 loops=61)

  • Sort Key: (count(*)) DESC
  • Sort Method: quicksort Memory: 25kB
23. 0.122 0.122 ↓ 0.0 0 61

HashAggregate (cost=508.46..509.36 rows=90 width=12) (actual time=0.002..0.002 rows=0 loops=61)

  • Group Key: c_2.account_manager_id
24. 0.000 0.000 ↓ 0.0 0 61

Nested Loop (cost=0.58..507.32 rows=229 width=4) (actual time=0.000..0.000 rows=0 loops=61)

25. 0.000 0.000 ↓ 0.0 0 61

Nested Loop (cost=0.29..328.18 rows=370 width=8) (actual time=0.000..0.000 rows=0 loops=61)

26. 0.000 0.000 ↓ 0.0 0 61

Seq Scan on partners_partnerstatdata sd_2 (cost=0.00..17.40 rows=370 width=4) (actual time=0.000..0.000 rows=0 loops=61)

  • Filter: is_reactivated
27. 0.000 0.000 ↓ 0.0 0

Index Only Scan using partners_partner_pkey on partners_partner p_2 (cost=0.29..0.84 rows=1 width=4) (never executed)

  • Index Cond: (id = sd_2.partner_id)
  • Heap Fetches: 0
28. 0.000 0.000 ↓ 0.0 0

Index Scan using partners_partnercontract_4e98b6eb on partners_partnercontract c_2 (cost=0.29..0.47 rows=1 width=8) (never executed)

  • Index Cond: (partner_id = p_2.id)
  • Filter: ((brand)::text = (m.brand)::text)
29. 0.000 0.000 ↓ 0.0 0

Index Scan using partners_partnermanager_pkey on partners_partnermanager (cost=0.28..2.29 rows=1 width=8) (never executed)

  • Index Cond: (id = c_2.account_manager_id)
30. 0.000 0.000 ↓ 0.0 0

Index Scan using auth_user_pkey on auth_user u (cost=0.28..0.86 rows=1 width=20) (never executed)

  • Index Cond: (id = partners_partnermanager.user_id)
31. 0.122 0.427 ↑ 1.0 1 61

Aggregate (cost=5.35..5.36 rows=1 width=8) (actual time=0.007..0.007 rows=1 loops=61)

32. 0.305 0.305 ↑ 3.5 2 61

Index Only Scan using idx_contract_punishment_rules_account on partners_partnercontract c_3 (cost=0.28..5.33 rows=7 width=0) (actual time=0.004..0.005 rows=2 loops=61)

  • Index Cond: (account_manager_id = m.id)
  • Heap Fetches: 85
33. 0.061 39.711 ↑ 1.0 1 61

Aggregate (cost=1,015.63..1,015.64 rows=1 width=8) (actual time=0.651..0.651 rows=1 loops=61)

34. 5.805 39.650 ↑ 2.0 1 61

Nested Loop (cost=0.70..1,015.63 rows=2 width=0) (actual time=0.229..0.650 rows=1 loops=61)

35. 9.089 9.089 ↑ 3.5 135 61

Index Scan using partners_partnercontract_6bc80cbd on partners_partnercontract c_4 (cost=0.41..454.71 rows=468 width=4) (actual time=0.004..0.149 rows=135 loops=61)

  • Index Cond: (account_manager_id = m.id)
36. 24.756 24.756 ↓ 0.0 0 8,252

Index Scan using partners_partnercreditrisk_contract_id_key on partners_partnercreditrisk cr (cost=0.29..1.20 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=8,252)

  • Index Cond: (contract_id = c_4.id)
  • Filter: (due_30_days IS NOT NULL)
  • Rows Removed by Filter: 1
37. 0.061 12.871 ↑ 1.0 1 61

Aggregate (cost=478.29..478.30 rows=1 width=8) (actual time=0.211..0.211 rows=1 loops=61)

38. 1.522 12.810 ↓ 0.0 0 61

Hash Join (cost=22.75..478.28 rows=4 width=0) (actual time=0.210..0.210 rows=0 loops=61)

  • Hash Cond: (c_5.partner_id = sd_3.partner_id)
39. 11.285 11.285 ↑ 3.5 134 61

Index Scan using partners_partnercontract_6bc80cbd on partners_partnercontract c_5 (cost=0.41..454.71 rows=468 width=4) (actual time=0.004..0.185 rows=134 loops=61)

  • Index Cond: (account_manager_id = m.id)
40. 0.000 0.003 ↓ 0.0 0 1

Hash (cost=19.25..19.25 rows=247 width=4) (actual time=0.003..0.003 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
41. 0.003 0.003 ↓ 0.0 0 1

Seq Scan on partners_partnerstatdata sd_3 (cost=0.00..19.25 rows=247 width=4) (actual time=0.003..0.003 rows=0 loops=1)

  • Filter: (avg_cancellation_percent_month >= 55)
42. 0.061 32.330 ↑ 1.0 1 61

Aggregate (cost=1,017.18..1,017.19 rows=1 width=8) (actual time=0.530..0.530 rows=1 loops=61)

43. 0.000 32.269 ↓ 0.0 0 61

Nested Loop (cost=0.70..1,016.79 rows=154 width=0) (actual time=0.265..0.529 rows=0 loops=61)

44. 7.991 7.991 ↑ 3.5 135 61

Index Scan using partners_partnercontract_6bc80cbd on partners_partnercontract c_6 (cost=0.41..454.71 rows=468 width=4) (actual time=0.004..0.131 rows=135 loops=61)

  • Index Cond: (account_manager_id = m.id)
45. 24.756 24.756 ↓ 0.0 0 8,252

Index Scan using partners_partnercreditrisk_contract_id_key on partners_partnercreditrisk cr_1 (cost=0.29..1.20 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=8,252)

  • Index Cond: (contract_id = c_6.id)
  • Filter: (credit_limit_remain_share <= 30)
  • Rows Removed by Filter: 1
Planning time : 3.742 ms
Execution time : 101.983 ms