explain.depesz.com

PostgreSQL's explain analyze made readable

Result: qEl

Settings
# exclusive inclusive rows x rows loops node
1. 2.232 412.647 ↑ 1.0 458 1

Hash Join (cost=104,085.61..2,023,376.83 rows=458 width=84) (actual time=2.726..412.647 rows=458 loops=1)

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

CTE managers

3. 0.441 2.175 ↑ 1.0 458 1

Nested Loop (cost=0.41..1,466.52 rows=458 width=40) (actual time=0.031..2.175 rows=458 loops=1)

4. 0.360 0.360 ↑ 1.0 458 1

Seq Scan on partners_partnermanager m_1 (cost=0.00..29.90 rows=458 width=8) (actual time=0.011..0.360 rows=458 loops=1)

  • Filter: (is_hired AND (user_id < 5000))
  • Rows Removed by Filter: 415
5. 1.374 1.374 ↑ 1.0 1 458

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

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

CTE top_performer

7. 0.003 2.607 ↑ 100.0 2 1

Subquery Scan on m_2 (cost=10.30..102,612.59 rows=200 width=64) (actual time=2.597..2.607 rows=2 loops=1)

8. 0.183 2.572 ↑ 100.0 2 1

HashAggregate (cost=10.30..12.30 rows=200 width=32) (actual time=2.570..2.572 rows=2 loops=1)

  • Group Key: managers.brand
9. 2.389 2.389 ↑ 1.0 458 1

CTE Scan on managers (cost=0.00..9.16 rows=458 width=32) (actual time=0.000..2.389 rows=458 loops=1)

10.          

SubPlan (forSubquery Scan)

11. 0.002 0.032 ↓ 0.0 0 2

Limit (cost=510.37..512.99 rows=1 width=32) (actual time=0.016..0.016 rows=0 loops=2)

12. 0.002 0.030 ↓ 0.0 0 2

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

13. 0.000 0.028 ↓ 0.0 0 2

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

14. 0.002 0.028 ↓ 0.0 0 2

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

15. 0.018 0.026 ↓ 0.0 0 2

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

  • Sort Key: (count(*)) DESC
  • Sort Method: quicksort Memory: 25kB
16. 0.004 0.008 ↓ 0.0 0 2

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

  • Group Key: c.account_manager_id
17. 0.000 0.004 ↓ 0.0 0 2

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

18. 0.002 0.004 ↓ 0.0 0 2

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

19. 0.002 0.002 ↓ 0.0 0 2

Seq Scan on partners_partnerstatdata sd (cost=0.00..17.40 rows=370 width=4) (actual time=0.001..0.001 rows=0 loops=2)

  • Filter: is_reactivated
20. 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.partner_id)
  • Heap Fetches: 0
21. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: (partner_id = p_1.id)
  • Filter: ((brand)::text = (m_2.brand)::text)
22. 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.account_manager_id)
23. 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)
24. 0.180 0.180 ↑ 1.0 458 1

CTE Scan on managers m (cost=0.00..9.16 rows=458 width=40) (actual time=0.033..0.180 rows=458 loops=1)

25. 0.005 2.615 ↑ 100.0 2 1

Hash (cost=4.00..4.00 rows=200 width=64) (actual time=2.614..2.615 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
26. 2.610 2.610 ↑ 100.0 2 1

CTE Scan on top_performer top (cost=0.00..4.00 rows=200 width=64) (actual time=2.598..2.610 rows=2 loops=1)

27.          

SubPlan (forHash Join)

28. 0.458 50.838 ↑ 1.0 1 458

Aggregate (cost=482.83..482.84 rows=1 width=8) (actual time=0.111..0.111 rows=1 loops=458)

29. 7.326 50.380 ↓ 0.0 0 458

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

  • Hash Cond: (c_1.id = sd_1.contract_id)
30. 43.052 43.052 ↑ 5.1 91 458

Index Scan using partners_partnercontract_6bc80cbd on partners_partnercontract c_1 (cost=0.41..454.71 rows=468 width=4) (actual time=0.004..0.094 rows=91 loops=458)

  • Index Cond: (account_manager_id = m.manager_id)
31. 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
32. 0.002 0.002 ↓ 0.0 0 1

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

33. 0.916 0.916 ↑ 1.0 1 458

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

34. 0.000 0.000 ↓ 0.0 0 458

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

35. 0.000 0.000 ↓ 0.0 0 458

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

36. 0.000 0.000 ↓ 0.0 0 458

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=458)

  • Filter: is_reactivated
37. 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
38. 0.000 0.000 ↓ 0.0 0

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

39. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: (partner_id = p_2.id)
  • Filter: (account_manager_id = m.manager_id)
40. 0.458 1.832 ↑ 1.0 1 458

Aggregate (cost=5.35..5.36 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=458)

41. 1.374 1.374 ↑ 3.5 2 458

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.002..0.003 rows=2 loops=458)

  • Index Cond: (account_manager_id = m.manager_id)
  • Heap Fetches: 496
42. 0.458 151.140 ↑ 1.0 1 458

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

43. 32.440 150.682 ↓ 0.0 0 458

Nested Loop (cost=0.70..1,015.63 rows=2 width=0) (actual time=0.120..0.329 rows=0 loops=458)

44. 34.350 34.350 ↑ 5.1 92 458

Index Scan using partners_partnercontract_6bc80cbd on partners_partnercontract c_4 (cost=0.41..454.71 rows=468 width=4) (actual time=0.003..0.075 rows=92 loops=458)

  • Index Cond: (account_manager_id = m.manager_id)
45. 83.892 83.892 ↓ 0.0 0 41,946

Index Scan using partners_partnercreditrisk_contract_id_key on partners_partnercreditrisk cr (cost=0.29..1.20 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=41,946)

  • Index Cond: (contract_id = c_4.id)
  • Filter: (due_30_days IS NOT NULL)
  • Rows Removed by Filter: 1
46. 0.458 57.708 ↑ 1.0 1 458

Aggregate (cost=478.29..478.30 rows=1 width=8) (actual time=0.125..0.126 rows=1 loops=458)

47. 7.325 57.250 ↓ 0.0 0 458

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

  • Hash Cond: (c_5.partner_id = sd_3.partner_id)
48. 49.922 49.922 ↑ 5.1 91 458

Index Scan using partners_partnercontract_6bc80cbd on partners_partnercontract c_5 (cost=0.41..454.71 rows=468 width=4) (actual time=0.003..0.109 rows=91 loops=458)

  • Index Cond: (account_manager_id = m.manager_id)
49. 0.001 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
50. 0.002 0.002 ↓ 0.0 0 1

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

  • Filter: (avg_cancellation_percent_month >= 55)
51. 0.000 145.186 ↑ 1.0 1 458

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

52. 30.150 145.186 ↓ 0.0 0 458

Nested Loop (cost=0.70..1,016.79 rows=154 width=0) (actual time=0.191..0.317 rows=0 loops=458)

53. 31.144 31.144 ↑ 5.1 92 458

Index Scan using partners_partnercontract_6bc80cbd on partners_partnercontract c_6 (cost=0.41..454.71 rows=468 width=4) (actual time=0.003..0.068 rows=92 loops=458)

  • Index Cond: (account_manager_id = m.manager_id)
54. 83.892 83.892 ↓ 0.0 0 41,946

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.002..0.002 rows=0 loops=41,946)

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