1.14.0
Execution time:
1m 38s
Planning time:
0.244ms
Triggers: N/A
| Main Query Plan | |||
|---|---|---|---|
| #1 | Limit | ||
| #19 | â”” WindowAgg | ||
| #20 |  └ Sort | ||
| #21 |   └ Limit | ||
| #22 |    └ Sort | ||
| #23 |     └ Subquery Scan | ||
| #24 |      └ GroupAggregate | ||
| #25 |       └ Sort | ||
| #26 |        └ Subquery Scan | ||
| #27 |         └ GroupAggregate | ||
| #28 |          └ Sort | ||
| #29 |           └ CTE Scan | ||
| CTE map | |||
| #2 | WindowAgg | ||
| #3 | â”” Sort | ||
| #4 |  └ WindowAgg | ||
| #5 |   └ Sort | ||
| #6 |    └ Nested Loop | ||
| #7 |     ├ Seq Scan | ||
| #8 |     └ Function Scan | ||
| CTE map_with_drain | |||
| #9 | Recursive Union | ||
| #10 | ├ CTE Scan | ||
| #11 | â”” Merge Join | ||
| #12 |  ├ Sort | ||
| #13 |  │└ WorkTable Scan | ||
| #14 |  └ Materialize | ||
| #15 |   └ Sort | ||
| #16 |    └ Nested Loop | ||
| #17 |     ├ CTE Scan | ||
| #18 |     └ CTE Scan | ||
[
{
"Plan": {
"Node Type": "Limit",
"Parallel Aware": false,
"Startup Cost": 1280576375.17,
"Total Cost": 1280576375.20,
"Plan Rows": 1,
"Plan Width": 16,
"Actual Startup Time": 98047.771,
"Actual Total Time": 98047.777,
"Actual Rows": 1,
"Actual Loops": 1,
"Output": ["(((basins.size * lead(basins.size) OVER (?)) * lead(basins.size, 2) OVER (?)))", "basins.size"],
"Shared Hit Blocks": 2,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "WindowAgg",
"Parent Relationship": "InitPlan",
"Subplan Name": "CTE map",
"Parallel Aware": false,
"Startup Cost": 28358.64,
"Total Cost": 32358.64,
"Plan Rows": 100000,
"Plan Width": 36,
"Actual Startup Time": 16.679,
"Actual Total Time": 22.881,
"Actual Rows": 10000,
"Actual Loops": 1,
"Output": ["(_.x_pos)::integer", "src.y_pos", "(_.height_text)::integer", "(lag((_.height_text)::integer) OVER (?))", "(lead((_.height_text)::integer) OVER (?))", "lag((_.height_text)::integer) OVER (?)", "lead((_.height_text)::integer) OVER (?)", "_.x_pos"],
"Shared Hit Blocks": 2,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "Sort",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Startup Cost": 28358.64,
"Total Cost": 28608.64,
"Plan Rows": 100000,
"Plan Width": 52,
"Actual Startup Time": 16.675,
"Actual Total Time": 17.053,
"Actual Rows": 10000,
"Actual Loops": 1,
"Output": ["src.y_pos", "_.x_pos", "_.height_text", "(lag((_.height_text)::integer) OVER (?))", "(lead((_.height_text)::integer) OVER (?))"],
"Sort Key": ["src.y_pos", "_.x_pos"],
"Sort Method": "quicksort",
"Sort Space Used": 1166,
"Sort Space Type": "Memory",
"Shared Hit Blocks": 2,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "WindowAgg",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Startup Cost": 13384.32,
"Total Cost": 16634.32,
"Plan Rows": 100000,
"Plan Width": 52,
"Actual Startup Time": 7.561,
"Actual Total Time": 13.336,
"Actual Rows": 10000,
"Actual Loops": 1,
"Output": ["src.y_pos", "_.x_pos", "_.height_text", "lag((_.height_text)::integer) OVER (?)", "lead((_.height_text)::integer) OVER (?)"],
"Shared Hit Blocks": 2,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "Sort",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Startup Cost": 13384.32,
"Total Cost": 13634.32,
"Plan Rows": 100000,
"Plan Width": 44,
"Actual Startup Time": 7.555,
"Actual Total Time": 8.121,
"Actual Rows": 10000,
"Actual Loops": 1,
"Output": ["src.y_pos", "_.x_pos", "_.height_text"],
"Sort Key": ["_.x_pos", "src.y_pos"],
"Sort Method": "quicksort",
"Sort Space Used": 1166,
"Sort Space Type": "Memory",
"Shared Hit Blocks": 2,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "Nested Loop",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Join Type": "Inner",
"Startup Cost": 0.00,
"Total Cost": 2003.00,
"Plan Rows": 100000,
"Plan Width": 44,
"Actual Startup Time": 0.048,
"Actual Total Time": 4.642,
"Actual Rows": 10000,
"Actual Loops": 1,
"Output": ["src.y_pos", "_.x_pos", "_.height_text"],
"Inner Unique": false,
"Shared Hit Blocks": 2,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "Seq Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Relation Name": "2021_day_09",
"Schema": "aoc",
"Alias": "src",
"Startup Cost": 0.00,
"Total Cost": 3.00,
"Plan Rows": 100,
"Plan Width": 105,
"Actual Startup Time": 0.006,
"Actual Total Time": 0.015,
"Actual Rows": 100,
"Actual Loops": 1,
"Output": ["src.y_pos", "src.data"],
"Shared Hit Blocks": 2,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0
},
{
"Node Type": "Function Scan",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Function Name": "regexp_split_to_table",
"Schema": "pg_catalog",
"Alias": "_",
"Startup Cost": 0.00,
"Total Cost": 10.00,
"Plan Rows": 1000,
"Plan Width": 40,
"Actual Startup Time": 0.034,
"Actual Total Time": 0.040,
"Actual Rows": 100,
"Actual Loops": 100,
"Output": ["_.height_text", "_.x_pos"],
"Function Call": "regexp_split_to_table(src.data, ''::text)",
"Shared Hit Blocks": 0,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0
}
]
}
]
}
]
}
]
}
]
},
{
"Node Type": "Recursive Union",
"Parent Relationship": "InitPlan",
"Subplan Name": "CTE map_with_drain",
"Parallel Aware": false,
"Startup Cost": 0.00,
"Total Cost": 1261607735.71,
"Plan Rows": 86810940,
"Plan Width": 16,
"Actual Startup Time": 16.695,
"Actual Total Time": 98022.607,
"Actual Rows": 21608,
"Actual Loops": 1,
"Shared Hit Blocks": 2,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "CTE Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"CTE Name": "map",
"Alias": "map",
"Startup Cost": 0.00,
"Total Cost": 4000.00,
"Plan Rows": 6250,
"Plan Width": 16,
"Actual Startup Time": 16.695,
"Actual Total Time": 25.387,
"Actual Rows": 242,
"Actual Loops": 1,
"Output": ["map.x_pos", "map.y_pos", "map.x_pos", "map.y_pos"],
"Filter": "(((map.height_self < map.height_up) IS DISTINCT FROM false) AND ((map.height_self < map.height_down) IS DISTINCT FROM false) AND ((map.height_self < map.height_left) IS DISTINCT FROM false) AND ((map.height_self < map.height_right) IS DISTINCT FROM false))",
"Rows Removed by Filter": 9758,
"Shared Hit Blocks": 2,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0
},
{
"Node Type": "Merge Join",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Join Type": "Inner",
"Startup Cost": 125820485.98,
"Total Cost": 125986751.69,
"Plan Rows": 8680469,
"Plan Width": 16,
"Actual Startup Time": 10885.459,
"Actual Total Time": 10888.283,
"Actual Rows": 2374,
"Actual Loops": 9,
"Output": ["map_from.x_pos", "map_from.y_pos", "map_with_drain_1.x_drain", "map_with_drain_1.y_drain"],
"Inner Unique": false,
"Merge Cond": "((map_with_drain_1.x_pos = map_to.x_pos) AND (map_with_drain_1.y_pos = map_to.y_pos))",
"Shared Hit Blocks": 0,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "Sort",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Startup Cost": 6228.62,
"Total Cost": 6384.87,
"Plan Rows": 62500,
"Plan Width": 16,
"Actual Startup Time": 0.695,
"Actual Total Time": 0.797,
"Actual Rows": 2400,
"Actual Loops": 9,
"Output": ["map_with_drain_1.x_drain", "map_with_drain_1.y_drain", "map_with_drain_1.x_pos", "map_with_drain_1.y_pos"],
"Sort Key": ["map_with_drain_1.x_pos", "map_with_drain_1.y_pos"],
"Sort Method": "quicksort",
"Sort Space Used": 102,
"Sort Space Type": "Memory",
"Shared Hit Blocks": 0,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "WorkTable Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"CTE Name": "map_with_drain",
"Alias": "map_with_drain_1",
"Startup Cost": 0.00,
"Total Cost": 1250.00,
"Plan Rows": 62500,
"Plan Width": 16,
"Actual Startup Time": 0.001,
"Actual Total Time": 0.189,
"Actual Rows": 2401,
"Actual Loops": 9,
"Output": ["map_with_drain_1.x_drain", "map_with_drain_1.y_drain", "map_with_drain_1.x_pos", "map_with_drain_1.y_pos"],
"Shared Hit Blocks": 0,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0
}
]
},
{
"Node Type": "Materialize",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"Startup Cost": 125814257.37,
"Total Cost": 125842034.87,
"Plan Rows": 5555500,
"Plan Width": 16,
"Actual Startup Time": 10884.507,
"Actual Total Time": 10886.000,
"Actual Rows": 11117,
"Actual Loops": 9,
"Output": ["map_from.x_pos", "map_from.y_pos", "map_to.x_pos", "map_to.y_pos"],
"Shared Hit Blocks": 0,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "Sort",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Startup Cost": 125814257.37,
"Total Cost": 125828146.12,
"Plan Rows": 5555500,
"Plan Width": 16,
"Actual Startup Time": 10884.505,
"Actual Total Time": 10884.904,
"Actual Rows": 9971,
"Actual Loops": 9,
"Output": ["map_from.x_pos", "map_from.y_pos", "map_to.x_pos", "map_to.y_pos"],
"Sort Key": ["map_to.x_pos", "map_to.y_pos"],
"Sort Method": "quicksort",
"Sort Space Used": 852,
"Sort Space Type": "Memory",
"Shared Hit Blocks": 0,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "Nested Loop",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Join Type": "Inner",
"Startup Cost": 0.00,
"Total Cost": 125002000.00,
"Plan Rows": 5555500,
"Plan Width": 16,
"Actual Startup Time": 0.017,
"Actual Total Time": 10880.571,
"Actual Rows": 9976,
"Actual Loops": 9,
"Output": ["map_from.x_pos", "map_from.y_pos", "map_to.x_pos", "map_to.y_pos"],
"Inner Unique": false,
"Join Filter": "((map_from.height_self > map_to.height_self) AND ((abs((map_to.x_pos - map_from.x_pos)) + abs((map_to.y_pos - map_from.y_pos))) = 1))",
"Rows Removed by Join Filter": 71650024,
"Shared Hit Blocks": 0,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "CTE Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"CTE Name": "map",
"Alias": "map_from",
"Startup Cost": 0.00,
"Total Cost": 2250.00,
"Plan Rows": 33333,
"Plan Width": 12,
"Actual Startup Time": 0.001,
"Actual Total Time": 1.776,
"Actual Rows": 7166,
"Actual Loops": 9,
"Output": ["map_from.x_pos", "map_from.y_pos", "map_from.height_self", "map_from.height_up", "map_from.height_down", "map_from.height_left", "map_from.height_right"],
"Filter": "(map_from.height_self < 9)",
"Rows Removed by Filter": 2834,
"Shared Hit Blocks": 0,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0
},
{
"Node Type": "CTE Scan",
"Parent Relationship": "Inner",
"Parallel Aware": false,
"CTE Name": "map",
"Alias": "map_to",
"Startup Cost": 0.00,
"Total Cost": 2000.00,
"Plan Rows": 100000,
"Plan Width": 12,
"Actual Startup Time": 0.000,
"Actual Total Time": 1.001,
"Actual Rows": 10000,
"Actual Loops": 64494,
"Output": ["map_to.x_pos", "map_to.y_pos", "map_to.height_self", "map_to.height_up", "map_to.height_down", "map_to.height_left", "map_to.height_right"],
"Shared Hit Blocks": 0,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0
}
]
}
]
}
]
}
]
}
]
},
{
"Node Type": "WindowAgg",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Startup Cost": 18936280.82,
"Total Cost": 18936280.89,
"Plan Rows": 3,
"Plan Width": 16,
"Actual Startup Time": 98047.771,
"Actual Total Time": 98047.772,
"Actual Rows": 1,
"Actual Loops": 1,
"Output": ["((basins.size * lead(basins.size) OVER (?)) * lead(basins.size, 2) OVER (?))", "basins.size"],
"Shared Hit Blocks": 2,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "Sort",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Startup Cost": 18936280.82,
"Total Cost": 18936280.82,
"Plan Rows": 3,
"Plan Width": 8,
"Actual Startup Time": 98047.765,
"Actual Total Time": 98047.766,
"Actual Rows": 3,
"Actual Loops": 1,
"Output": ["basins.size"],
"Sort Key": ["basins.size"],
"Sort Method": "quicksort",
"Sort Space Used": 25,
"Sort Space Type": "Memory",
"Shared Hit Blocks": 2,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "Limit",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Startup Cost": 18936280.75,
"Total Cost": 18936280.76,
"Plan Rows": 3,
"Plan Width": 8,
"Actual Startup Time": 98047.763,
"Actual Total Time": 98047.764,
"Actual Rows": 3,
"Actual Loops": 1,
"Output": ["basins.size"],
"Shared Hit Blocks": 2,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "Sort",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Startup Cost": 18936280.75,
"Total Cost": 18936281.25,
"Plan Rows": 200,
"Plan Width": 8,
"Actual Startup Time": 98047.762,
"Actual Total Time": 98047.763,
"Actual Rows": 3,
"Actual Loops": 1,
"Output": ["basins.size"],
"Sort Key": ["basins.size DESC"],
"Sort Method": "top-N heapsort",
"Sort Space Used": 25,
"Sort Space Type": "Memory",
"Shared Hit Blocks": 2,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "Subquery Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Alias": "basins",
"Startup Cost": 18936272.17,
"Total Cost": 18936278.17,
"Plan Rows": 200,
"Plan Width": 8,
"Actual Startup Time": 98046.926,
"Actual Total Time": 98047.743,
"Actual Rows": 242,
"Actual Loops": 1,
"Output": ["basins.size"],
"Shared Hit Blocks": 2,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "Aggregate",
"Strategy": "Sorted",
"Partial Mode": "Simple",
"Parent Relationship": "Subquery",
"Parallel Aware": false,
"Startup Cost": 18936272.17,
"Total Cost": 18936276.17,
"Plan Rows": 200,
"Plan Width": 16,
"Actual Startup Time": 98046.926,
"Actual Total Time": 98047.728,
"Actual Rows": 242,
"Actual Loops": 1,
"Output": ["without_ambiguous_drains.x_drain", "without_ambiguous_drains.y_drain", "count(*)"],
"Group Key": ["without_ambiguous_drains.x_drain", "without_ambiguous_drains.y_drain"],
"Shared Hit Blocks": 2,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "Sort",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Startup Cost": 18936272.17,
"Total Cost": 18936272.67,
"Plan Rows": 200,
"Plan Width": 8,
"Actual Startup Time": 98046.919,
"Actual Total Time": 98047.180,
"Actual Rows": 7166,
"Actual Loops": 1,
"Output": ["without_ambiguous_drains.x_drain", "without_ambiguous_drains.y_drain"],
"Sort Key": ["without_ambiguous_drains.x_drain", "without_ambiguous_drains.y_drain"],
"Sort Method": "quicksort",
"Sort Space Used": 528,
"Sort Space Type": "Memory",
"Shared Hit Blocks": 2,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "Subquery Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Alias": "without_ambiguous_drains",
"Startup Cost": 17633598.43,
"Total Cost": 18936264.53,
"Plan Rows": 200,
"Plan Width": 8,
"Actual Startup Time": 98032.533,
"Actual Total Time": 98045.855,
"Actual Rows": 7166,
"Actual Loops": 1,
"Output": ["without_ambiguous_drains.x_drain", "without_ambiguous_drains.y_drain"],
"Shared Hit Blocks": 2,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "Aggregate",
"Strategy": "Sorted",
"Partial Mode": "Simple",
"Parent Relationship": "Subquery",
"Parallel Aware": false,
"Startup Cost": 17633598.43,
"Total Cost": 18936262.53,
"Plan Rows": 200,
"Plan Width": 16,
"Actual Startup Time": 98032.532,
"Actual Total Time": 98045.361,
"Actual Rows": 7166,
"Actual Loops": 1,
"Output": ["map_with_drain.x_pos", "map_with_drain.y_pos", "min(map_with_drain.x_drain)", "min(map_with_drain.y_drain)"],
"Group Key": ["map_with_drain.x_pos", "map_with_drain.y_pos"],
"Filter": "(count(DISTINCT ROW(map_with_drain.x_drain, map_with_drain.y_drain)) = 1)",
"Rows Removed by Filter": 0,
"Shared Hit Blocks": 2,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "Sort",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Startup Cost": 17633598.43,
"Total Cost": 17850625.78,
"Plan Rows": 86810940,
"Plan Width": 16,
"Actual Startup Time": 98032.517,
"Actual Total Time": 98033.406,
"Actual Rows": 21608,
"Actual Loops": 1,
"Output": ["map_with_drain.x_pos", "map_with_drain.y_pos", "map_with_drain.x_drain", "map_with_drain.y_drain"],
"Sort Key": ["map_with_drain.x_pos", "map_with_drain.y_pos"],
"Sort Method": "quicksort",
"Sort Space Used": 1781,
"Sort Space Type": "Memory",
"Shared Hit Blocks": 2,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0,
"Plans": [
{
"Node Type": "CTE Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"CTE Name": "map_with_drain",
"Alias": "map_with_drain",
"Startup Cost": 0.00,
"Total Cost": 1736218.80,
"Plan Rows": 86810940,
"Plan Width": 16,
"Actual Startup Time": 16.696,
"Actual Total Time": 98026.892,
"Actual Rows": 21608,
"Actual Loops": 1,
"Output": ["map_with_drain.x_pos", "map_with_drain.y_pos", "map_with_drain.x_drain", "map_with_drain.y_drain"],
"Shared Hit Blocks": 2,
"Shared Read Blocks": 0,
"Shared Dirtied Blocks": 0,
"Shared Written Blocks": 0,
"Local Hit Blocks": 0,
"Local Read Blocks": 0,
"Local Dirtied Blocks": 0,
"Local Written Blocks": 0,
"Temp Read Blocks": 0,
"Temp Written Blocks": 0
}
]
}
]
}
]
}
]
}
]
}
]
}
]
}
]
}
]
}
]
}
]
},
"Planning Time": 0.244,
"Triggers": [
],
"Execution Time": 98048.250
}
]WITH RECURSIVE map AS (
SELECT
x_pos::int,
y_pos::int,
height AS height_self,
lag(height) OVER (PARTITION BY x_pos ORDER BY y_pos) AS height_up,
lead(height) OVER (PARTITION BY x_pos ORDER BY y_pos) AS height_down,
lag(height) OVER (PARTITION BY y_pos ORDER BY x_pos) AS height_left,
lead(height) OVER (PARTITION BY y_pos ORDER BY x_pos) AS height_right
FROM aoc."2021_day_09" AS src(y_pos, data)
CROSS JOIN regexp_split_to_table(src.data, '') WITH ORDINALITY AS _(height_text, x_pos)
CROSS JOIN LATERAL (SELECT height_text::int) AS __(height)
), flow AS (
SELECT
map_from.x_pos AS from_x_pos,
map_from.y_pos AS from_y_pos,
map_to.x_pos AS to_x_pos,
map_to.y_pos AS to_y_pos
FROM map AS map_from
INNER JOIN map AS map_to
ON (abs(map_to.x_pos - map_from.x_pos) + abs(map_to.y_pos - map_from.y_pos)) = 1
AND map_from.height_self > map_to.height_self
WHERE map_from.height_self < 9
), map_with_drain AS (
SELECT
x_pos,
y_pos,
x_pos AS x_drain,
y_pos AS y_drain
FROM map
WHERE height_self < height_up IS DISTINCT FROM FALSE
AND height_self < height_down IS DISTINCT FROM FALSE
AND height_self < height_left IS DISTINCT FROM FALSE
AND height_self < height_right IS DISTINCT FROM FALSE
UNION ALL
SELECT
flow.from_x_pos,
flow.from_y_pos,
map_with_drain.x_drain,
map_with_drain.y_drain
FROM map_with_drain
INNER JOIN flow
ON flow.to_x_pos = map_with_drain.x_pos
AND flow.to_y_pos = map_with_drain.y_pos
), basins AS (
SELECT x_drain, y_drain, count(*) AS size
FROM (
SELECT
x_pos,
y_pos,
min(x_drain) AS x_drain,
min(y_drain) AS y_drain
FROM map_with_drain
GROUP BY x_pos, y_pos
HAVING count(DISTINCT (x_drain, y_drain)) = 1
) AS without_ambiguous_drains
GROUP BY x_drain, y_drain
)
SELECT
-- I did consider using a custom aggregate, but I stuck to vanilla postgres ... but this is quite ugly
size * lead(size) OVER (ORDER BY size) * lead(size, 2) OVER (ORDER BY size)
FROM (
SELECT size
FROM basins
ORDER BY size DESC
LIMIT 3
) AS _
LIMIT 1;
| Node Type | Count | Time | |
|---|---|---|---|
| CTE Scan | 4 | 2m 42s | 166% |
| #29 CTE Scan | 1m 38s | 100% | |
| #18 CTE Scan | 1m 4s | 66% | |
| #10 CTE Scan | 25.4ms | 0% | |
| #17 CTE Scan | 16ms | 0% | |
| Nested Loop | 2 | 33s 351ms | 34% |
| #16 Nested Loop | 33s 351ms | 34% | |
| #6 Nested Loop | 0.627ms | 0% | |
| Sort | 8 | 59.5ms | 0% |
| #15 Sort | 39ms | 0% | |
| #28 Sort | 6.51ms | 0% | |
| #12 Sort | 5.47ms | 0% | |
| #3 Sort | 3.72ms | 0% | |
| #5 Sort | 3.48ms | 0% | |
| #25 Sort | 1.32ms | 0% | |
| #22 Sort | 0.02ms | 0% | |
| #20 Sort | 0.002ms | 0% | |
| Merge Join | 1 | 13.4ms | 0% |
| #11 Merge Join | 13.4ms | 0% | |
| GroupAggregate | 2 | 12.5ms | 0% |
| #27 GroupAggregate | 12ms | 0% | |
| #24 GroupAggregate | 0.548ms | 0% | |
| WindowAgg | 3 | 11ms | 0% |
| #2 WindowAgg | 5.83ms | 0% | |
| #4 WindowAgg | 5.21ms | 0% | |
| #19 WindowAgg | 0.006ms | 0% | |
| Materialize | 1 | 9.86ms | 0% |
| #14 Materialize | 9.86ms | 0% | |
| Function Scan | 1 | 4ms | 0% |
| #8 Function Scan | 4ms | 0% | |
| Recursive Union | 1 | 2.67ms | 0% |
| #9 Recursive Union | 2.67ms | 0% | |
| WorkTable Scan | 1 | 1.7ms | 0% |
| #13 WorkTable Scan | 1.7ms | 0% | |
| Subquery Scan | 2 | 0.509ms | 0% |
| #26 Subquery Scan | 0.494ms | 0% | |
| #23 Subquery Scan | 0.015ms | 0% | |
| Seq Scan | 1 | 0.015ms | 0% |
| #7 Seq Scan | 0.015ms | 0% | |
| Limit | 2 | 0.006ms | 0% |
| #1 Limit | 0.005ms | 0% | |
| #21 Limit | 0.001ms | 0% | |