explain.depesz.com

PostgreSQL's explain analyze made readable

Result: nS95 : Optimization for: plan #ZlpC

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Sort (cost=436,725.25..437,965.01 rows=495,903 width=125) (actual rows= loops=)

  • Sort Key: (count(case_activities.case_activity_id)) DESC
2. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=343,969.24..357,606.57 rows=495,903 width=125) (actual rows= loops=)

  • Group Key: head.head_id, unit.unit_name, division.division_name, unit_division.unit_division_id, cases.number_on_file
3. 0.000 0.000 ↓ 0.0

Sort (cost=343,969.24..345,209.00 rows=495,903 width=125) (actual rows= loops=)

  • Sort Key: head.head_id, unit.unit_name, division.division_name, unit_division.unit_division_id, cases.number_on_file
4. 0.000 0.000 ↓ 0.0

Hash Join (cost=148,242.02..264,850.57 rows=495,903 width=125) (actual rows= loops=)

  • Hash Cond: (udct.case_type_id_fk = ct.case_type_id)
5. 0.000 0.000 ↓ 0.0

Hash Join (cost=148,192.07..257,981.95 rows=495,903 width=133) (actual rows= loops=)

  • Hash Cond: (udct.unit_division_id_fk = unit_division.unit_division_id)
6. 0.000 0.000 ↓ 0.0

Hash Join (cost=148,142.20..251,113.42 rows=495,903 width=39) (actual rows= loops=)

  • Hash Cond: (cases.unit_div_case_type_id_fk = udct.unit_div_case_type_id)
7. 0.000 0.000 ↓ 0.0

Hash Join (cost=147,434.45..243,586.99 rows=495,903 width=31) (actual rows= loops=)

  • Hash Cond: (cao.case_type_outcome_id_fk = cto.case_type_outcome_id)
8. 0.000 0.000 ↓ 0.0

Hash Join (cost=145,453.42..234,787.30 rows=495,903 width=39) (actual rows= loops=)

  • Hash Cond: (case_activities.court_processes_id_fk = cp.court_processes_id)
9. 0.000 0.000 ↓ 0.0

Hash Join (cost=143,916.77..226,430.75 rows=496,234 width=47) (actual rows= loops=)

  • Hash Cond: (case_activities.case_id_fk = cases.case_id)
10. 0.000 0.000 ↓ 0.0

Hash Join (cost=97,287.57..159,015.75 rows=731,855 width=32) (actual rows= loops=)

  • Hash Cond: (cao.case_activity_id_fk = case_activities.case_activity_id)
11. 0.000 0.000 ↓ 0.0

Seq Scan on case_activity_outcome cao (cost=0.00..28,071.82 rows=1,484,482 width=16) (actual rows= loops=)

12. 0.000 0.000 ↓ 0.0

Hash (cost=77,632.96..77,632.96 rows=1,070,529 width=24) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

Seq Scan on case_activities (cost=0.00..77,632.96 rows=1,070,529 width=24) (actual rows= loops=)

  • Filter: ((activity_date >= '2019-07-01'::date) AND (activity_date <= '2020-06-30'::date) AND (activity_deleted = 0))
14. 0.000 0.000 ↓ 0.0

Hash (cost=37,937.28..37,937.28 rows=449,514 width=31) (actual rows= loops=)

15. 0.000 0.000 ↓ 0.0

Seq Scan on cases (cost=0.00..37,937.28 rows=449,514 width=31) (actual rows= loops=)

  • Filter: ((done = 1) AND (case_status_id_fk = 1))
16. 0.000 0.000 ↓ 0.0

Hash (cost=1,117.26..1,117.26 rows=33,551 width=4) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

Hash Join (cost=6.42..1,117.26 rows=33,551 width=4) (actual rows= loops=)

  • Hash Cond: (cp.court_action_id_fk = cat.court_actions_id)
18. 0.000 0.000 ↓ 0.0

Seq Scan on court_processes cp (cost=0.00..649.51 rows=33,551 width=12) (actual rows= loops=)

19. 0.000 0.000 ↓ 0.0

Hash (cost=4.52..4.52 rows=152 width=8) (actual rows= loops=)

20. 0.000 0.000 ↓ 0.0

Seq Scan on court_actions cat (cost=0.00..4.52 rows=152 width=8) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

