explain.depesz.com

PostgreSQL's explain analyze made readable

Result: SMmi

Settings
# exclusive inclusive rows x rows loops node
1. 0.399 808.894 ↓ 0.0 0 1

Insert on intersect_2012 (cost=0.08..0.32 rows=1 width=314) (actual time=808.894..808.894 rows=0 loops=1)

2. 0.388 808.495 ↑ 1.0 1 1

Subquery Scan on *SELECT* (cost=0.08..0.32 rows=1 width=314) (actual time=808.489..808.495 rows=1 loops=1)

3. 0.005 808.107 ↑ 1.0 1 1

Limit (cost=0.08..0.32 rows=1 width=310) (actual time=808.103..808.107 rows=1 loops=1)

4. 0.000 808.102 ↑ 2,677,840,517.0 1 1

Append (cost=0.08..634,882,893.67 rows=2,677,840,517 width=310) (actual time=808.102..808.102 rows=1 loops=1)

5. 4.313 808.102 ↑ 875,507,784.0 1 1

Nested Loop (cost=0.08..43,279,151.76 rows=875,507,784 width=68) (actual time=808.101..808.102 rows=1 loops=1)

6. 0.610 0.610 ↑ 1,755,692.0 1 1

Seq Scan on c2c_year_buff a (cost=0.00..77,792.08 rows=1,755,692 width=423) (actual time=0.610..0.610 rows=1 loops=1)

7. 803.179 803.179 ↑ 51.0 1 1

Index Scan using changetypebuff_geom_geom_idx on changetypebuff b (cost=0.08..24.20 rows=51 width=481) (actual time=803.179..803.179 rows=1 loops=1)

  • Index Cond: (a.geom && geom)
  • Filter: (_st_intersects(a.geom, geom) AND ((NOT (geom ~ a.geom)) OR (NOT _st_contains(geom, a.geom))) AND ((NOT (a.geom ~ geom)) OR (NOT _st_contains(a.geom, geom))))
  • Rows Removed by Filter: 2
8. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=4.94..583,570,220.36 rows=1,802,332,733 width=427) (never executed)

9. 0.000 0.000 ↓ 0.0 0

Seq Scan on c2c_year_buff a_1 (cost=0.00..77,792.08 rows=1,755,692 width=423) (never executed)

10. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on changetypebuff b_1 (cost=4.94..329.26 rows=1,027 width=481) (never executed)

  • Recheck Cond: ((geom ~ a_1.geom) OR (a_1.geom ~ geom))
  • Filter: (((geom ~ a_1.geom) AND _st_contains(geom, a_1.geom)) OR ((a_1.geom ~ geom) AND _st_contains(a_1.geom, geom)))
11. 0.000 0.000 ↓ 0.0 0

BitmapOr (cost=4.94..4.94 rows=3,080 width=0) (never executed)

12. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on changetypebuff_geom_geom_idx (cost=0.00..2.42 rows=1,540 width=0) (never executed)

  • Index Cond: (geom ~ a_1.geom)
13. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on changetypebuff_geom_geom_idx (cost=0.00..2.42 rows=1,540 width=0) (never executed)

  • Index Cond: (a_1.geom ~ geom)
Planning time : 73.822 ms
Execution time : 809.461 ms