explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Ztf5

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 8.328 108,094.716 ↓ 7.0 28 1

GroupAggregate (cost=4,603.92..4,604.04 rows=4 width=60) (actual time=108,084.079..108,094.716 rows=28 loops=1)

  • Group Key: g.admingeom_id, g.label, g.area
  • Buffers: shared hit=13431000
2.          

CTE geoms

3. 712.183 712.183 ↑ 1.1 54 1

Seq Scan on admingeom (cost=0.00..4,214.93 rows=59 width=2,640) (actual time=2.004..712.183 rows=54 loops=1)

  • Filter: ((label <> 'Monde'::text) AND (adminclass_id = 1))
  • Rows Removed by Filter: 41432
  • Buffers: shared hit=4721
4. 25.266 108,086.388 ↓ 10,443.8 41,775 1

Sort (cost=389.00..389.01 rows=4 width=44) (actual time=108,083.572..108,086.388 rows=41,775 loops=1)

  • Sort Key: g.admingeom_id, g.label, g.area
  • Sort Method: quicksort Memory: 4800kB
  • Buffers: shared hit=13431000
5. 9.466 108,061.122 ↓ 10,443.8 41,775 1

Nested Loop (cost=0.28..388.96 rows=4 width=44) (actual time=39.816..108,061.122 rows=41,775 loops=1)

  • Buffers: shared hit=13430991
6. 716.936 716.936 ↑ 1.1 54 1

CTE Scan on geoms g (cost=0.00..1.18 rows=59 width=108) (actual time=2.014..716.936 rows=54 loops=1)

  • Buffers: shared hit=4721
7. 10.422 107,334.720 ↓ 774.0 774 54

Append (cost=0.28..6.56 rows=1 width=32) (actual time=1,850.419..1,987.680 rows=774 loops=54)

  • Buffers: shared hit=13426270
8. 107,324.298 107,324.298 ↓ 774.0 774 54

Index Scan using localization_2019_coord_idx on localization_2019 l (cost=0.28..6.56 rows=1 width=32) (actual time=1,850.358..1,987.487 rows=774 loops=54)

  • Index Cond: ((coord && g.bbox) AND (g.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(g.contour, coord))
  • Rows Removed by Filter: 13697
  • Buffers: shared hit=13425741