Hash (cost=1,401.63..1,401.63 rows=46,352 width=8) (actual rows= loops=)

22. 0.000 0.000 ↓ 0.0

Hash Join (cost=4.77..1,401.63 rows=46,352 width=8) (actual rows= loops=)

  • Hash Cond: (cto.case_outcome_id_fk = co.case_outcome_id)
23. 0.000 0.000 ↓ 0.0

Seq Scan on case_type_outcome cto (cost=0.00..759.52 rows=46,352 width=16) (actual rows= loops=)

24. 0.000 0.000 ↓ 0.0

Hash (cost=3.23..3.23 rows=123 width=8) (actual rows= loops=)

25. 0.000 0.000 ↓ 0.0

Seq Scan on case_outcomes co (cost=0.00..3.23 rows=123 width=8) (actual rows= loops=)

26. 0.000 0.000 ↓ 0.0

Hash (cost=411.78..411.78 rows=23,678 width=24) (actual rows= loops=)

27. 0.000 0.000 ↓ 0.0

Seq Scan on unit_div_case_type udct (cost=0.00..411.78 rows=23,678 width=24) (actual rows= loops=)

28. 0.000 0.000 ↓ 0.0

Hash (cost=45.74..45.74 rows=330 width=102) (actual rows= loops=)

29. 0.000 0.000 ↓ 0.0

Hash Join (cost=25.83..45.74 rows=330 width=102) (actual rows= loops=)

  • Hash Cond: (unit.subhead_id_fk = subhead.subhead_id)
30. 0.000 0.000 ↓ 0.0

Hash Join (cost=20.66..36.03 rows=330 width=110) (actual rows= loops=)

  • Hash Cond: (unit_division.division_id_fk = division.division_id)
31. 0.000 0.000 ↓ 0.0

Hash Join (cost=18.15..28.99 rows=330 width=89) (actual rows= loops=)

  • Hash Cond: (unit_division.unit_id_fk = unit.unit_id)
32. 0.000 0.000 ↓ 0.0

Seq Scan on unit_division (cost=0.00..6.30 rows=330 width=24) (actual rows= loops=)

33. 0.000 0.000 ↓ 0.0

Hash (cost=14.38..14.38 rows=302 width=77) (actual rows= loops=)

34. 0.000 0.000 ↓ 0.0

Hash Join (cost=1.20..14.38 rows=302 width=77) (actual rows= loops=)

  • Hash Cond: (unit.head_id_fk = head.head_id)
35. 0.000 0.000 ↓ 0.0

Seq Scan on unit (cost=0.00..9.02 rows=302 width=45) (actual rows= loops=)

36. 0.000 0.000 ↓ 0.0

Hash (cost=1.09..1.09 rows=9 width=40) (actual rows= loops=)

37. 0.000 0.000 ↓ 0.0

Seq Scan on head (cost=0.00..1.09 rows=9 width=40) (actual rows= loops=)

38. 0.000 0.000 ↓ 0.0

Hash (cost=1.67..1.67 rows=67 width=37) (actual rows= loops=)

39. 0.000 0.000 ↓ 0.0

Seq Scan on division (cost=0.00..1.67 rows=67 width=37) (actual rows= loops=)

40. 0.000 0.000 ↓ 0.0

Hash (cost=3.41..3.41 rows=141 width=8) (actual rows= loops=)

41. 0.000 0.000 ↓ 0.0

Seq Scan on subhead (cost=0.00..3.41 rows=141 width=8) (actual rows= loops=)

42. 0.000 0.000 ↓ 0.0

Hash (cost=39.67..39.67 rows=822 width=8) (actual rows= loops=)

43. 0.000 0.000 ↓ 0.0

Hash Join (cost=9.15..39.67 rows=822 width=8) (actual rows= loops=)

  • Hash Cond: (ct.case_category_id_fk = cg.category_id)
44. 0.000 0.000 ↓ 0.0

Seq Scan on case_types ct (cost=0.00..19.22 rows=822 width=16) (actual rows= loops=)

45. 0.000 0.000 ↓ 0.0

Hash (cost=6.29..6.29 rows=229 width=8) (actual rows= loops=)

46. 0.000 0.000 ↓ 0.0

Seq Scan on case_categories cg (cost=0.00..6.29 rows=229 width=8) (actual rows= loops=)