explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ZLFT : Optimization for: plan #Ztf5

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 104.311 105,603.491 ↑ 2.1 28 1

Finalize GroupAggregate (cost=8,035.19..8,065.77 rows=59 width=32) (actual time=105,500.724..105,603.491 rows=28 loops=1)

  • Group Key: admingeom.admingeom_id
  • Buffers: shared hit=13143575
2. 0.000 105,499.180 ↑ 4.2 28 1

Gather Merge (cost=8,035.19..8,048.95 rows=118 width=2,608) (actual time=105,499.058..105,499.180 rows=28 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=13269651
3. 0.192 106,465.293 ↑ 6.6 9 3

Sort (cost=7,035.16..7,035.31 rows=59 width=2,608) (actual time=35,488.428..35,488.431 rows=9 loops=3)

  • Sort Key: admingeom.admingeom_id
  • Sort Method: quicksort Memory: 38kB
  • Worker 0: Sort Method: quicksort Memory: 37kB
  • Worker 1: Sort Method: quicksort Memory: 41kB
  • Buffers: shared hit=13269651
4. 13.206 106,465.101 ↑ 6.6 9 3

Partial HashAggregate (cost=7,032.84..7,033.43 rows=59 width=2,608) (actual time=35,488.364..35,488.367 rows=9 loops=3)

  • Group Key: admingeom.admingeom_id
  • Buffers: shared hit=13269637
5. 9.525 106,451.895 ↓ 9.1 13,925 3

Nested Loop (cost=3.61..7,025.17 rows=1,534 width=2,600) (actual time=25.385..35,483.965 rows=13,925 loops=3)

  • Buffers: shared hit=13269637
6. 62.316 62.316 ↑ 1.4 18 3

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

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

Append (cost=3.61..127.50 rows=6 width=32) (actual time=1,821.415..1,970.001 rows=774 loops=54)

  • Buffers: shared hit=13266060
8. 106,244.676 106,372.116 ↓ 129.0 774 54

Bitmap Heap Scan on localization_2019 (cost=3.61..127.47 rows=6 width=32) (actual time=1,821.403..1,969.854 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=31211
  • Buffers: shared hit=13266060
9. 127.440 127.440 ↓ 328.9 14,471 54

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

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