PEV2 1.14.0
Execution time: 9s 479ms
Planning time: 0.242ms
Triggers: N/A
Main Query Plan
#1 Limit
#26 └ Sort
#27  └ CTE Scan
CTE low_points
#2 WindowAgg
#3 └ Sort
#4  └ WindowAgg
#5   └ Sort
#6    └ Nested Loop
#7     ├ Seq Scan
#8     └ Function Scan
CTE zone
#9 Recursive Union
#10 ├ CTE Scan
#11 └ Nested Loop
#12  ├ CTE Scan
#13  └ WorkTable Scan on zone b
CTE mult
#14 Recursive Union
#15 ├ Result
#16 └ Hash Join
#17  ├ WorkTable Scan on mult mult_1
#18  └ Hash
#19   └ Subquery Scan
#20    └ WindowAgg
#21     └ Subquery Scan
#22      └ Limit
#23       └ Sort
#24        └ HashAggregate
#25         └ CTE Scan

Limit

#1

Sort

#26
by mult.pt DESC

CTE Scan

#27
CTE low_points

WindowAgg

#2

Sort

#3
by _.x, day9.line_number

WindowAgg

#4

Sort

#5
by day9.line_number, _.x

Nested Loop

#6

Seq Scan

#7
on day9

Function Scan

#8
on regexp_split_to_table as _
CTE zone

Recursive Union

#9

CTE Scan

#10

Nested Loop

#11

CTE Scan

#12
on l

WorkTable Scan on zone b

#13
CTE mult

Recursive Union

#14

Result

#15

Hash Join

#16
on mult_1.pt = biggest.id

WorkTable Scan on mult mult_1

#17

Hash

#18

Subquery Scan

#19
on biggest

WindowAgg

#20

Subquery Scan

#21
on area

Limit

#22

Sort

#23
by (count(*)) DESC

HashAggregate

#24
by zone.orig_x, zone.orig_y

CTE Scan

#25
 Limit  (cost=7504956242.12..7504956242.13 rows=1 width=12) (actual time=9377.550..9377.555 rows=1 loops=1)
   CTE low_points
     ->  WindowAgg  (cost=21862.64..28612.64 rows=100000 width=17) (actual time=14.035..21.804 rows=10000 loops=1)
           ->  Sort  (cost=21862.64..22112.64 rows=100000 width=52) (actual time=14.025..14.531 rows=10000 loops=1)
                 Sort Key: _.x, day9.line_number
                 Sort Method: quicksort  Memory: 1166kB
                 ->  WindowAgg  (cost=10307.82..13557.82 rows=100000 width=52) (actual time=4.106..10.316 rows=10000 loops=1)
                       ->  Sort  (cost=10307.82..10557.82 rows=100000 width=44) (actual time=4.099..4.487 rows=10000 loops=1)
                             Sort Key: day9.line_number, _.x
                             Sort Method: quicksort  Memory: 853kB
                             ->  Nested Loop  (cost=0.00..2003.00 rows=100000 width=44) (actual time=0.037..3.129 rows=10000 loops=1)
                                   ->  Seq Scan on day9  (cost=0.00..3.00 rows=100 width=105) (actual time=0.010..0.021 rows=100 loops=1)
                                   ->  Function Scan on regexp_split_to_table _  (cost=0.00..10.00 rows=1000 width=40) (actual time=0.017..0.023 rows=100 loops=100)
   CTE zone
     ->  Recursive Union  (cost=0.00..7500332988.40 rows=16674420 width=24) (actual time=14.061..9365.580 rows=7442 loops=1)
           ->  CTE Scan on low_points  (cost=0.00..2000.00 rows=50000 width=24) (actual time=14.059..23.639 rows=208 loops=1)
                 Filter: is_min
                 Rows Removed by Filter: 9792
           ->  Nested Loop  (cost=0.00..749999750.00 rows=1662442 width=24) (actual time=0.697..1036.096 rows=2483 loops=9)
                 Join Filter: ((((b.x = (l.x - 1)) OR (b.x = (l.x + 1))) AND (b.y = l.y)) OR (((b.y = (l.y - 1)) OR (b.y = (l.y + 1))) AND (b.x = l.x)))
                 Rows Removed by Join Filter: 6151224
                 ->  CTE Scan on low_points l  (cost=0.00..2250.00 rows=33333 width=12) (actual time=0.001..2.485 rows=7442 loops=9)
                       Filter: (height < 9)
                       Rows Removed by Filter: 2558
                 ->  WorkTable Scan on zone b  (cost=0.00..10000.00 rows=500000 width=24) (actual time=0.000..0.039 rows=827 loops=66978)
   CTE mult
     ->  Recursive Union  (cost=0.00..4594640.31 rows=31 width=12) (actual time=0.003..9377.543 rows=4 loops=1)
           ->  Result  (cost=0.00..0.01 rows=1 width=12) (actual time=0.001..0.001 rows=1 loops=1)
           ->  Hash Join  (cost=459463.69..459463.97 rows=3 width=12) (actual time=2344.384..2344.384 rows=1 loops=4)
                 Hash Cond: (mult_1.pt = biggest.id)
                 ->  WorkTable Scan on mult mult_1  (cost=0.00..0.20 rows=10 width=12) (actual time=0.000..0.000 rows=1 loops=4)
                 ->  Hash  (cost=459463.65..459463.65 rows=3 width=16) (actual time=9377.522..9377.523 rows=3 loops=1)
                       Buckets: 1024  Batches: 1  Memory Usage: 9kB
                       ->  Subquery Scan on biggest  (cost=459463.54..459463.65 rows=3 width=16) (actual time=9377.516..9377.520 rows=3 loops=1)
                             ->  WindowAgg  (cost=459463.54..459463.62 rows=3 width=16) (actual time=9377.516..9377.519 rows=3 loops=1)
                                   ->  Subquery Scan on area  (cost=459463.54..459463.58 rows=3 width=8) (actual time=9377.511..9377.513 rows=3 loops=1)
                                         ->  Limit  (cost=459463.54..459463.55 rows=3 width=20) (actual time=9377.510..9377.512 rows=3 loops=1)
                                               ->  Sort  (cost=459463.54..459563.54 rows=40000 width=20) (actual time=9377.510..9377.511 rows=3 loops=1)
                                                     Sort Key: (count(*)) DESC
                                                     Sort Method: top-N heapsort  Memory: 25kB
                                                     ->  HashAggregate  (cost=458546.55..458946.55 rows=40000 width=20) (actual time=9377.292..9377.487 rows=208 loops=1)
                                                           Group Key: zone.orig_x, zone.orig_y
                                                           Batches: 1  Memory Usage: 1585kB
                                                           ->  CTE Scan on zone  (cost=0.00..333488.40 rows=16674420 width=12) (actual time=14.062..9370.392 rows=7442 loops=1)
   ->  Sort  (cost=0.78..0.85 rows=31 width=12) (actual time=9377.549..9377.549 rows=1 loops=1)
         Sort Key: mult.pt DESC
         Sort Method: top-N heapsort  Memory: 25kB
         ->  CTE Scan on mult  (cost=0.00..0.62 rows=31 width=12) (actual time=0.004..9377.545 rows=4 loops=1)
 Planning Time: 0.242 ms
 Execution Time: 9479.367 ms
