explain.depesz.com

PostgreSQL's explain analyze made readable

Result: OxDF

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 239,499.058 ↑ 3.8 17 1

Finalize GroupAggregate (cost=45,483.08..45,499.29 rows=64 width=16) (actual time=239,499.047..239,499.058 rows=17 loops=1)

  • Group Key: harvest.game_species_id
2. 19.989 239,512.896 ↑ 3.7 35 1

Gather Merge (cost=45,483.08..45,498.01 rows=128 width=16) (actual time=239,499.039..239,512.896 rows=35 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
3. 0.019 239,492.907 ↑ 5.3 12 3 / 3

Sort (cost=44,483.06..44,483.22 rows=64 width=16) (actual time=239,492.906..239,492.907 rows=12 loops=3)

  • Sort Key: harvest.game_species_id
  • Sort Method: quicksort Memory: 25kB
  • Worker 0: Sort Method: quicksort Memory: 25kB
  • Worker 1: Sort Method: quicksort Memory: 25kB
4. 0.026 239,492.888 ↑ 5.3 12 3 / 3

Partial HashAggregate (cost=44,480.50..44,481.14 rows=64 width=16) (actual time=239,492.886..239,492.888 rows=12 loops=3)

  • Group Key: harvest.game_species_id
5. 102.451 239,492.862 ↑ 3,445.2 31 3 / 3

Parallel Seq Scan on harvest (cost=9,149.76..43,946.49 rows=106,802 width=12) (actual time=239,473.206..239,492.862 rows=31 loops=3)

  • Filter: (((group_hunting_day_id IS NULL) AND (hashed SubPlan 2)) OR ((group_hunting_day_id IS NOT NULL) AND (hashed SubPlan 3) AND (point_of_time >= '2020-08-01 00:00:00+00'::timestamp with time zone) AND (point_of_time < '2021-01-01 00:00:00+00'::timestamp with time zone)))
  • Rows Removed by Filter: 301,315
6.          

SubPlan (for Parallel Seq Scan)

7. 9.425 239,390.222 ↓ 228.0 228 3 / 3

Nested Loop (cost=5.89..8,321.38 rows=1 width=8) (actual time=239,377.686..239,390.222 rows=228 loops=3)

  • Join Filter: (z.zone_id = hca.zone_id)
  • Rows Removed by Join Filter: 102,894
8. 0.075 0.075 ↑ 1.0 18 3 / 3

Index Scan using ndx_hunting_club_area_club_id on hunting_club_area hca (cost=0.29..386.63 rows=18 width=8) (actual time=0.010..0.075 rows=18 loops=3)

  • Index Cond: (club_id = 1,541)
  • Filter: (is_active AND (hunting_year = 2,020))
  • Rows Removed by Filter: 71
9. 14.580 239,380.722 ↓ 42.8 5,729 54 / 3

Materialize (cost=5.60..7,898.90 rows=134 width=16) (actual time=0.064..13,298.929 rows=5,729 loops=54)

10. 5.009 239,366.142 ↓ 42.8 5,729 3 / 3

Nested Loop (cost=5.60..7,898.23 rows=134 width=16) (actual time=1.154..239,366.142 rows=5,729 loops=3)

11. 1.177 26.323 ↓ 47.0 235 3 / 3

Nested Loop (cost=5.32..6,508.84 rows=5 width=40) (actual time=0.347..26.323 rows=235 loops=3)

  • Join Filter: ((date(h2.point_of_time) >= COALESCE(occ.begin_date, date(h2.point_of_time))) AND (date(h2.point_of_time) <= COALESCE(occ.end_date, date(h2.point_of_time))))
12. 0.182 12.154 ↑ 1.7 203 3 / 3

Nested Loop (cost=0.85..2,941.72 rows=349 width=16) (actual time=0.152..12.154 rows=203 loops=3)

13. 8.115 8.115 ↑ 1.7 203 3 / 3

Index Scan using ndx_occupation_organisation_and_type on occupation occ (cost=0.43..1,128.16 rows=349 width=16) (actual time=0.135..8.115 rows=203 loops=3)

  • Index Cond: (organisation_id = 1,541)
  • Filter: (deletion_time IS NULL)
  • Rows Removed by Filter: 104
14. 3.857 3.857 ↑ 1.0 1 609 / 3

Index Only Scan using person_pkey on person p (cost=0.42..5.20 rows=1 width=8) (actual time=0.019..0.019 rows=1 loops=609)

  • Index Cond: (person_id = occ.person_id)
  • Heap Fetches: 246
15. 4.654 12.992 ↑ 2.0 1 609 / 3

Bitmap Heap Scan on harvest h2 (cost=4.47..10.17 rows=2 width=64) (actual time=0.054..0.064 rows=1 loops=609)

  • Recheck Cond: ((author_id = p.person_id) OR (actual_shooter_id = p.person_id))
  • Filter: ((point_of_time >= '2020-08-01 00:00:00+00'::timestamp with time zone) AND (point_of_time < '2021-01-01 00:00:00+00'::timestamp with time zone) AND ((NOT harvest_report_required) OR (harvest_report_required AND ((harvest_report_state)::text = 'APPROVED'::text))) AND (NOT (hashed SubPlan 1)))
  • Rows Removed by Filter: 10
  • Heap Blocks: exact=2,097
16. 0.406 8.323 ↓ 0.0 0 609 / 3

BitmapOr (cost=1.43..1.43 rows=68 width=0) (actual time=0.041..0.041 rows=0 loops=609)

17. 4.872 4.872 ↑ 4.2 10 609 / 3

Bitmap Index Scan on ndx_harvest_author (cost=0.00..0.77 rows=42 width=0) (actual time=0.024..0.024 rows=10 loops=609)

  • Index Cond: (author_id = p.person_id)
18. 3.045 3.045 ↑ 2.9 9 609 / 3

Bitmap Index Scan on ndx_harvest_actual_shooter (cost=0.00..0.65 rows=26 width=0) (actual time=0.015..0.015 rows=9 loops=609)

  • Index Cond: (actual_shooter_id = p.person_id)
19.          

SubPlan (for Bitmap Heap Scan)

20. 0.015 0.015 ↑ 1.0 4 3 / 3

Seq Scan on game_species (cost=0.00..3.04 rows=4 width=8) (actual time=0.010..0.015 rows=4 loops=3)

  • Filter: (official_code = ANY ('{47503,47484,47629,200556}'::integer[]))
  • Rows Removed by Filter: 65
21. 239,334.810 239,334.810 ↓ 2.2 24 705 / 3

Index Scan using zone_geom_gist on zone z (cost=0.28..277.77 rows=11 width=498,211) (actual time=293.320..1,018.446 rows=24 loops=705)

  • Index Cond: (geom && h2.geom)
  • Filter: st_intersects(geom, h2.geom)
  • Rows Removed by Filter: 93
22. 0.011 0.189 ↑ 2.4 50 3 / 3

Nested Loop (cost=5.43..828.08 rows=119 width=8) (actual time=0.097..0.189 rows=50 loops=3)

23. 0.093 0.110 ↓ 1.1 17 3 / 3

Bitmap Heap Scan on organisation "group" (cost=5.01..317.06 rows=16 width=8) (actual time=0.086..0.110 rows=17 loops=3)

  • Recheck Cond: (parent_organisation_id = 1,541)
  • Filter: (hunting_year = 2,020)
  • Rows Removed by Filter: 69
  • Heap Blocks: exact=68
24. 0.017 0.017 ↑ 1.1 86 3 / 3

Bitmap Index Scan on ndx_organisation_parent (cost=0.00..5.00 rows=95 width=0) (actual time=0.017..0.017 rows=86 loops=3)

  • Index Cond: (parent_organisation_id = 1,541)
25. 0.068 0.068 ↑ 6.0 3 51 / 3

Index Scan using ndx_group_hunting_day_group on group_hunting_day (cost=0.42..31.76 rows=18 width=16) (actual time=0.002..0.004 rows=3 loops=51)

  • Index Cond: (hunting_group_id = "group".organisation_id)
Planning time : 4.420 ms
Execution time : 239,521.408 ms