explain.depesz.com

PostgreSQL's explain analyze made readable

Result: R2L

Settings
# exclusive inclusive rows x rows loops node
1. 0.026 577.538 ↑ 1.0 1 1

Aggregate (cost=80,574.42..80,574.43 rows=1 width=8) (actual time=577.538..577.538 rows=1 loops=1)

2. 0.042 577.512 ↓ 5.0 159 1

Unique (cost=80,573.86..80,574.02 rows=32 width=4) (actual time=577.446..577.512 rows=159 loops=1)

3. 0.000 577.470 ↓ 7.8 248 1

Sort (cost=80,573.86..80,573.94 rows=32 width=4) (actual time=577.445..577.470 rows=248 loops=1)

  • Sort Key: action_required_actionrequired.client_id
  • Sort Method: quicksort Memory: 36kB
4. 5.199 578.560 ↓ 7.8 248 1

Gather (cost=48,567.58..80,573.06 rows=32 width=4) (actual time=455.012..578.560 rows=248 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
5. 0.460 573.361 ↓ 6.5 124 2 / 2

Hash Join (cost=47,567.58..79,569.86 rows=19 width=4) (actual time=489.038..573.361 rows=124 loops=2)

  • Hash Cond: (action_required_actionrequired.client_id = v0.id)
6. 5.235 102.264 ↓ 47.6 904 2 / 2

Hash Semi Join (cost=1,020.00..33,022.22 rows=19 width=8) (actual time=17.775..102.264 rows=904 loops=2)

  • Hash Cond: (CASE WHEN (action_required_actionrequired.is_handed_off AND accounts_managementcompany.action_required_tour_scheduled_handoff) THEN action_required_actionrequired.site_team_group_id WHEN ((accounts_managementcompany.action_required_hand_off_days IS NULL) AND (NOT accounts_managementcompany.action_required_tour_scheduled_handoff)) THEN action_required_actionrequired.site_team_group_id WHEN ((clients_client.created_at < '2020-05-19 17:13:30.96173'::timestamp without time zone) AND (accounts_managementcompany.action_required_hand_off_days IS NOT NULL)) THEN action_required_actionrequired.site_team_group_id ELSE accounts_employeegroup.central_leasing_team_id END = u0.id)
7. 5.541 96.964 ↑ 1.1 26,008 2 / 2

Nested Loop (cost=1,013.08..32,767.10 rows=27,966 width=30) (actual time=17.490..96.964 rows=26,008 loops=2)

8. 5.068 39.406 ↑ 1.1 26,008 2 / 2

Hash Join (cost=1,012.65..3,865.55 rows=27,966 width=18) (actual time=17.470..39.406 rows=26,008 loops=2)

  • Hash Cond: (accounts_employeegroup.company_id = accounts_managementcompany.id)
9. 6.119 18.313 ↑ 1.1 26,008 2 / 2

Hash Join (cost=219.50..2,998.97 rows=27,966 width=17) (actual time=1.371..18.313 rows=26,008 loops=2)

  • Hash Cond: (action_required_actionrequired.site_team_group_id = accounts_employeegroup.id)
10. 10.841 10.841 ↑ 1.1 26,008 2 / 2

Parallel Seq Scan on action_required_actionrequired (cost=0.00..2,705.85 rows=27,966 width=9) (actual time=0.004..10.841 rows=26,008 loops=2)

  • Filter: ((NOT action_completed) AND (polymorphic_ctype_id = 317))
  • Rows Removed by Filter: 52595
11. 0.272 1.353 ↑ 1.0 1,489 2 / 2

Hash (cost=200.89..200.89 rows=1,489 width=12) (actual time=1.353..1.353 rows=1,489 loops=2)

  • Buckets: 2048 Batches: 1 Memory Usage: 76kB
12. 1.081 1.081 ↑ 1.0 1,489 2 / 2

Seq Scan on accounts_employeegroup (cost=0.00..200.89 rows=1,489 width=12) (actual time=0.006..1.081 rows=1,489 loops=2)

13. 2.177 16.025 ↑ 1.0 11,230 2 / 2

Hash (cost=652.51..652.51 rows=11,251 width=9) (actual time=16.025..16.025 rows=11,230 loops=2)

  • Buckets: 16384 Batches: 1 Memory Usage: 534kB
14. 13.848 13.848 ↑ 1.0 11,230 2 / 2

Seq Scan on accounts_managementcompany (cost=0.00..652.51 rows=11,251 width=9) (actual time=0.011..13.848 rows=11,230 loops=2)

15. 52.017 52.017 ↑ 1.0 1 52,017 / 2

Index Scan using clients_client_pkey on clients_client (cost=0.42..1.03 rows=1 width=12) (actual time=0.002..0.002 rows=1 loops=52,017)

  • Index Cond: (id = action_required_actionrequired.client_id)
16. 0.005 0.065 ↓ 4.0 8 2 / 2

Hash (cost=6.90..6.90 rows=2 width=8) (actual time=0.065..0.065 rows=8 loops=2)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
17. 0.001 0.060 ↓ 4.0 8 2 / 2

Nested Loop (cost=0.56..6.90 rows=2 width=8) (actual time=0.043..0.060 rows=8 loops=2)

18. 0.019 0.019 ↓ 4.0 8 2 / 2

Index Scan using accounts_employeegroup_employees_e8701ad4 on accounts_employeegroup_employees u1 (cost=0.29..3.00 rows=2 width=4) (actual time=0.013..0.019 rows=8 loops=2)

  • Index Cond: (user_id = 30738)
19. 0.040 0.040 ↑ 1.0 1 16 / 2

Index Only Scan using accounts_employeegroup_pkey on accounts_employeegroup u0 (cost=0.28..1.95 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=16)

  • Index Cond: (id = u1.employeegroup_id)
  • Heap Fetches: 0
20. 0.406 470.637 ↑ 23.2 1,768 2 / 2

Hash (cost=46,035.71..46,035.71 rows=40,950 width=4) (actual time=470.637..470.637 rows=1,768 loops=2)

  • Buckets: 65536 Batches: 1 Memory Usage: 575kB
21. 1.635 470.231 ↑ 23.2 1,768 2 / 2

HashAggregate (cost=45,216.71..45,626.21 rows=40,950 width=4) (actual time=469.644..470.231 rows=1,768 loops=2)

  • Group Key: v0.id
22. 104.010 468.596 ↑ 11.4 3,587 2 / 2

Hash Right Join (cost=32,542.89..45,114.33 rows=40,950 width=4) (actual time=359.514..468.596 rows=3,587 loops=2)

  • Hash Cond: (v4.client_id = v0.id)
  • Filter: ((v2.user_id = 30738) OR (hashed SubPlan 1))
  • Rows Removed by Filter: 64195
23. 92.655 92.655 ↓ 1.0 467,803 2 / 2

Index Only Scan using clients_groupassignment_client_id_group_id_55417b62_uniq on clients_groupassignment v4 (cost=0.42..10,008.33 rows=467,654 width=8) (actual time=0.033..92.655 rows=467,803 loops=2)

  • Heap Fetches: 73378
24. 16.816 271.882 ↑ 1.2 67,776 2 / 2

Hash (cost=31,511.99..31,511.99 rows=81,886 width=8) (actual time=271.882..271.882 rows=67,776 loops=2)

  • Buckets: 131072 Batches: 1 Memory Usage: 3658kB
25. 125.346 255.066 ↑ 1.2 67,776 2 / 2

Hash Right Join (cost=20,028.69..31,511.99 rows=81,886 width=8) (actual time=78.389..255.066 rows=67,776 loops=2)

  • Hash Cond: (v2.client_id = v0.id)
26. 51.991 51.991 ↓ 1.0 639,379 2 / 2

Seq Scan on clients_client_agents v2 (cost=0.00..9,820.90 rows=633,290 width=8) (actual time=0.015..51.991 rows=639,379 loops=2)

27. 8.750 77.729 ↑ 2.0 40,536 2 / 2

Hash (cost=19,005.12..19,005.12 rows=81,886 width=4) (actual time=77.729..77.729 rows=40,536 loops=2)

  • Buckets: 131072 Batches: 1 Memory Usage: 2450kB
28. 68.979 68.979 ↑ 2.0 40,536 2 / 2

Index Scan using clients_client_brokerage_id on clients_client v0 (cost=0.42..19,005.12 rows=81,886 width=4) (actual time=0.021..68.979 rows=40,536 loops=2)

  • Index Cond: (brokerage_id = 18855)
  • Filter: ((status <> ALL ('{50,70}'::integer[])) AND (status <> 80))
  • Rows Removed by Filter: 57998
29.          

SubPlan (for Hash Right Join)

30. 0.005 0.049 ↓ 4.0 8 2 / 2

Nested Loop (cost=0.56..6.90 rows=2 width=4) (actual time=0.028..0.049 rows=8 loops=2)

31. 0.020 0.020 ↓ 4.0 8 2 / 2

Index Scan using accounts_employeegroup_employees_e8701ad4 on accounts_employeegroup_employees u1_1 (cost=0.29..3.00 rows=2 width=4) (actual time=0.014..0.020 rows=8 loops=2)

  • Index Cond: (user_id = 30738)
32. 0.024 0.024 ↑ 1.0 1 16 / 2

Index Only Scan using accounts_employeegroup_pkey on accounts_employeegroup u0_1 (cost=0.28..1.95 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=16)

  • Index Cond: (id = u1_1.employeegroup_id)
  • Heap Fetches: 0
Planning time : 5.720 ms
Execution time : 579.922 ms