explain.depesz.com

PostgreSQL's explain analyze made readable

Result: D4bd

Settings
# exclusive inclusive rows x rows loops node
1. 10.213 4,222.675 ↑ 1.0 457 1

Hash Join (cost=138,689.80..2,514,441.92 rows=458 width=108) (actual time=33.191..4,222.675 rows=457 loops=1)

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

CTE managers

3. 0.545 5.519 ↑ 1.0 457 1

Nested Loop (cost=0.41..1,104.46 rows=458 width=40) (actual time=0.026..5.519 rows=457 loops=1)

4. 0.404 0.404 ↑ 1.0 457 1

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

  • Filter: (is_hired AND (user_id < 5000))
  • Rows Removed by Filter: 417
5. 4.570 4.570 ↑ 1.0 1 457

Index Scan using profile_userconfig_user_id_key on profile_userconfig p (cost=0.41..2.34 rows=1 width=10) (actual time=0.010..0.010 rows=1 loops=457)

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

CTE top_performer

7. 0.009 28.083 ↑ 100.0 2 1

Subquery Scan on m_2 (cost=10.30..137,578.85 rows=200 width=64) (actual time=14.577..28.083 rows=2 loops=1)

8. 1.233 6.968 ↑ 100.0 2 1

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

  • Group Key: managers.brand
9. 5.735 5.735 ↑ 1.0 457 1

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

10.          

SubPlan (forSubquery Scan)

11. 0.002 21.106 ↓ 0.0 0 2

Limit (cost=685.20..687.82 rows=1 width=32) (actual time=10.553..10.553 rows=0 loops=2)

12. 0.002 21.104 ↓ 0.0 0 2

Nested Loop (cost=685.20..687.82 rows=1 width=32) (actual time=10.552..10.552 rows=0 loops=2)

13. 0.002 21.102 ↓ 0.0 0 2

Nested Loop (cost=684.92..686.95 rows=1 width=4) (actual time=10.551..10.551 rows=0 loops=2)

14. 0.004 21.100 ↓ 0.0 0 2

Limit (cost=684.64..684.65 rows=1 width=12) (actual time=10.550..10.550 rows=0 loops=2)

15. 0.012 21.096 ↓ 0.0 0 2

Sort (cost=684.64..684.65 rows=1 width=12) (actual time=10.548..10.548 rows=0 loops=2)

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

GroupAggregate (cost=684.61..684.63 rows=1 width=12) (actual time=10.542..10.542 rows=0 loops=2)

  • Group Key: c.account_manager_id
17. 0.008 21.080 ↓ 0.0 0 2

Sort (cost=684.61..684.62 rows=1 width=4) (actual time=10.540..10.540 rows=0 loops=2)

  • Sort Key: c.account_manager_id
  • Sort Method: quicksort Memory: 25kB
18. 0.004 21.072 ↓ 0.0 0 2

Nested Loop (cost=0.58..684.60 rows=1 width=4) (actual time=10.536..10.536 rows=0 loops=2)

  • Join Filter: (sd.partner_id = p_1.id)
19. 0.002 21.068 ↓ 0.0 0 2

Nested Loop (cost=0.29..684.26 rows=1 width=12) (actual time=10.534..10.534 rows=0 loops=2)

20. 21.066 21.066 ↓ 0.0 0 2

Seq Scan on partners_partnerstatdata sd (cost=0.00..681.94 rows=1 width=4) (actual time=10.533..10.533 rows=0 loops=2)

  • Filter: is_reactivated
  • Rows Removed by Filter: 33932
21. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: (partner_id = sd.partner_id)
  • Filter: ((brand)::text = (m_2.brand)::text)
22. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: (id = c.partner_id)
  • Heap Fetches: 0
23. 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)
24. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: (id = partners_partnermanager.user_id)
25. 0.530 0.530 ↑ 1.0 457 1

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

26. 0.006 28.097 ↑ 100.0 2 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
27. 28.091 28.091 ↑ 100.0 2 1

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

28.          

SubPlan (forHash Join)

29. 10.054 276.485 ↑ 1.0 1 457

Aggregate (cost=1,038.98..1,038.99 rows=1 width=32) (actual time=0.605..0.605 rows=1 loops=457)

30. 24.522 266.431 ↑ 5.1 92 457

Nested Loop (cost=0.70..1,037.81 rows=469 width=6) (actual time=0.015..0.583 rows=92 loops=457)

31. 73.577 73.577 ↑ 5.0 92 457

Index Scan using partners_partnercontract_6bc80cbd on partners_partnercontract c_1 (cost=0.41..448.74 rows=462 width=4) (actual time=0.011..0.161 rows=92 loops=457)

  • Index Cond: (account_manager_id = m.manager_id)
32. 168.332 168.332 ↑ 1.0 1 42,083

Index Scan using partners_partnercontractstatdata_pkey on partners_partnercontractstatdata sd_1 (cost=0.29..1.28 rows=1 width=10) (actual time=0.004..0.004 rows=1 loops=42,083)

  • Index Cond: (contract_id = c_1.id)
