explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ZnPS

Settings
# exclusive inclusive rows x rows loops node
1. 11.876 311.235 ↑ 2.5 16,669 1

Sort (cost=52,219.74..52,322.13 rows=40,957 width=1,144) (actual time=309.562..311.235 rows=16,669 loops=1)

  • Sort Key: profile_userconfig.user_id
  • Sort Method: quicksort Memory: 4455kB
2.          

CTE partners

3. 7.314 147.930 ↑ 1.2 5,793 1

Hash Semi Join (cost=5,501.21..11,125.03 rows=6,790 width=4) (actual time=83.906..147.930 rows=5,793 loops=1)

  • Hash Cond: (p.id = c_1.partner_id)
4. 14.063 57.550 ↓ 1.4 17,131 1

Merge Anti Join (cost=1.16..5,228.92 rows=12,168 width=4) (actual time=0.288..57.550 rows=17,131 loops=1)

  • Merge Cond: (p.id = c.partner_id)
5. 23.496 23.496 ↓ 1.0 32,089 1

Index Only Scan using partners_partner_pkey on partners_partner p (cost=0.29..1,226.95 rows=31,644 width=4) (actual time=0.022..23.496 rows=32,089 loops=1)

  • Heap Fetches: 31976
6. 19.991 19.991 ↓ 1.0 19,580 1

Index Only Scan using idx_partner_contract_is_involved on partners_partnercontract c (cost=0.29..3,721.03 rows=19,476 width=4) (actual time=0.026..19.991 rows=19,580 loops=1)

  • Heap Fetches: 19580
7. 6.454 83.066 ↓ 1.2 20,791 1

