1.15.0
Execution time:
339ms
Planning time:
0.794ms
Triggers: N/A
| Main Query Plan | |||
|---|---|---|---|
| #1 | Aggregate | ||
| #3 | └ Nested Loop | ||
| #4 | ├ Nested Loop Join | ||
| #5 | │├ Seq Scan | ||
| #6 | │└ Nested Loop | ||
| #10 | │ ├ Hash Join | ||
| #11 | │ │└ Hash | ||
| #12 | │ │ └ CTE Scan | ||
| #13 | │ └ Limit | ||
| #14 | │ └ Sort | ||
| #15 | │ └ CTE Scan | ||
| #16 | └ Limit | ||
| #43 | └ Group | ||
| #44 | └ Sort | ||
| #45 | └ CTE Scan | ||
| CTE matching_pairs | |||
| #2 | Values Scan on "*VALUES*" | ||
| CTE levels | |||
| #7 | WindowAgg | ||
| #8 | └ WindowAgg | ||
| #9 | └ Function Scan | ||
| CTE levels | |||
| #17 | WindowAgg | ||
| #18 | └ WindowAgg | ||
| #19 | └ Function Scan | ||
| CTE scores | |||
| #20 | Recursive Union | ||
| #21 | ├ Result | ||
| #22 | └ Hash Join | ||
| #23 | ├ Hash Join | ||
| #24 | │├ WindowAgg | ||
| #25 | ││└ Sort | ||
| #26 | ││ └ Nested Loop | ||
| #27 | ││ ├ Limit | ||
| #28 | ││ │└ Sort | ||
| #29 | ││ │ └ CTE Scan | ||
| #30 | ││ └ Nested Loop | ||
| #31 | ││ ├ Function Scan | ||
| #32 | ││ └ Hash Join | ||
| #33 | ││ ├ CTE Scan | ||
| #34 | ││ └ Hash | ||
| #35 | ││ └ Subquery Scan | ||
| #36 | ││ └ Limit | ||
| #37 | ││ └ Sort | ||
| #38 | ││ └ CTE Scan | ||
| #39 | │└ Hash | ||
| #40 | │ └ Values Scan on "*VALUES*_1" | ||
| #41 | └ Hash | ||
| #42 | └ WorkTable Scan on scores scores_1 | ||
Aggregate (cost=133982.89..133982.90 rows=1 width=8) (actual time=337.304..337.314 rows=1 loops=1)
CTE matching_pairs
-> Values Scan on "*VALUES*" (cost=0.00..0.05 rows=4 width=64) (actual time=0.001..0.003 rows=4 loops=1)
-> Nested Loop (cost=2849.40..133982.73 rows=45 width=8) (actual time=11.995..337.227 rows=45 loops=1)
-> Nested Loop Left Join (cost=77.64..9252.42 rows=45 width=101) (actual time=4.980..153.801 rows=45 loops=1)
Filter: (NOT COALESCE((true), false))
Rows Removed by Filter: 45
-> Seq Scan on day10 (cost=0.00..2.90 rows=90 width=101) (actual time=0.013..0.094 rows=90 loops=1)
-> Nested Loop (cost=77.64..102.75 rows=1 width=1) (actual time=1.564..1.706 rows=0 loops=90)
Join Filter: (opening."char" <> matching.open)
Rows Removed by Join Filter: 43
CTE levels
-> WindowAgg (cost=0.00..52.50 rows=1000 width=48) (actual time=0.029..1.125 rows=100 loops=90)
-> WindowAgg (cost=0.00..30.00 rows=1000 width=48) (actual time=0.026..0.108 rows=100 loops=90)
-> Function Scan on regexp_split_to_table splitted_1 (cost=0.00..10.00 rows=1000 width=40) (actual time=0.022..0.035 rows=100 loops=90)
-> Hash Join (cost=0.13..25.21 rows=1 width=48) (actual time=0.081..0.112 rows=43 loops=90)
Hash Cond: (closing."char" = matching.close)
-> CTE Scan on levels closing (cost=0.00..25.00 rows=20 width=48) (actual time=0.079..0.096 rows=43 loops=90)
Filter: ("char" = ANY ('{),],>,"}"}'::text[]))
Rows Removed by Filter: 57
-> Hash (cost=0.08..0.08 rows=4 width=64) (actual time=0.009..0.010 rows=4 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> CTE Scan on matching_pairs matching (cost=0.00..0.08 rows=4 width=64) (actual time=0.003..0.006 rows=4 loops=1)
-> Limit (cost=25.01..25.01 rows=1 width=40) (actual time=0.036..0.036 rows=1 loops=3903)
-> Sort (cost=25.01..25.02 rows=2 width=40) (actual time=0.036..0.036 rows=1 loops=3903)
Sort Key: opening.idx DESC
Sort Method: top-N heapsort Memory: 25kB
-> CTE Scan on levels opening (cost=0.00..25.00 rows=2 width=40) (actual time=0.001..0.034 rows=14 loops=3903)
Filter: ((idx < closing.idx) AND (level = closing.level))
Rows Removed by Filter: 86
-> Limit (cost=2771.76..2771.76 rows=1 width=12) (actual time=4.073..4.074 rows=1 loops=45)
CTE levels
-> WindowAgg (cost=0.00..52.50 rows=1000 width=48) (actual time=0.030..1.093 rows=100 loops=45)
-> WindowAgg (cost=0.00..30.00 rows=1000 width=48) (actual time=0.027..0.105 rows=100 loops=45)
-> Function Scan on regexp_split_to_table splitted (cost=0.00..10.00 rows=1000 width=40) (actual time=0.023..0.035 rows=100 loops=45)
CTE scores
-> Recursive Union (cost=0.00..2718.84 rows=11 width=12) (actual time=0.001..4.059 rows=14 loops=45)
-> Result (cost=0.00..0.01 rows=1 width=12) (actual time=0.000..0.000 rows=1 loops=45)
-> Hash Join (cost=240.32..271.86 rows=1 width=12) (actual time=0.283..0.287 rows=1 loops=633)
Hash Cond: ((row_number() OVER (?)) = scores_1.pt)
-> Hash Join (cost=239.99..271.44 rows=20 width=12) (actual time=0.276..0.283 rows=13 loops=633)
Hash Cond: (m.close = "*VALUES*_1".column1)
-> WindowAgg (cost=239.89..257.39 rows=1000 width=48) (actual time=0.276..0.280 rows=13 loops=633)
-> Sort (cost=239.89..242.39 rows=1000 width=40) (actual time=0.274..0.275 rows=13 loops=633)
Sort Key: opening_level.opening_level DESC
Sort Method: quicksort Memory: 25kB
-> Nested Loop (cost=55.05..190.07 rows=1000 width=40) (actual time=0.131..0.272 rows=13 loops=633)
-> Limit (cost=32.50..32.50 rows=1 width=16) (actual time=0.112..0.112 rows=1 loops=633)
-> Sort (cost=32.50..35.00 rows=1000 width=16) (actual time=0.112..0.112 rows=1 loops=633)
Sort Key: levels.idx DESC
Sort Method: top-N heapsort Memory: 25kB
-> CTE Scan on levels (cost=0.00..27.50 rows=1000 width=16) (actual time=0.003..0.101 rows=100 loops=633)
-> Nested Loop (cost=22.55..147.55 rows=1000 width=40) (actual time=0.018..0.158 rows=13 loops=633)
-> Function Scan on generate_series opening_level (cost=0.00..10.00 rows=1000 width=8) (actual time=0.002..0.003 rows=13 loops=633)
-> Hash Join (cost=22.55..22.66 rows=1 width=32) (actual time=0.011..0.011 rows=1 loops=8422)
Hash Cond: (m.open = dangling."char")
-> CTE Scan on matching_pairs m (cost=0.00..0.08 rows=4 width=64) (actual time=0.000..0.000 rows=4 loops=8422)
-> Hash (cost=22.54..22.54 rows=1 width=32) (actual time=0.009..0.009 rows=1 loops=8422)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Subquery Scan on dangling (cost=22.52..22.54 rows=1 width=32) (actual time=0.009..0.009 rows=1 loops=8422)
-> Limit (cost=22.52..22.53 rows=1 width=40) (actual time=0.009..0.009 rows=1 loops=8422)
-> Sort (cost=22.52..22.54 rows=5 width=40) (actual time=0.009..0.009 rows=1 loops=8422)
Sort Key: opening_1.idx DESC
Sort Method: quicksort Memory: 25kB
-> CTE Scan on levels opening_1 (cost=0.00..22.50 rows=5 width=40) (actual time=0.001..0.008 rows=4 loops=8422)
Filter: (level = opening_level.opening_level)
Rows Removed by Filter: 96
-> Hash (cost=0.05..0.05 rows=4 width=36) (actual time=0.006..0.007 rows=4 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Values Scan on "*VALUES*_1" (cost=0.00..0.05 rows=4 width=36) (actual time=0.003..0.005 rows=4 loops=1)
-> Hash (cost=0.20..0.20 rows=10 width=12) (actual time=0.001..0.001 rows=1 loops=633)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> WorkTable Scan on scores scores_1 (cost=0.00..0.20 rows=10 width=12) (actual time=0.000..0.000 rows=1 loops=633)
-> Group (cost=0.41..0.49 rows=11 width=12) (actual time=4.073..4.073 rows=1 loops=45)
Group Key: scores.pt, scores.acc
-> Sort (cost=0.41..0.44 rows=11 width=12) (actual time=4.071..4.071 rows=1 loops=45)
Sort Key: scores.pt DESC, scores.acc
Sort Method: quicksort Memory: 25kB
-> CTE Scan on scores (cost=0.00..0.22 rows=11 width=12) (actual time=0.002..4.065 rows=14 loops=45)
Planning Time: 0.794 ms
Execution Time: 339.469 ms| Node Type | Count | Time | |
|---|---|---|---|
| CTE Scan | 6 | 447ms | 132% |
| #45 CTE Scan | 183ms | 54% | |
| #15 CTE Scan | 133ms | 39% | |
| #38 CTE Scan | 67.4ms | 20% | |
| #29 CTE Scan | 63.9ms | 19% | |
| #12 CTE Scan | 0.006ms | 0% | |
| #33 CTE Scan | 0ms | 0% | |
| WindowAgg | 5 | 149ms | 44% |
| #7 WindowAgg | 91.5ms | 27% | |
| #17 WindowAgg | 44.5ms | 13% | |
| #8 WindowAgg | 6.57ms | 2% | |
| #24 WindowAgg | 3.16ms | 1% | |
| #18 WindowAgg | 3.15ms | 1% | |
| Hash Join | 4 | 30.7ms | 9% |
| #32 Hash Join | 16.8ms | 5% | |
| #10 Hash Join | 10.1ms | 3% | |
| #22 Hash Join | 1.9ms | 1% | |
| #23 Hash Join | 1.89ms | 1% | |
| Sort | 5 | 25.4ms | 7% |
| #37 Sort | 8.42ms | 2% | |
| #14 Sort | 7.81ms | 2% | |
| #28 Sort | 6.96ms | 2% | |
| #25 Sort | 1.9ms | 1% | |
| #44 Sort | 0.27ms | 0% | |
| Nested Loop | 4 | 9.79ms | 3% |
| #30 Nested Loop | 5.47ms | 2% | |
| #6 Nested Loop | 2.95ms | 1% | |
| #26 Nested Loop | 1.27ms | 0% | |
| #3 Nested Loop | 0.096ms | 0% | |
| Function Scan | 3 | 6.62ms | 2% |
| #9 Function Scan | 3.15ms | 1% | |
| #31 Function Scan | 1.9ms | 1% | |
| #19 Function Scan | 1.58ms | 0% | |
| Recursive Union | 1 | 0.984ms | 0% |
| #20 Recursive Union | 0.984ms | 0% | |
| Hash | 4 | 0.639ms | 0% |
| #41 Hash | 0.633ms | 0% | |
| #11 Hash | 0.004ms | 0% | |
| #39 Hash | 0.002ms | 0% | |
| #34 Hash | 0ms | 0% | |
| Nested Loop Join | 1 | 0.167ms | 0% |
| #4 Nested Loop Join | 0.167ms | 0% | |
| Seq Scan | 1 | 0.094ms | 0% |
| #5 Seq Scan | 0.094ms | 0% | |
| Group | 1 | 0.09ms | 0% |
| #43 Group | 0.09ms | 0% | |
| Aggregate | 1 | 0.087ms | 0% |
| #1 Aggregate | 0.087ms | 0% | |
| Limit | 4 | 0.045ms | 0% |
| #16 Limit | 0.045ms | 0% | |
| #36 Limit | 0ms | 0% | |
| #27 Limit | 0ms | 0% | |
| #13 Limit | 0ms | 0% | |
| Values Scan on "*VALUES*_1" | 1 | 0.005ms | 0% |
| #40 Values Scan on "*VALUES*_1" | 0.005ms | 0% | |
| Values Scan on "*VALUES*" | 1 | 0.003ms | 0% |
| #2 Values Scan on "*VALUES*" | 0.003ms | 0% | |
| Result | 1 | 0ms | 0% |
| #21 Result | 0ms | 0% | |
| Subquery Scan | 1 | 0ms | 0% |
| #35 Subquery Scan | 0ms | 0% | |
| WorkTable Scan on scores scores_1 | 1 | 0ms | 0% |
| #42 WorkTable Scan on scores scores_1 | 0ms | 0% | |