#	exclusive	inclusive	rows x	rows	loops	node
1.	1.031	1860.176	↓ 223.0	446	1	→ WindowAgg (cost=36.54..36.58 rows=2 width=32) (actual time=1858.794..1860.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=1000 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=1000 width=32) (actual time=70.184..83.284 rows=574 loops=1)
8.						CTE neighbour
9.	914.693	1840.403	↓ 48.0	48	1	→ Nested Loop (cost=0.00..0.31 rows=1 width=64) (actual time=624.767..1840.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.	1841.049	1841.049	↓ 48.0	48	1	→ CTE Scan on neighbour (cost=0.00..0.03 rows=1 width=64) (actual time=624.818..1841.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	1842.831	↓ 15.5	31	1	→ Unique (cost=0.07..0.08 rows=2 width=32) (actual time=1842.651..1842.831 rows=31 loops=1)
18.	0.298	1842.713	↓ 36.0	72	1	→ Sort (cost=0.07..0.08 rows=2 width=32) (actual time=1842.648..1842.713 rows=72 loops=1) Sort Key: larger.geom Sort Method: quicksort Memory: 44kB
19.	0.168	1842.415	↓ 36.0	72	1	→ Append (cost=0.00..0.06 rows=2 width=32) (actual time=624.829..1842.415 rows=72 loops=1)
20.	1841.258	1841.258	↓ 48.0	48	1	→ CTE Scan on larger (cost=0.00..0.02 rows=1 width=32) (actual time=624.825..1841.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	1847.906	↓ 22.0	22	1	→ Subquery Scan on p (cost=0.05..0.07 rows=1 width=32) (actual time=1847.760..1847.906 rows=22 loops=1)
32.	4.784	1847.684	↑ 1.0	1	1	→ Aggregate (cost=0.05..0.06 rows=1 width=32) (actual time=1847.682..1847.684 rows=1 loops=1)
33.	1842.900	1842.900	↓ 15.5	31	1	→ CTE Scan on to_union (cost=0.00..0.04 rows=2 width=32) (actual time=1842.655..1842.900 rows=31 loops=1)
34.	1.807	1859.145	↓ 223.0	446	1	→ Sort (cost=0.11..0.12 rows=2 width=32) (actual time=1858.782..1859.145 rows=446 loops=1) Sort Key: simply2.geom Sort Method: quicksort Memory: 165kB
35.	0.970	1857.338	↓ 223.0	446	1	→ Unique (cost=0.07..0.08 rows=2 width=32) (actual time=1856.003..1857.338 rows=446 loops=1)
36.	1.542	1856.368	↓ 223.0	446	1	→ Sort (cost=0.07..0.08 rows=2 width=32) (actual time=1856.000..1856.368 rows=446 loops=1) Sort Key: simply2.geom Sort Method: quicksort Memory: 165kB
37.	0.737	1854.826	↓ 223.0	446	1	→ Append (cost=0.00..0.06 rows=2 width=32) (actual time=1847.770..1854.826 rows=446 loops=1)
38.	1847.965	1847.965	↓ 22.0	22	1	→ CTE Scan on simply2 (cost=0.00..0.02 rows=1 width=32) (actual time=1847.767..1847.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)

"WindowAgg  (cost=36.54..36.58 rows=2 width=32) (actual time=1858.794..1860.176 rows=446 loops=1)"
"  CTE poly"
"    ->  Function Scan on __bbox_polygon_by_type  (cost=0.25..12.75 rows=1000 width=36) (actual time=70.171..80.488 rows=574 loops=1)"
"  CTE simply"
"    ->  Subquery Scan on p  (cost=22.50..22.53 rows=1 width=32) (actual time=554.637..562.234 rows=447 loops=1)"
"          ->  Aggregate  (cost=22.50..22.52 rows=1 width=32) (actual time=554.105..554.107 rows=1 loops=1)"
"                ->  CTE Scan on poly  (cost=0.00..20.00 rows=1000 width=32) (actual time=70.184..83.284 rows=574 loops=1)"
"  CTE neighbour"
"    ->  Nested Loop  (cost=0.00..0.31 rows=1 width=64) (actual time=624.767..1840.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))"
"          ->  CTE Scan on simply p1  (cost=0.00..0.02 rows=1 width=88) (actual time=554.659..555.594 rows=447 loops=1)"
"          ->  CTE Scan on simply p2  (cost=0.00..0.02 rows=1 width=88) (actual time=0.002..0.828 rows=447 loops=447)"
"  CTE larger"
"    ->  CTE Scan on neighbour  (cost=0.00..0.03 rows=1 width=64) (actual time=624.818..1841.049 rows=48 loops=1)"
"  CTE snapped"
"    ->  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))"
"  CTE to_union"
"    ->  Unique  (cost=0.07..0.08 rows=2 width=32) (actual time=1842.651..1842.831 rows=31 loops=1)"
"          ->  Sort  (cost=0.07..0.08 rows=2 width=32) (actual time=1842.648..1842.713 rows=72 loops=1)"
"                Sort Key: larger.geom"
"                Sort Method: quicksort  Memory: 44kB"
"                ->  Append  (cost=0.00..0.06 rows=2 width=32) (actual time=624.829..1842.415 rows=72 loops=1)"
"                      ->  CTE Scan on larger  (cost=0.00..0.02 rows=1 width=32) (actual time=624.825..1841.258 rows=48 loops=1)"
"                      ->  CTE Scan on snapped  (cost=0.00..0.02 rows=1 width=32) (actual time=0.113..0.989 rows=24 loops=1)"
"  CTE others"
"    ->  SetOp Except  (cost=0.11..0.13 rows=1 width=32) (actual time=3.505..5.105 rows=424 loops=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"
"                ->  Append  (cost=0.00..0.09 rows=3 width=32) (actual time=0.009..2.210 rows=478 loops=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)"
"                            ->  CTE Scan on simply  (cost=0.00..0.02 rows=1 width=32) (actual time=0.003..0.532 rows=447 loops=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)"
"                            ->  CTE Scan on to_union  (cost=0.00..0.04 rows=2 width=32) (actual time=0.002..0.039 rows=31 loops=1)"
"  CTE simply2"
"    ->  Subquery Scan on p  (cost=0.05..0.07 rows=1 width=32) (actual time=1847.760..1847.906 rows=22 loops=1)"
"          ->  Aggregate  (cost=0.05..0.06 rows=1 width=32) (actual time=1847.682..1847.684 rows=1 loops=1)"
"                ->  CTE Scan on to_union  (cost=0.00..0.04 rows=2 width=32) (actual time=1842.655..1842.900 rows=31 loops=1)"
"  ->  Sort  (cost=0.11..0.12 rows=2 width=32) (actual time=1858.782..1859.145 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=1856.003..1857.338 rows=446 loops=1)"
"              ->  Sort  (cost=0.07..0.08 rows=2 width=32) (actual time=1856.000..1856.368 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=1847.770..1854.826 rows=446 loops=1)"
"                          ->  CTE Scan on simply2  (cost=0.00..0.02 rows=1 width=32) (actual time=1847.767..1847.965 rows=22 loops=1)"
"                          ->  CTE Scan on others  (cost=0.00..0.02 rows=1 width=32) (actual time=3.509..6.124 rows=424 loops=1)"
"Total runtime: 1861.057 ms"

Per node type stats

node type	count	sum of times	% of query
Aggregate	2	475.607 ms	25.6 %
Append	3	1.612 ms	0.1 %
CTE Scan	12	8390.754 ms	451.1 %
Function Scan	1	80.488 ms	4.3 %
Nested Loop	1	914.693 ms	49.2 %
SetOp Except	1	1.153 ms	0.1 %
Sort	4	5.389 ms	0.3 %
Subquery Scan on "SELECT 1"	1	0.864 ms	0.0 %
Subquery Scan on "SELECT 2"	1	0.068 ms	0.0 %
Subquery Scan on p	2	8.349 ms	0.4 %
Unique	2	1.088 ms	0.1 %
WindowAgg	1	1.031 ms	0.1 %

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: 3Tr

Colorize:

Columns visibility:

Per node type stats

Per table stats