explain.depesz.com

PostgreSQL's explain analyze made readable

Result: FA4t

Settings
# exclusive inclusive rows x rows loops node
1. 3,026.498 3,026.498 ↓ 31.0 62 1

CTE Scan on z_final (cost=59,912.64..59,912.76 rows=2 width=44) (actual time=3,022.385..3,026.498 rows=62 loops=1)

  • Filter: (zb <= 1800)
  • Rows Removed by Filter: 6
2.          

CTE v_iso_6

3. 0.037 0.037 ↑ 1.0 214 1

Seq Scan on line_preview (cost=0.00..22.14 rows=214 width=1,505) (actual time=0.011..0.037 rows=214 loops=1)

4.          

CTE inp3

5. 0.577 0.577 ↓ 8.5 68 1

CTE Scan on v_iso_6 z (cost=0.00..16.05 rows=8 width=48) (actual time=0.023..0.577 rows=68 loops=1)

  • Filter: ((zb <= 1800) AND st_isclosed(geom) AND (st_length(geom) > '200'::double precision))
  • Rows Removed by Filter: 146
6.          

CTE g

7. 0.086 59.660 ↑ 14.7 68 1

Result (cost=0.21..265.47 rows=1,000 width=32) (actual time=54.950..59.660 rows=68 loops=1)

8. 4.835 59.574 ↑ 14.7 68 1

ProjectSet (cost=0.21..5.47 rows=1,000 width=32) (actual time=54.948..59.574 rows=68 loops=1)

9. 0.693 54.739 ↑ 1.0 1 1

Aggregate (cost=0.21..0.22 rows=1 width=32) (actual time=54.739..54.739 rows=1 loops=1)

10. 53.428 54.046 ↑ 1.0 1 1

Aggregate (cost=0.18..0.19 rows=1 width=32) (actual time=54.046..54.046 rows=1 loops=1)

11. 0.618 0.618 ↓ 8.5 68 1

CTE Scan on inp3 (cost=0.00..0.16 rows=8 width=32) (actual time=0.023..0.618 rows=68 loops=1)

12.          

CTE v_iso_7

13. 0.401 60.353 ↑ 14.7 68 1

WindowAgg (cost=0.00..32.50 rows=1,000 width=40) (actual time=54.956..60.353 rows=68 loops=1)

14. 59.952 59.952 ↑ 14.7 68 1

CTE Scan on g (cost=0.00..20.00 rows=1,000 width=32) (actual time=54.952..59.952 rows=68 loops=1)

15.          

CTE z_with_bounds

16. 0.686 3,021.797 ↑ 7.6 131 1

WindowAgg (cost=58,386.97..58,406.97 rows=1,000 width=52) (actual time=3,021.100..3,021.797 rows=131 loops=1)

17. 1.327 3,021.111 ↑ 7.6 131 1

Sort (cost=58,386.97..58,389.47 rows=1,000 width=44) (actual time=3,021.096..3,021.111 rows=131 loops=1)

  • Sort Key: v7.zone_id, v6.zb DESC
  • Sort Method: quicksort Memory: 2832kB
18. 2,956.444 3,019.784 ↑ 7.6 131 1

Nested Loop Left Join (cost=0.00..58,337.14 rows=1,000 width=44) (actual time=55.409..3,019.784 rows=131 loops=1)

  • Join Filter: ((v7.geom && v6.geom) AND _st_touches(v7.geom, v6.geom))
  • Rows Removed by Join Filter: 14421
19. 60.688 60.688 ↑ 14.7 68 1

CTE Scan on v_iso_7 v7 (cost=0.00..20.00 rows=1,000 width=40) (actual time=54.957..60.688 rows=68 loops=1)

20. 2.652 2.652 ↑ 1.0 214 68

CTE Scan on v_iso_6 v6 (cost=0.00..4.28 rows=214 width=36) (actual time=0.000..0.039 rows=214 loops=68)

21.          

CTE z_count_holes

22. 3,022.536 3,022.536 ↓ 13.6 68 1

CTE Scan on z_with_bounds (cost=0.00..22.51 rows=5 width=56) (actual time=3,021.103..3,022.536 rows=68 loops=1)

  • Filter: (sel = 1)
  • Rows Removed by Filter: 63
23.          

CTE z_final

24. 3.544 3,026.301 ↓ 13.6 68 1

Nested Loop Left Join (cost=0.00..1,147.00 rows=5 width=44) (actual time=3,021.133..3,026.301 rows=68 loops=1)

  • Join Filter: ((z_1.geom ~ p.geom) AND _st_contains(z_1.geom, p.geom))
  • Rows Removed by Join Filter: 536
25. 3,022.689 3,022.689 ↓ 13.6 68 1

CTE Scan on z_count_holes z_1 (cost=0.00..0.10 rows=5 width=48) (actual time=3,021.104..3,022.689 rows=68 loops=1)

26. 0.045 0.068 ↑ 106.2 8 68

Materialize (cost=0.00..22.75 rows=850 width=36) (actual time=0.000..0.001 rows=8 loops=68)

27. 0.023 0.023 ↑ 106.2 8 1

Seq Scan on points p (cost=0.00..18.50 rows=850 width=36) (actual time=0.022..0.023 rows=8 loops=1)