explain.depesz.com

A tool for finding a real cause for slow queries.

Result: 1I7

options
Did it help? Consider supporting us - Bitcoin address: 12v2hUztAk2LgzQ9H9LMwuU32urHMjZQnq
# exclusive inclusive rows x rows loops node
1. 1.135 2680.500 ↓ 223.0 446 1

WindowAgg (cost=36.57..36.61 rows=2 width=32) (actual time=2678.981..2680.500 rows=446 loops=1)

2.          

CTE poly

3. 892.611 892.611 ↑ 1.7 574 1

Function Scan on __bbox_polygon_by_type (cost=0.25..12.75 rows=1000 width=36) (actual time=881.659..892.611 rows=574 loops=1)

4.          

CTE simply

5. 30.083 1469.333 ↓ 447.0 447 1

Subquery Scan on p (cost=22.50..22.53 rows=1 width=32) (actual time=1464.192..1469.333 rows=447 loops=1)

6. 543.813 1439.250 ↑ 1.0 1 1

Aggregate (cost=22.50..22.52 rows=1 width=32) (actual time=1439.248..1439.250 rows=1 loops=1)

7. 895.437 895.437 ↑ 1.7 574 1

CTE Scan on poly (cost=0.00..20.00 rows=1000 width=32) (actual time=881.674..895.437 rows=574 loops=1)

8.          

CTE neighbour

9. 850.412 2660.133 ↓ 48.0 48 1

Nested Loop (cost=0.00..0.31 rows=1 width=64) (actual time=1513.666..2660.133 rows=48 loops=1)

  • Join Filter: ((p1.* <> p2.*) AND (p1.geom && st_expand(p2.geom, 1.5::double precision)) AND (p2.geom && st_expand(p1.geom, 1.5::double precision)) AND _st_dwithin(p1.geom, p2.geom, 1.5::double precision))
10. 1465.084 1465.084 ↓ 447.0 447 1

CTE Scan on simply p1 (cost=0.00..0.02 rows=1 width=88) (actual time=1464.214..1465.084 rows=447 loops=1)

11. 344.637 344.637 ↓ 447.0 447 447

CTE Scan on simply p2 (cost=0.00..0.02 rows=1 width=88) (actual time=0.002..0.771 rows=447 loops=447)

12.          

CTE larger

13. 0.101 2661.029 ↓ 23.0 23 1

Unique (cost=0.04..0.05 rows=1 width=64) (actual time=2660.877..2661.029 rows=23 loops=1)

14. 0.260 2660.928 ↓ 48.0 48 1

Sort (cost=0.04..0.04 rows=1 width=64) (actual time=2660.873..2660.928 rows=48 loops=1)

  • Sort Key: (CASE WHEN (st_area(neighbour.geom1) < st_area(neighbour.geom2)) THEN neighbour.geom2 ELSE neighbour.geom1 END)
  • Sort Method: quicksort Memory: 38kB
15. 2660.668 2660.668 ↓ 48.0 48 1

CTE Scan on neighbour (cost=0.00..0.03 rows=1 width=64) (actual time=1513.697..2660.668 rows=48 loops=1)

16.          

CTE snapped

17. 0.068 1.194 ↓ 24.0 24 1

Unique (cost=0.54..0.55 rows=1 width=64) (actual time=1.106..1.194 rows=24 loops=1)

18. 0.125 1.126 ↓ 24.0 24 1

Sort (cost=0.54..0.54 rows=1 width=64) (actual time=1.101..1.126 rows=24 loops=1)

  • Sort Key: (st_snap(neighbour.geom1, neighbour.geom2, (st_distance(neighbour.geom1, neighbour.geom2) + 0.5::double precision)))
  • Sort Method: quicksort Memory: 31kB
19. 1.001 1.001 ↓ 24.0 24 1

CTE Scan on neighbour (cost=0.00..0.53 rows=1 width=64) (actual time=0.118..1.001 rows=24 loops=1)

  • Filter: (st_area(geom2) < st_area(geom1))
20.          

CTE to_union

21. 0.093 2662.710 ↓ 15.5 31 1

Unique (cost=0.07..0.08 rows=2 width=32) (actual time=2662.567..2662.710 rows=31 loops=1)

22. 0.172 2662.617 ↓ 23.5 47 1