Table Count Time
day9 1 0.021ms 0%
#7 Seq Scan 0.021ms 0%
Function Count Time
regexp_split_to_table 1 2.3ms 0%
#8 Function Scan 2.3ms 0%
Node Type Count Time
CTE Scan 4 18s 794ms 198%
#27 CTE Scan 9s 378ms 99%
#25 CTE Scan 9s 370ms 99%
#10 CTE Scan 23.6ms 0%
#12 CTE Scan 22.4ms 0%
Nested Loop 2 6s 691ms 71%
#11 Nested Loop 6s 690ms 71%
#6 Nested Loop 0.808ms 0%
WorkTable Scan on zone b 1 2s 612ms 28%
#13 WorkTable Scan on zone b 2s 612ms 28%
Recursive Union 2 17.1ms 0%
#9 Recursive Union 17.1ms 0%
#14 Recursive Union 0.006ms 0%
WindowAgg 3 13.1ms 0%
#2 WindowAgg 7.27ms 0%
#4 WindowAgg 5.83ms 0%
#20 WindowAgg 0.006ms 0%
HashAggregate 1 7.09ms 0%
#24 HashAggregate 7.09ms 0%
Sort 4 5.6ms 0%
#3 Sort 4.21ms 0%
#5 Sort 1.36ms 0%
#23 Sort 0.024ms 0%
#26 Sort 0.004ms 0%
Function Scan 1 2.3ms 0%
#8 Function Scan 2.3ms 0%
Seq Scan 1 0.021ms 0%
#7 Seq Scan 0.021ms 0%
Hash Join 1 0.013ms 0%
#16 Hash Join 0.013ms 0%
Limit 2 0.007ms 0%
#1 Limit 0.006ms 0%
#22 Limit 0.001ms 0%
Hash 1 0.003ms 0%
#18 Hash 0.003ms 0%
Subquery Scan 2 0.002ms 0%
#21 Subquery Scan 0.001ms 0%
#19 Subquery Scan 0.001ms 0%
Result 1 0.001ms 0%
#15 Result 0.001ms 0%
WorkTable Scan on mult mult_1 1 0ms 0%
#17 WorkTable Scan on mult mult_1 0ms 0%
Index Count Time
No index used