explain.depesz.com

PostgreSQL's explain analyze made readable

Result: vsR3

Settings
# 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)

  • Group Key: a.operation_assetid, a.operational_area, a.catchment_assetid, a.catchment_name, a.value, a.regional_average, ga.catchment_assetid, ga.avg_ari_24hr, ga.avg_aridepth_24hr
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)

  • Sort Key: gmp1.assetid
  • Sort Method: quicksort Memory: 1353kB
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)

  • Join Filter: (gmp1.assetid = gs2.assetid)
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)

  • Join Filter: (gmp1.assetid = gs1.assetid)
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)

  • Index Cond: ((workid = 594) AND (assettypeid = 3541))
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)

  • Index Cond: (assetid = gmp1.assetid)
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)

  • Index Cond: (assetid = a_1.assetid)
  • Filter: (datavariabletypeid = 10)
  • Rows Removed by Filter: 2
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)

  • Index Cond: (assetid = a_1.assetid)
  • Filter: (datavariabletypeid = 110573)
  • Rows Removed by Filter: 2
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)

  • Join Filter: ("left"((rg.catchment_description)::text, ("position"((rg.catchment_description)::text, ' -'::text) - 1)) = (gmp2.name)::text)
  • Rows Removed by Join Filter: 754
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)

  • Index Cond: ((workid = 936) AND (assettypeid = 3877))
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)

  • Filter: (NOT (hashed SubPlan 3))
  • Rows Removed by Filter: 178
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)

  • Recheck Cond: ((workid = 936) AND (assettypeid = 3877) AND (rg_1.catch_geom && shapenztm))
  • Filter: _st_intersects(rg_1.catch_geom, shapenztm)
  • Rows Removed by Filter: 1
  • Heap Blocks: exact=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)

  • Index Cond: ((workid = 936) AND (assettypeid = 3877))
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)

  • Index Cond: (rg_1.catch_geom && shapenztm)
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)

  • Join Filter: (rg_2.catchment_assetid = g1_1.catchment_assetid)
  • Rows Removed by Join Filter: 57072
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)

  • Sort Key: g1_1.operation_assetid, g1_1.operational_area, g1_1.catchment_assetid
  • Sort Method: quicksort Memory: 36kB
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)

  • Sort Key: rg_3.operation_assetid, rg_3.catchment_assetid, rg_3.gs_rainfall, ggd.unixdatetime DESC
  • Sort Method: quicksort Memory: 86kB
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)

  • Filter: (rg_3.gs_rainfall = gaugesummaryid)
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)

  • Group Key: rg_4.operation_assetid, rg_4.operational_area
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)

  • Join Filter: (rg_4.operation_assetid = ag.operation_assetid)
  • Rows Removed by Join Filter: 36312
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)

  • Join Filter: (rg_5.operation_assetid = ra.operation_assetid)
  • Rows Removed by Join Filter: 738
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)

  • Group Key: rg_6.catchment_assetid
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)

  • Merge Cond: ((((td.value1)::bigint) = rg_6.catchment_assetid) AND (((td.value2)::bigint) = gla.itemid))
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)

  • Sort Key: ((td.value1)::bigint), ((td.value2)::bigint)
  • Sort Method: external merge Disk: 110232kB
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)

  • Sort Key: rg_6.catchment_assetid, gla.itemid
  • Sort Method: quicksort Memory: 2658kB
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)

  • Merge Cond: (gla.itemid = glad.itemid)
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)

  • Sort Key: gla.itemid
  • Sort Method: quicksort Memory: 25kB
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)

  • Sort Key: glad.itemid
  • Sort Method: quicksort Memory: 25kB
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)

  • Join Filter: (a.catchment_assetid = ga.catchment_assetid)
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