Sort (cost=0.07..0.08 rows=2 width=32) (actual time=2662.565..2662.617 rows=47 loops=1)

  • Sort Key: larger.geom
  • Sort Method: quicksort Memory: 37kB
23. 0.102 2662.445 ↓ 23.5 47 1

Append (cost=0.00..0.06 rows=2 width=32) (actual time=2660.888..2662.445 rows=47 loops=1)

24. 2661.087 2661.087 ↓ 23.0 23 1

CTE Scan on larger (cost=0.00..0.02 rows=1 width=32) (actual time=2660.884..2661.087 rows=23 loops=1)

25. 1.256 1.256 ↓ 24.0 24 1

CTE Scan on snapped (cost=0.00..0.02 rows=1 width=32) (actual time=1.109..1.256 rows=24 loops=1)

26.          

CTE others

27. 1.132 5.068 ↓ 424.0 424 1

SetOp Except (cost=0.11..0.13 rows=1 width=32) (actual time=3.516..5.068 rows=424 loops=1)

28. 1.713 3.936 ↓ 159.3 478 1

Sort (cost=0.11..0.12 rows=3 width=32) (actual time=3.508..3.936 rows=478 loops=1)

  • Sort Key: "*SELECT* 1".geom
  • Sort Method: quicksort Memory: 173kB
29. 0.731 2.223 ↓ 159.3 478 1

Append (cost=0.00..0.09 rows=3 width=32) (actual time=0.009..2.223 rows=478 loops=1)

30. 0.833 1.393 ↓ 447.0 447 1

Subquery Scan on "*SELECT* 1" (cost=0.00..0.03 rows=1 width=32) (actual time=0.006..1.393 rows=447 loops=1)

31. 0.560 0.560 ↓ 447.0 447 1

CTE Scan on simply (cost=0.00..0.02 rows=1 width=32) (actual time=0.003..0.560 rows=447 loops=1)

32. 0.063 0.099 ↓ 15.5 31 1

Subquery Scan on "*SELECT* 2" (cost=0.00..0.06 rows=2 width=32) (actual time=0.005..0.099 rows=31 loops=1)

33. 0.036 0.036 ↓ 15.5 31 1

CTE Scan on to_union (cost=0.00..0.04 rows=2 width=32) (actual time=0.002..0.036 rows=31 loops=1)

34.          

CTE simply2

35. 0.230 2668.167 ↓ 22.0 22 1

Subquery Scan on p (cost=0.05..0.07 rows=1 width=32) (actual time=2668.021..2668.167 rows=22 loops=1)

36. 5.147 2667.937 ↑ 1.0 1 1

Aggregate (cost=0.05..0.06 rows=1 width=32) (actual time=2667.936..2667.937 rows=1 loops=1)

37. 2662.790 2662.790 ↓ 15.5 31 1

CTE Scan on to_union (cost=0.00..0.04 rows=2 width=32) (actual time=2662.571..2662.790 rows=31 loops=1)

38. 1.870 2679.365 ↓ 223.0 446 1

Sort (cost=0.11..0.12 rows=2 width=32) (actual time=2678.964..2679.365 rows=446 loops=1)

  • Sort Key: simply2.geom
  • Sort Method: quicksort Memory: 165kB
39. 0.968 2677.495 ↓ 223.0 446 1

Unique (cost=0.07..0.08 rows=2 width=32) (actual time=2676.182..2677.495 rows=446 loops=1)

40. 1.582 2676.527 ↓ 223.0 446 1

Sort (cost=0.07..0.08 rows=2 width=32) (actual time=2676.178..2676.527 rows=446 loops=1)

  • Sort Key: simply2.geom
  • Sort Method: quicksort Memory: 165kB
41. 0.698 2674.945 ↓ 223.0 446 1

Append (cost=0.00..0.06 rows=2 width=32) (actual time=2668.033..2674.945 rows=446 loops=1)

42. 2668.231 2668.231 ↓ 22.0 22 1

CTE Scan on simply2 (cost=0.00..0.02 rows=1 width=32) (actual time=2668.030..2668.231 rows=22 loops=1)

43. 6.016 6.016 ↓ 424.0 424 1

CTE Scan on others (cost=0.00..0.02 rows=1 width=32) (actual time=3.521..6.016 rows=424 loops=1)