explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1cxd

Settings
# exclusive inclusive rows x rows loops node
1. 65.381 3,478.101 ↓ 144.0 144 1

Hash Right Join (cost=204,650.10..210,577.56 rows=1 width=76) (actual time=2,087.415..3,478.101 rows=144 loops=1)

  • Hash Cond: ((b.geometry_key)::text = (c.geometry_key)::text)
  • Filter: ((CASE WHEN (b.geometry_key IS NULL) THEN 'NO'::text ELSE 'YES'::text END IS NOT NULL) AND (CASE WHEN (b.geometry_key IS NULL) THEN 'NO'::text ELSE 'YES'::text END = ANY ('{YES,NO}'::text[])))
  • Buffers: shared hit=107289, temp read=7286 written=7300
2. 100.037 3,412.263 ↓ 4.2 555,713 1

Unique (cost=204,304.43..208,570.82 rows=131,566 width=20) (actual time=2,086.618..3,412.263 rows=555,713 loops=1)

  • Buffers: shared hit=106967, temp read=7286 written=7300
3. 2,717.169 3,312.226 ↑ 1.0 556,760 1

Sort (cost=204,304.43..205,726.56 rows=568,852 width=20) (actual time=2,086.617..3,312.226 rows=556,760 loops=1)

  • Sort Key: b.id_season, b.geometry_key
  • Sort Method: external merge Disk: 16840kB
  • Buffers: shared hit=106967, temp read=7286 written=7300
4. 334.834 595.057 ↑ 1.0 556,760 1

Hash Join (cost=94,972.85..138,259.74 rows=568,852 width=20) (actual time=142.453..595.057 rows=556,760 loops=1)

  • Hash Cond: (b.id_structure = c_1.id_structure)
  • Buffers: shared hit=106967, temp read=5181 written=5181
5. 119.564 119.564 ↑ 1.0 829,455 1

Seq Scan on structure_season b (cost=0.00..28,545.55 rows=829,455 width=28) (actual time=0.006..119.564 rows=829,455 loops=1)

  • Buffers: shared hit=20251
6. 49.444 140.659 ↑ 1.0 311,732 1

Hash (cost=89,842.60..89,842.60 rows=312,660 width=8) (actual time=140.659..140.659 rows=311,732 loops=1)

  • Buckets: 131072 Batches: 8 Memory Usage: 2545kB
  • Buffers: shared hit=86716, temp written=929
7. 91.215 91.215 ↑ 1.0 311,732 1

Seq Scan on structure_geometry c_1 (cost=0.00..89,842.60 rows=312,660 width=8) (actual time=0.005..91.215 rows=311,732 loops=1)

  • Buffers: shared hit=86716
8. 0.032 0.457 ↓ 18.0 36 1

Hash (cost=345.64..345.64 rows=2 width=4,366) (actual time=0.457..0.457 rows=36 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 83kB
  • Buffers: shared hit=258
9. 0.246 0.425 ↓ 18.0 36 1

Bitmap Heap Scan on map_geometry c (cost=5.15..345.64 rows=2 width=4,366) (actual time=0.407..0.425 rows=36 loops=1)

  • Recheck Cond: ((geom IS NOT NULL) AND (geom && '01030000000100000005000000FFFFFFDFB01A48C05296DAF771D735C0FFFFFFDFB01A48C0FB72F793FAD135C0FFFFFF1FBF1748C0FB72F793FAD135C0FFFFFF1FBF1748C05296DAF771D735C0FFFFFFDFB01A48C05296DAF771D735C0'::geome (...)
  • Filter: ((id_map IS NOT NULL) AND (id_map = 342017))
  • Rows Removed by Filter: 304
  • Heap Blocks: exact=233
  • Buffers: shared hit=258
10. 0.179 0.179 ↓ 4.0 340 1

Bitmap Index Scan on gidx_map_geometry_temp_geom (cost=0.00..5.15 rows=86 width=0) (actual time=0.179..0.179 rows=340 loops=1)

  • Index Cond: ((geom IS NOT NULL) AND (geom && '01030000000100000005000000FFFFFFDFB01A48C05296DAF771D735C0FFFFFFDFB01A48C0FB72F793FAD135C0FFFFFF1FBF1748C0FB72F793FAD135C0FFFFFF1FBF1748C05296DAF771D735C0FFFFFFDFB01A48C05296DAF771D735C0'::g (...)
  • Buffers: shared hit=25
Planning time : 0.364 ms
Execution time : 3,483.232 ms