33. 2.742 2,979.183 ↑ 1.0 1 457

Aggregate (cost=686.58..686.59 rows=1 width=8) (actual time=6.519..6.519 rows=1 loops=457)

34. 0.457 2,976.441 ↓ 0.0 0 457

Nested Loop (cost=0.58..686.58 rows=1 width=0) (actual time=6.513..6.513 rows=0 loops=457)

35. 0.000 2,975.984 ↓ 0.0 0 457

Nested Loop (cost=0.29..684.25 rows=1 width=4) (actual time=6.512..6.512 rows=0 loops=457)

36. 2,975.984 2,975.984 ↓ 0.0 0 457

Seq Scan on partners_partnerstatdata sd_2 (cost=0.00..681.94 rows=1 width=4) (actual time=6.512..6.512 rows=0 loops=457)

  • Filter: is_reactivated
  • Rows Removed by Filter: 33932
37. 0.000 0.000 ↓ 0.0 0

Index Only Scan using partners_partner_pkey on partners_partner p_2 (cost=0.29..2.31 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. 1.371 98.255 ↑ 1.0 1 457

Aggregate (cost=453.38..453.39 rows=1 width=8) (actual time=0.215..0.215 rows=1 loops=457)

41. 96.884 96.884 ↑ 3.5 2 457

Index Scan using partners_partnercontract_6bc80cbd on partners_partnercontract c_3 (cost=0.41..453.36 rows=7 width=0) (actual time=0.031..0.212 rows=2 loops=457)

  • 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: 90
42. 0.914 305.733 ↑ 1.0 1 457

Aggregate (cost=1,007.82..1,007.83 rows=1 width=8) (actual time=0.669..0.669 rows=1 loops=457)

43. 9.402 304.819 ↑ 2.0 1 457

Nested Loop (cost=0.70..1,007.82 rows=2 width=0) (actual time=0.217..0.667 rows=1 loops=457)

44. 85.002 85.002 ↑ 5.0 92 457

Index Scan using partners_partnercontract_6bc80cbd on partners_partnercontract c_4 (cost=0.41..448.74 rows=462 width=4) (actual time=0.006..0.186 rows=92 loops=457)

  • Index Cond: (account_manager_id = m.manager_id)
45. 210.415 210.415 ↓ 0.0 0 42,083

Index Scan using partners_partnercreditrisk_contract_id_key on partners_partnercreditrisk cr (cost=0.29..1.21 rows=1 width=4) (actual time=0.005..0.005 rows=0 loops=42,083)

  • Index Cond: (contract_id = c_4.id)
  • Filter: (due_30_days IS NOT NULL)
  • Rows Removed by Filter: 1
46. 2.285 293.851 ↑ 1.0 1 457

Aggregate (cost=991.00..991.01 rows=1 width=8) (actual time=0.643..0.643 rows=1 loops=457)

47. 26.807 291.566 ↑ 4.3 3 457

Nested Loop (cost=0.70..990.96 rows=13 width=0) (actual time=0.057..0.638 rows=3 loops=457)

48. 96.427 96.427 ↑ 5.0 92 457

Index Scan using partners_partnercontract_6bc80cbd on partners_partnercontract c_5 (cost=0.41..448.74 rows=462 width=4) (actual time=0.004..0.211 rows=92 loops=457)

  • Index Cond: (account_manager_id = m.manager_id)
49. 168.332 168.332 ↓ 0.0 0 42,083

Index Scan using partners_partnerstatdata_pkey on partners_partnerstatdata sd_3 (cost=0.29..1.17 rows=1 width=4) (actual time=0.004..0.004 rows=0 loops=42,083)

  • Index Cond: (partner_id = c_5.partner_id)
  • Filter: (avg_cancellation_percent_month >= '55'::numeric)
  • Rows Removed by Filter: 1
50. 0.457 230.328 ↑ 1.0 1 457

Aggregate (cost=1,009.35..1,009.36 rows=1 width=8) (actual time=0.504..0.504 rows=1 loops=457)

51. 8.070 229.871 ↓ 0.0 0 457

Nested Loop (cost=0.70..1,008.96 rows=157 width=0) (actual time=0.286..0.503 rows=0 loops=457)

52. 53.469 53.469 ↑ 5.0 92 457

Index Scan using partners_partnercontract_6bc80cbd on partners_partnercontract c_6 (cost=0.41..448.74 rows=462 width=4) (actual time=0.004..0.117 rows=92 loops=457)

  • Index Cond: (account_manager_id = m.manager_id)
53. 168.332 168.332 ↓ 0.0 0 42,083

Index Scan using partners_partnercreditrisk_contract_id_key on partners_partnercreditrisk cr_1 (cost=0.29..1.21 rows=1 width=4) (actual time=0.004..0.004 rows=0 loops=42,083)

  • Index Cond: (contract_id = c_6.id)
  • Filter: (credit_limit_remain_share <= 30)
  • Rows Removed by Filter: 1
Planning time : 6.363 ms
Execution time : 4,223.189 ms