#	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)

"WindowAgg  (cost=36.57..36.61 rows=2 width=32) (actual time=2678.981..2680.500 rows=446 loops=1)"
"  CTE poly"
"    ->  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)"
"  CTE simply"
"    ->  Subquery Scan on p  (cost=22.50..22.53 rows=1 width=32) (actual time=1464.192..1469.333 rows=447 loops=1)"
"          ->  Aggregate  (cost=22.50..22.52 rows=1 width=32) (actual time=1439.248..1439.250 rows=1 loops=1)"
"                ->  CTE Scan on poly  (cost=0.00..20.00 rows=1000 width=32) (actual time=881.674..895.437 rows=574 loops=1)"
"  CTE neighbour"
"    ->  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))"
"          ->  CTE Scan on simply p1  (cost=0.00..0.02 rows=1 width=88) (actual time=1464.214..1465.084 rows=447 loops=1)"
"          ->  CTE Scan on simply p2  (cost=0.00..0.02 rows=1 width=88) (actual time=0.002..0.771 rows=447 loops=447)"
"  CTE larger"
"    ->  Unique  (cost=0.04..0.05 rows=1 width=64) (actual time=2660.877..2661.029 rows=23 loops=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"
"                ->  CTE Scan on neighbour  (cost=0.00..0.03 rows=1 width=64) (actual time=1513.697..2660.668 rows=48 loops=1)"
"  CTE snapped"
"    ->  Unique  (cost=0.54..0.55 rows=1 width=64) (actual time=1.106..1.194 rows=24 loops=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"
"                ->  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))"
"  CTE to_union"
"    ->  Unique  (cost=0.07..0.08 rows=2 width=32) (actual time=2662.567..2662.710 rows=31 loops=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"
"                ->  Append  (cost=0.00..0.06 rows=2 width=32) (actual time=2660.888..2662.445 rows=47 loops=1)"
"                      ->  CTE Scan on larger  (cost=0.00..0.02 rows=1 width=32) (actual time=2660.884..2661.087 rows=23 loops=1)"
"                      ->  CTE Scan on snapped  (cost=0.00..0.02 rows=1 width=32) (actual time=1.109..1.256 rows=24 loops=1)"
"  CTE others"
"    ->  SetOp Except  (cost=0.11..0.13 rows=1 width=32) (actual time=3.516..5.068 rows=424 loops=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"
"                ->  Append  (cost=0.00..0.09 rows=3 width=32) (actual time=0.009..2.223 rows=478 loops=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)"
"                            ->  CTE Scan on simply  (cost=0.00..0.02 rows=1 width=32) (actual time=0.003..0.560 rows=447 loops=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)"
"                            ->  CTE Scan on to_union  (cost=0.00..0.04 rows=2 width=32) (actual time=0.002..0.036 rows=31 loops=1)"
"  CTE simply2"
"    ->  Subquery Scan on p  (cost=0.05..0.07 rows=1 width=32) (actual time=2668.021..2668.167 rows=22 loops=1)"
"          ->  Aggregate  (cost=0.05..0.06 rows=1 width=32) (actual time=2667.936..2667.937 rows=1 loops=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)"
"  ->  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"
"        ->  Unique  (cost=0.07..0.08 rows=2 width=32) (actual time=2676.182..2677.495 rows=446 loops=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"
"                    ->  Append  (cost=0.00..0.06 rows=2 width=32) (actual time=2668.033..2674.945 rows=446 loops=1)"
"                          ->  CTE Scan on simply2  (cost=0.00..0.02 rows=1 width=32) (actual time=2668.030..2668.231 rows=22 loops=1)"
"                          ->  CTE Scan on others  (cost=0.00..0.02 rows=1 width=32) (actual time=3.521..6.016 rows=424 loops=1)"
"Total runtime: 2681.416 ms"

Per node type stats

node type	count	sum of times	% of query
Aggregate	2	548.960 ms	20.5 %
Append	3	1.531 ms	0.1 %
CTE Scan	12	13366.803 ms	498.7 %
Function Scan	1	892.611 ms	33.3 %
Nested Loop	1	850.412 ms	31.7 %
SetOp Except	1	1.132 ms	0.0 %
Sort	6	5.722 ms	0.2 %
Subquery Scan on "SELECT 1"	1	0.833 ms	0.0 %
Subquery Scan on "SELECT 2"	1	0.063 ms	0.0 %
Subquery Scan on p	2	30.313 ms	1.1 %
Unique	4	1.230 ms	0.0 %
WindowAgg	1	1.135 ms	0.0 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table

explain.depesz.com

A tool for finding a real cause for slow queries.

Result: 1I7

Colorize:

Columns visibility:

Per node type stats

Per table stats