Hash (cost=5,279.32..5,279.32 rows=17,659 width=4) (actual time=83.066..83.066 rows=20,791 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 987kB
8. 11.051 76.612 ↓ 1.2 20,791 1

Hash Join (cost=1,139.50..5,279.32 rows=17,659 width=4) (actual time=20.354..76.612 rows=20,791 loops=1)

  • Hash Cond: (c_1.id = cr.contract_id)
9. 45.601 45.601 ↓ 1.2 22,962 1

Index Scan using idx_partner_contract_b2b_sales1 on partners_partnercontract c_1 (cost=0.29..3,888.35 rows=19,941 width=8) (actual time=0.046..45.601 rows=22,962 loops=1)

  • Index Cond: (created_date <= (now() - '1 year'::interval))
10. 8.363 19.960 ↓ 1.0 35,559 1

Hash (cost=696.46..696.46 rows=35,420 width=4) (actual time=19.960..19.960 rows=35,559 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 1763kB
11. 11.597 11.597 ↓ 1.0 35,559 1

Seq Scan on partners_partnercreditrisk cr (cost=0.00..696.46 rows=35,420 width=4) (actual time=0.011..11.597 rows=35,559 loops=1)

  • Filter: (total_unpaid IS NULL)
  • Rows Removed by Filter: 4126
12.          

CTE contracts

13. 5.150 280.264 ↑ 1.2 5,083 1

Sort (cost=32,571.88..32,587.43 rows=6,220 width=157) (actual time=279.403..280.264 rows=5,083 loops=1)

  • Sort Key: account_userconfig.team_id
  • Sort Method: quicksort Memory: 1352kB
14. 20.784 275.114 ↑ 1.2 5,083 1

Hash Join (cost=6,118.75..32,179.94 rows=6,220 width=157) (actual time=115.432..275.114 rows=5,083 loops=1)

  • Hash Cond: (account_userconfig.team_id = profile_userconfig_1.team_id)
15. 3.769 217.031 ↑ 2.5 5,097 1

Hash Join (cost=4,047.88..7,468.45 rows=12,919 width=93) (actual time=110.410..217.031 rows=5,097 loops=1)

  • Hash Cond: (c_2.account_manager_id = account_manager.id)
16. 3.755 207.318 ↑ 1.7 5,097 1

Hash Join (cost=1,796.16..5,044.63 rows=8,582 width=77) (actual time=104.445..207.318 rows=5,097 loops=1)

  • Hash Cond: (c_2.sales_manager_id = sales_manager.id)
17. 4.345 200.840 ↑ 1.5 5,862 1

Nested Loop (cost=1,297.28..4,444.90 rows=8,582 width=61) (actual time=101.707..200.840 rows=5,862 loops=1)

  • Join Filter: (partners.id = c_2.partner_id)
18. 5.005 173.323 ↑ 1.2 5,793 1

Hash Join (cost=1,296.99..1,518.51 rows=6,790 width=34) (actual time=101.685..173.323 rows=5,793 loops=1)

  • Hash Cond: (partners.id = p_1.id)
19. 150.728 150.728 ↑ 1.2 5,793 1

CTE Scan on partners (cost=0.00..135.80 rows=6,790 width=4) (actual time=83.908..150.728 rows=5,793 loops=1)

20. 8.424 17.590 ↓ 1.0 32,089 1

Hash (cost=901.44..901.44 rows=31,644 width=30) (actual time=17.590..17.590 rows=32,089 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 2232kB
21. 9.166 9.166 ↓ 1.0 32,089 1

Seq Scan on partners_partner p_1 (cost=0.00..901.44 rows=31,644 width=30) (actual time=0.017..9.166 rows=32,089 loops=1)

22. 23.172 23.172 ↑ 1.0 1 5,793

Index Scan using partners_partnercontract_4e98b6eb on partners_partnercontract c_2 (cost=0.29..0.42 rows=1 width=35) (actual time=0.003..0.004 rows=1 loops=5,793)

  • Index Cond: (partner_id = p_1.id)
23. 0.232 2.723 ↑ 1.0 809 1

Hash (cost=488.29..488.29 rows=847 width=24) (actual time=2.723..2.723 rows=809 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 53kB
24. 0.307 2.491 ↑ 1.0 809 1

Hash Join (cost=451.13..488.29 rows=847 width=24) (actual time=1.919..2.491 rows=809 loops=1)

  • Hash Cond: (sales_manager.user_id = sales_user.id)
25. 0.306 0.306 ↑ 1.0 809 1

Seq Scan on partners_partnermanager sales_manager (cost=0.00..26.47 rows=847 width=8) (actual time=0.012..0.306 rows=809 loops=1)

26. 0.741 1.878 ↑ 1.0 2,628 1

Hash (cost=418.28..418.28 rows=2,628 width=20) (actual time=1.878..1.878 rows=2,628 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 171kB
27. 1.137 1.137 ↑ 1.0 2,628 1

Seq Scan on auth_user sales_user (cost=0.00..418.28 rows=2,628 width=20) (actual time=0.005..1.137 rows=2,628 loops=1)

28. 0.279 5.944 ↑ 1.6 809 1

Hash (cost=2,235.78..2,235.78 rows=1,275 width=24) (actual time=5.944..5.944 rows=809 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 61kB
29. 0.652 5.665 ↑ 1.6 809 1

Nested Loop (cost=451.53..2,235.78 rows=1,275 width=24) (actual time=1.978..5.665 rows=809 loops=1)

  • Join Filter: (account_manager.user_id = account_userconfig.user_id)
30. 0.334 2.586 ↑ 1.0 809 1

Hash Join (cost=451.13..488.29 rows=847 width=28) (actual time=1.959..2.586 rows=809 loops=1)

  • Hash Cond: (account_manager.user_id = account_user.id)
31. 0.345 0.345 ↑ 1.0 809 1

Seq Scan on partners_partnermanager account_manager (cost=0.00..26.47 rows=847 width=8) (actual time=0.016..0.345 rows=809 loops=1)

32. 0.724 1.907 ↑ 1.0 2,628 1

Hash (cost=418.28..418.28 rows=2,628 width=20) (actual time=1.907..1.907 rows=2,628 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 171kB
33. 1.183 1.183 ↑ 1.0 2,628 1

Seq Scan on auth_user account_user (cost=0.00..418.28 rows=2,628 width=20) (actual time=0.006..1.183 rows=2,628 loops=1)

34. 2.427 2.427 ↑ 1.0 1 809

Index Scan using profile_userconfig_user_id_key on profile_userconfig account_userconfig (cost=0.41..2.05 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=809)

  • Index Cond: (user_id = account_user.id)
35. 0.033 1.718 ↑ 1.1 134 1

Hash (cost=2,068.99..2,068.99 rows=151 width=4) (actual time=1.718..1.718 rows=134 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
36. 0.176 1.685 ↑ 1.1 134 1

HashAggregate (cost=2,067.48..2,068.99 rows=151 width=4) (actual time=1.661..1.685 rows=134 loops=1)

  • Group Key: profile_userconfig_1.team_id
37. 1.509 1.509 ↑ 2.4 547 1

Index Scan using profile_userconfig_f6a7ca40 on profile_userconfig profile_userconfig_1 (cost=0.28..2,064.18 rows=1,317 width=4) (actual time=0.009..1.509 rows=547 loops=1)

  • Index Cond: (team_id IS NOT NULL)
  • Filter: ((user_id >= 200) AND (user_id <= 2000))
  • Rows Removed by Filter: 755
38.          

SubPlan (forHash Join)

39. 0.000 35.581 ↑ 1.0 1 5,083

Limit (cost=0.58..3.62 rows=1 width=32) (actual time=0.007..0.007 rows=1 loops=5,083)

40. 10.193 35.581 ↑ 1.0 1 5,083

Nested Loop (cost=0.58..3.62 rows=1 width=32) (actual time=0.007..0.007 rows=1 loops=5,083)

41. 10.166 10.166 ↑ 1.0 1 5,083

Index Only Scan using idx_partner_contractdata_relation on partners_partnercontractdata cd (cost=0.29..1.31 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=5,083)

  • Index Cond: (contract_id = c_2.id)
  • Heap Fetches: 1404
42. 15.222 15.222 ↑ 1.0 1 5,074

Index Scan using partners_partnerlegalentity_pkey on partners_partnerlegalentity le (cost=0.29..2.31 rows=1 width=33) (actual time=0.003..0.003 rows=1 loops=5,074)

  • Index Cond: (id = cd.legal_entity_id)
43. 10.153 299.359 ↑ 2.5 16,669 1

Merge Join (cost=516.62..5,369.59 rows=40,957 width=1,144) (actual time=285.789..299.359 rows=16,669 loops=1)

  • Merge Cond: (profile_userconfig.team_id = contracts.account_team_id)
  • Join Filter: (contracts.sales_manager_id <> profile_userconfig.user_id)
  • Rows Removed by Join Filter: 2169
44. 1.595 1.595 ↑ 5.6 492 1

Index Scan using profile_userconfig_f6a7ca40 on profile_userconfig (cost=0.28..4,129.48 rows=2,736 width=8) (actual time=0.015..1.595 rows=492 loops=1)

  • Filter: ((user_id >= 200) AND (user_id <= 2000))
  • Rows Removed by Filter: 666
45. 4.206 287.611 ↓ 3.0 18,835 1

Sort (cost=516.34..531.89 rows=6,220 width=1,148) (actual time=285.718..287.611 rows=18,835 loops=1)

  • Sort Key: contracts.account_team_id
  • Sort Method: quicksort Memory: 1352kB
46. 283.405 283.405 ↑ 1.2 5,083 1

CTE Scan on contracts (cost=0.00..124.40 rows=6,220 width=1,148) (actual time=279.405..283.405 rows=5,083 loops=1)

Planning time : 5.583 ms
Execution time : 313.647 ms