explain.depesz.com

PostgreSQL's explain analyze made readable

Result: lSEW : Optimization for: Optimization for: plan #Ztf5; plan #ZLFT

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 102.027 105,211.113 ↑ 131.5 28 1

Finalize HashAggregate (cost=8,397.16..9,400.24 rows=3,681 width=40) (actual time=105,117.372..105,211.113 rows=28 loops=1)

  • Group Key: (date_part('year'::text, localization_2019.date)), admingeom.admingeom_id
  • Buffers: shared hit=13269860
2. 69,861.164 105,109.086 ↑ 109.6 28 1

Gather (cost=8,040.51..8,366.48 rows=3,068 width=2,616) (actual time=504.343..105,109.086 rows=28 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=13269637
3. 4.971 35,247.922 ↑ 170.4 9 3

Partial HashAggregate (cost=7,040.51..7,059.68 rows=1,534 width=2,616) (actual time=35,247.907..35,247.922 rows=9 loops=3)

  • Group Key: date_part('year'::text, localization_2019.date), admingeom.admingeom_id
  • Buffers: shared hit=13269637
4. 33,269.263 35,242.951 ↓ 9.1 13,925 3

Nested Loop (cost=3.61..7,029.00 rows=1,534 width=2,608) (actual time=18.167..35,242.951 rows=13,925 loops=3)

  • Buffers: shared hit=13269637
5. 17.067 17.067 ↑ 1.4 18 3

Parallel Seq Scan on admingeom (cost=0.00..3,836.29 rows=25 width=2,600) (actual time=6.089..17.067 rows=18 loops=3)

  • Filter: ((label <> 'Monde'::text) AND (adminclass_id = 1))
  • Rows Removed by Filter: 13811
  • Buffers: shared hit=3577
6. 0.250 1,956.621 ↓ 129.0 774 54

Append (cost=3.61..127.50 rows=6 width=40) (actual time=1,825.365..1,956.621 rows=774 loops=54)

  • Buffers: shared hit=13266060
7. 1,954.440 1,956.371 ↓ 129.0 774 54

Bitmap Heap Scan on localization_2019 (cost=3.61..127.47 rows=6 width=40) (actual time=1,825.357..1,956.371 rows=774 loops=54)

  • Recheck Cond: (admingeom.contour && coord)
  • Filter: ((date >= '2019-01-01 00:00:00+00'::timestamp with time zone) AND (date <= '2019-01-31 23:59:59+00'::timestamp with time zone) AND _st_intersects(admingeom.contour, coord))
  • Rows Removed by Filter: 13697
  • Heap Blocks: exact=5314
  • Buffers: shared hit=13266060
8. 1.931 1.931 ↓ 328.9 14,471 54

Bitmap Index Scan on localization_2019_coord_idx (cost=0.00..3.61 rows=44 width=0) (actual time=1.931..1.931 rows=14,471 loops=54)

  • Index Cond: (admingeom.contour && coord)
  • Buffers: shared hit=5697