explain.depesz.com

A tool for finding a real cause for slow queries.

Result: 3Tr

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

WindowAgg (cost=36.54..36.58 rows=2 width=32) (actual time=1,858.794..1,860.176 rows=446 loops=1)

2.          

CTE poly

3. 80.488 80.488 ↑ 1.7 574 1

Function Scan on __bbox_polygon_by_type (cost=0.25..12.75 rows=1,000 width=36) (actual time=70.171..80.488 rows=574 loops=1)

4.          

CTE simply

5. 8.127 562.234 ↓ 447.0 447 1

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

6. 470.823 554.107 ↑ 1.0 1 1

Aggregate (cost=22.50..22.52 rows=1 width=32) (actual time=554.105..554.107 rows=1 loops=1)

7. 83.284 83.284 ↑ 1.7 574 1

CTE Scan on poly (cost=0.00..20.00 rows=1,000 width=32) (actual time=70.184..83.284 rows=574 loops=1)

8.          

CTE neighbour

9. 914.693 1,840.403 ↓ 48.0 48 1

Nested Loop (cost=0.00..0.31 rows=1 width=64) (actual time=624.767..1,840.403 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. 555.594 555.594 ↓ 447.0 447 1

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

11. 370.116 370.116 ↓ 447.0 447 447

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

12.          

CTE larger

13. 1,841.049 1,841.049 ↓ 48.0 48 1

CTE Scan on neighbour (cost=0.00..0.03 rows=1 width=64) (actual time=624.818..1,841.049 rows=48 loops=1)

14.          

CTE snapped

15. 0.904 0.904 ↓ 24.0 24 1

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

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

CTE to_union

17. 0.118 1,842.831 ↓ 15.5 31 1

Unique (cost=0.07..0.08 rows=2 width=32) (actual time=1,842.651..1,842.831 rows=31 loops=1)

18. 0.298 1,842.713 ↓ 36.0 72 1

Sort (cost=0.07..0.08 rows=2 width=32) (actual time=1,842.648..1,842.713 rows=72 loops=1)

  • Sort Key: larger.geom
  • Sort Method: quicksort Memory: 44kB
19. 0.168 1,842.415 ↓ 36.0 72 1

Append (cost=0.00..0.06 rows=2 width=32) (actual time=624.829..1,842.415 rows=72 loops=1)

20. 1,841.258 1,841.258 ↓ 48.0 48 1

CTE Scan on larger (cost=0.00..0.02 rows=1 width=32) (actual time=624.825..1,841.258 rows=48 loops=1)

21. 0.989 0.989 ↓ 24.0 24 1

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

22.          

CTE others

23. 1.153 5.105 ↓ 424.0 424 1

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

24. 1.742 3.952 ↓ 159.3 478 1

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

  • Sort Key: "*SELECT* 1".geom
  • Sort Method: quicksort Memory: 173kB
25. 0.707 2.210 ↓ 159.3 478 1

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

26. 0.864 1.396 ↓ 447.0 447 1

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

27. 0.532 0.532 ↓ 447.0 447 1

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

28. 0.068 0.107 ↓ 15.5 31 1

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

29. 0.039 0.039 ↓ 15.5 31 1

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

30.          

CTE simply2

31. 0.222 1,847.906 ↓ 22.0 22 1

Subquery Scan on p (cost=0.05..0.07 rows=1 width=32) (actual time=1,847.760..1,847.906 rows=22 loops=1)

32. 4.784 1,847.684 ↑ 1.0 1 1

Aggregate (cost=0.05..0.06 rows=1 width=32) (actual time=1,847.682..1,847.684 rows=1 loops=1)

33. 1,842.900 1,842.900 ↓ 15.5 31 1

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

34. 1.807 1,859.145 ↓ 223.0 446 1

Sort (cost=0.11..0.12 rows=2 width=32) (actual time=1,858.782..1,859.145 rows=446 loops=1)

  • Sort Key: simply2.geom
  • Sort Method: quicksort Memory: 165kB
35. 0.970 1,857.338 ↓ 223.0 446 1

Unique (cost=0.07..0.08 rows=2 width=32) (actual time=1,856.003..1,857.338 rows=446 loops=1)

36. 1.542 1,856.368 ↓ 223.0 446 1

Sort (cost=0.07..0.08 rows=2 width=32) (actual time=1,856.000..1,856.368 rows=446 loops=1)

  • Sort Key: simply2.geom
  • Sort Method: quicksort Memory: 165kB
37. 0.737 1,854.826 ↓ 223.0 446 1

Append (cost=0.00..0.06 rows=2 width=32) (actual time=1,847.770..1,854.826 rows=446 loops=1)

38. 1,847.965 1,847.965 ↓ 22.0 22 1

CTE Scan on simply2 (cost=0.00..0.02 rows=1 width=32) (actual time=1,847.767..1,847.965 rows=22 loops=1)

39. 6.124 6.124 ↓ 424.0 424 1

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