# | exclusive | inclusive | rows x | rows | loops | node |
---|---|---|---|---|---|---|
1. | 0.000 | 174,794.118 | ↓ 0.0 | 0 | 1 |
→
HashAggregate (cost=402,411.35..402,411.36 rows=1 width=592) (actual time=174,794.118..174,794.118 rows=0 loops=1)
|
2. |
CTE get_rg |
|||||
3. | 0.194 | 318.471 | ↓ 233.0 | 233 | 1 |
→
Unique (cost=45.99..45.99 rows=1 width=681) (actual time=317.979..318.471 rows=233 loops=1) |
4. | 1.318 | 318.277 | ↓ 233.0 | 233 | 1 |
→
Sort (cost=45.99..45.99 rows=1 width=681) (actual time=317.970..318.277 rows=233 loops=1)
|
5. | 0.052 | 316.959 | ↓ 233.0 | 233 | 1 |
→
Nested Loop (cost=1.69..45.98 rows=1 width=681) (actual time=4.838..316.959 rows=233 loops=1)
|
6. | 0.151 | 279.860 | ↓ 233.0 | 233 | 1 |
→
Nested Loop (cost=1.27..45.44 rows=1 width=685) (actual time=4.827..279.860 rows=233 loops=1)
|
7. | 0.191 | 140.375 | ↓ 21.2 | 233 | 1 |
→
Nested Loop (cost=0.85..39.54 rows=11 width=677) (actual time=3.771..140.375 rows=233 loops=1) |
8. | 89.390 | 89.390 | ↓ 21.2 | 233 | 1 |
→
Index Scan using geometrymultipolygondata_idx1 on geometrymultipolygondata gmp1 (cost=0.42..10.28 rows=11 width=578) (actual time=3.426..89.390 rows=233 loops=1)
|
9. | 50.794 | 50.794 | ↑ 1.0 | 1 | 233 |
→
Index Scan using asset_pk on asset a_1 (cost=0.43..2.65 rows=1 width=99) (actual time=0.218..0.218 rows=1 loops=233)
|
10. | 139.334 | 139.334 | ↑ 1.0 | 1 | 233 |
→
Index Scan using gaugesummary_idx on gaugesummary gs1 (cost=0.42..0.52 rows=1 width=8) (actual time=0.597..0.598 rows=1 loops=233)
|
11. | 37.047 | 37.047 | ↑ 1.0 | 1 | 233 |
→
Index Scan using gaugesummary_idx on gaugesummary gs2 (cost=0.42..0.52 rows=1 width=8) (actual time=0.002..0.159 rows=1 loops=233)
|
12. |
CTE g1 |
|||||
13. | 81.938 | 85.305 | ↓ 178.0 | 178 | 1 |
→
Nested Loop (cost=0.42..5.67 rows=1 width=880) (actual time=5.019..85.305 rows=178 loops=1)
|
14. | 1.037 | 1.037 | ↓ 233.0 | 233 | 1 |
→
CTE Scan on get_rg rg (cost=0.00..0.02 rows=1 width=866) (actual time=0.001..1.037 rows=233 loops=1) |
15. | 2.330 | 2.330 | ↑ 1.2 | 4 | 233 |
→
Index Scan using geometrymultipolygondata_idx1 on geometrymultipolygondata gmp2 (cost=0.42..5.55 rows=5 width=14) (actual time=0.009..0.010 rows=4 loops=233)
|
16. |
CTE g2 |
|||||
17. | 0.359 | 1,194.975 | ↓ 68.0 | 68 | 1 |
→
Nested Loop (cost=3.57..4.97 rows=1 width=880) (actual time=116.730..1,194.975 rows=68 loops=1) |
18. | 0.543 | 0.676 | ↓ 55.0 | 55 | 1 |
→
CTE Scan on get_rg rg_1 (cost=0.02..0.04 rows=1 width=866) (actual time=0.182..0.676 rows=55 loops=1)
|
19. |
SubPlan (for CTE Scan) |
|||||
20. | 0.133 | 0.133 | ↓ 178.0 | 178 | 1 |
→
CTE Scan on g1 (cost=0.00..0.02 rows=1 width=4) (actual time=0.002..0.133 rows=178 loops=1) |
21. | 899.580 | 1,193.940 | ↑ 1.0 | 1 | 55 |
→
Bitmap Heap Scan on geometrymultipolygondata gmp2_1 (cost=3.55..4.91 rows=1 width=564) (actual time=17.073..21.708 rows=1 loops=55)
|
22. | 3.575 | 294.360 | ↓ 0.0 | 0 | 55 |
→
BitmapAnd (cost=3.55..3.55 rows=1 width=0) (actual time=5.352..5.352 rows=0 loops=55) |
23. | 0.495 | 0.495 | ↑ 1.2 | 4 | 55 |
→
Bitmap Index Scan on geometrymultipolygondata_idx1 (cost=0.00..1.57 rows=5 width=0) (actual time=0.009..0.009 rows=4 loops=55)
|
24. | 290.290 | 290.290 | ↓ 30.0 | 1,348 | 55 |
→
Bitmap Index Scan on geometrymultipolygondata_shapenztm_idx (cost=0.00..1.72 rows=45 width=0) (actual time=5.278..5.278 rows=1,348 loops=55)
|
25. |
CTE catchments |
|||||
26. | 5.737 | 1,618.574 | ↓ 246.0 | 246 | 1 |
→
Nested Loop (cost=0.07..0.16 rows=1 width=1,144) (actual time=1,599.976..1,618.574 rows=246 loops=1)
|
27. | 318.522 | 318.522 | ↓ 233.0 | 233 | 1 |
→
CTE Scan on get_rg rg_2 (cost=0.00..0.02 rows=1 width=866) (actual time=317.988..318.522 rows=233 loops=1) |
28. | 7.456 | 1,294.315 | ↓ 123.0 | 246 | 233 |
→
Unique (cost=0.07..0.09 rows=2 width=282) (actual time=5.502..5.555 rows=246 loops=233) |
29. | 5.175 | 1,286.859 | ↓ 123.0 | 246 | 233 |
→
Sort (cost=0.07..0.08 rows=2 width=282) (actual time=5.502..5.523 rows=246 loops=233)
|
30. | 0.061 | 1,281.684 | ↓ 123.0 | 246 | 1 |
→
Append (cost=0.00..0.06 rows=2 width=282) (actual time=5.028..1,281.684 rows=246 loops=1) |
31. | 86.168 | 86.168 | ↓ 178.0 | 178 | 1 |
→
CTE Scan on g1 g1_1 (cost=0.00..0.02 rows=1 width=282) (actual time=5.028..86.168 rows=178 loops=1) |
32. | 1,195.455 | 1,195.455 | ↓ 68.0 | 68 | 1 |
→
CTE Scan on g2 (cost=0.00..0.02 rows=1 width=282) (actual time=116.739..1,195.455 rows=68 loops=1) |
33. |
CTE agg_rg |
|||||
34. | 0.062 | 78,509.101 | ↓ 246.0 | 246 | 1 |
→
Unique (cost=12.88..12.92 rows=1 width=576) (actual time=78,509.010..78,509.101 rows=246 loops=1) |
35. | 0.587 | 78,509.039 | ↓ 98.4 | 492 | 1 |
→
Sort (cost=12.88..12.89 rows=5 width=576) (actual time=78,509.009..78,509.039 rows=492 loops=1)
|
36. | 0.813 | 78,508.452 | ↓ 98.4 | 492 | 1 |
→
Nested Loop Left Join (cost=0.25..12.82 rows=5 width=576) (actual time=2,015.658..78,508.452 rows=492 loops=1) |
37. | 1,619.601 | 1,619.601 | ↓ 246.0 | 246 | 1 |
→
CTE Scan on catchments rg_3 (cost=0.00..0.02 rows=1 width=560) (actual time=1,599.979..1,619.601 rows=246 loops=1) |
38. | 76,888.038 | 76,888.038 | ↑ 2.5 | 2 | 246 |
→
Function Scan on gaugedata_ari_getmaxdepth_interval_24hr ggd (cost=0.25..12.75 rows=5 width=16) (actual time=312.552..312.553 rows=2 loops=246)
|
39. |
CTE regional_avg |
|||||
40. | 5.210 | 19.121 | ↓ 4.0 | 4 | 1 |
→
HashAggregate (cost=0.06..0.07 rows=1 width=286) (actual time=19.119..19.121 rows=4 loops=1)
|
41. | 5.581 | 13.911 | ↓ 24,204.0 | 24,204 | 1 |
→
Nested Loop (cost=0.00..0.05 rows=1 width=286) (actual time=0.018..13.911 rows=24,204 loops=1)
|
42. | 0.212 | 0.212 | ↓ 246.0 | 246 | 1 |
→
CTE Scan on catchments rg_4 (cost=0.00..0.02 rows=1 width=278) (actual time=0.002..0.212 rows=246 loops=1) |
43. | 8.118 | 8.118 | ↓ 246.0 | 246 | 246 |
→
CTE Scan on agg_rg ag (cost=0.00..0.02 rows=1 width=12) (actual time=0.000..0.033 rows=246 loops=246) |
44. |
CTE alltogether |
|||||
45. | 0.148 | 78,528.390 | ↓ 246.0 | 246 | 1 |
→
Nested Loop (cost=0.00..0.05 rows=1 width=580) (actual time=78,528.138..78,528.390 rows=246 loops=1)
|
46. | 78,509.054 | 78,509.054 | ↓ 246.0 | 246 | 1 |
→
CTE Scan on agg_rg rg_5 (cost=0.00..0.02 rows=1 width=572) (actual time=78,509.011..78,509.054 rows=246 loops=1) |
47. | 19.188 | 19.188 | ↓ 4.0 | 4 | 246 |
→
CTE Scan on regional_avg ra (cost=0.00..0.02 rows=1 width=12) (actual time=0.078..0.078 rows=4 loops=246) |
48. |
CTE getari |
|||||
49. | 0.003 | 96,265.612 | ↓ 0.0 | 0 | 1 |
→
GroupAggregate (cost=358,653.54..402,341.44 rows=1 width=20) (actual time=96,265.612..96,265.612 rows=0 loops=1)
|
50. | 233.922 | 96,265.609 | ↓ 0.0 | 0 | 1 |
→
Merge Join (cost=358,653.54..399,821.05 rows=336,050 width=20) (actual time=96,265.609..96,265.609 rows=0 loops=1)
|
51. | 1,914.522 | 2,644.799 | ↑ 1.0 | 2,688,486 | 1 |
→
Sort (cost=358,096.17..364,817.40 rows=2,688,491 width=16) (actual time=2,049.437..2,644.799 rows=2,688,486 loops=1)
|
52. | 730.277 | 730.277 | ↑ 1.0 | 2,688,491 | 1 |
→
Seq Scan on tabulardata td (cost=0.00..44,074.91 rows=2,688,491 width=16) (actual time=0.513..730.277 rows=2,688,491 loops=1) |
53. | 4.989 | 93,386.888 | ↓ 4.8 | 24,180 | 1 |
→
Sort (cost=557.37..569.87 rows=5,000 width=28) (actual time=93,385.411..93,386.888 rows=24,180 loops=1)
|
54. | 2.931 | 93,381.899 | ↓ 4.8 | 24,180 | 1 |
→
Nested Loop (cost=120.16..250.18 rows=5,000 width=28) (actual time=674.386..93,381.899 rows=24,180 loops=1) |
55. | 0.490 | 0.490 | ↓ 233.0 | 233 | 1 |
→
CTE Scan on get_rg rg_6 (cost=0.00..0.02 rows=1 width=36) (actual time=0.005..0.490 rows=233 loops=1) |
56. | 8.118 | 93,378.478 | ↑ 48.1 | 104 | 233 |
→
Merge Join (cost=120.16..200.16 rows=5,000 width=24) (actual time=400.722..400.766 rows=104 loops=233)
|
57. | 6.757 | 49,377.360 | ↑ 9.6 | 104 | 233 |
→
Sort (cost=60.08..62.58 rows=1,000 width=12) (actual time=211.911..211.920 rows=104 loops=233)
|
58. | 49,370.603 | 49,370.603 | ↑ 9.6 | 104 | 233 |
→
Function Scan on getlayer_arigrid gla (cost=0.25..10.25 rows=1,000 width=12) (actual time=211.877..211.891 rows=104 loops=233) |
59. | 6.496 | 43,993.000 | ↑ 9.6 | 104 | 232 |
→
Sort (cost=60.08..62.58 rows=1,000 width=12) (actual time=189.618..189.625 rows=104 loops=232)
|
60. | 43,986.504 | 43,986.504 | ↑ 9.6 | 104 | 232 |
→
Function Scan on getlayer_aridepthgrid glad (cost=0.25..10.25 rows=1,000 width=12) (actual time=189.584..189.597 rows=104 loops=232) |
61. | 0.000 | 174,794.118 | ↓ 0.0 | 0 | 1 |
→
Nested Loop (cost=0.00..0.05 rows=1 width=592) (actual time=174,794.118..174,794.118 rows=0 loops=1)
|
62. | 78,528.469 | 78,528.469 | ↓ 246.0 | 246 | 1 |
→
CTE Scan on alltogether a (cost=0.00..0.02 rows=1 width=572) (actual time=78,528.139..78,528.469 rows=246 loops=1) |
63. | 96,265.704 | 96,265.704 | ↓ 0.0 | 0 | 246 |
→
CTE Scan on getari ga (cost=0.00..0.02 rows=1 width=20) (actual time=391.324..391.324 rows=0 loops=246) |
Planning time | : | 13.520 ms |
---|---|---|
Execution time | : | 174,808.708 ms |