#	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

HashAggregate  (cost=402411.35..402411.36 rows=1 width=592) (actual time=174794.118..174794.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
  CTE get_rg
    ->  Unique  (cost=45.99..45.99 rows=1 width=681) (actual time=317.979..318.471 rows=233 loops=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
                ->  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)
                      ->  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)
                            ->  Nested Loop  (cost=0.85..39.54 rows=11 width=677) (actual time=3.771..140.375 rows=233 loops=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))
                                  ->  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)
                            ->  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
                      ->  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
  CTE g1
    ->  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
          ->  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)
          ->  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))
  CTE g2
    ->  Nested Loop  (cost=3.57..4.97 rows=1 width=880) (actual time=116.730..1194.975 rows=68 loops=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
                SubPlan 3
                  ->  CTE Scan on g1  (cost=0.00..0.02 rows=1 width=4) (actual time=0.002..0.133 rows=178 loops=1)
          ->  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
                ->  BitmapAnd  (cost=3.55..3.55 rows=1 width=0) (actual time=5.352..5.352 rows=0 loops=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))
                      ->  Bitmap Index Scan on geometrymultipolygondata_shapenztm_idx  (cost=0.00..1.72 rows=45 width=0) (actual time=5.278..5.278 rows=1348 loops=55)
                            Index Cond: (rg_1.catch_geom && shapenztm)
  CTE catchments
    ->  Nested Loop  (cost=0.07..0.16 rows=1 width=1144) (actual time=1599.976..1618.574 rows=246 loops=1)
          Join Filter: (rg_2.catchment_assetid = g1_1.catchment_assetid)
          Rows Removed by Join Filter: 57072
          ->  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)
          ->  Unique  (cost=0.07..0.09 rows=2 width=282) (actual time=5.502..5.555 rows=246 loops=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
                      ->  Append  (cost=0.00..0.06 rows=2 width=282) (actual time=5.028..1281.684 rows=246 loops=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)
                            ->  CTE Scan on g2  (cost=0.00..0.02 rows=1 width=282) (actual time=116.739..1195.455 rows=68 loops=1)
  CTE agg_rg
    ->  Unique  (cost=12.88..12.92 rows=1 width=576) (actual time=78509.010..78509.101 rows=246 loops=1)
          ->  Sort  (cost=12.88..12.89 rows=5 width=576) (actual time=78509.009..78509.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
                ->  Nested Loop Left Join  (cost=0.25..12.82 rows=5 width=576) (actual time=2015.658..78508.452 rows=492 loops=1)
                      ->  CTE Scan on catchments rg_3  (cost=0.00..0.02 rows=1 width=560) (actual time=1599.979..1619.601 rows=246 loops=1)
                      ->  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)
  CTE regional_avg
    ->  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
          ->  Nested Loop  (cost=0.00..0.05 rows=1 width=286) (actual time=0.018..13.911 rows=24204 loops=1)
                Join Filter: (rg_4.operation_assetid = ag.operation_assetid)
                Rows Removed by Join Filter: 36312
                ->  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)
                ->  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)
  CTE alltogether
    ->  Nested Loop  (cost=0.00..0.05 rows=1 width=580) (actual time=78528.138..78528.390 rows=246 loops=1)
          Join Filter: (rg_5.operation_assetid = ra.operation_assetid)
          Rows Removed by Join Filter: 738
          ->  CTE Scan on agg_rg rg_5  (cost=0.00..0.02 rows=1 width=572) (actual time=78509.011..78509.054 rows=246 loops=1)
          ->  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)
  CTE getari
    ->  GroupAggregate  (cost=358653.54..402341.44 rows=1 width=20) (actual time=96265.612..96265.612 rows=0 loops=1)
          Group Key: rg_6.catchment_assetid
          ->  Merge Join  (cost=358653.54..399821.05 rows=336050 width=20) (actual time=96265.609..96265.609 rows=0 loops=1)
                Merge Cond: ((((td.value1)::bigint) = rg_6.catchment_assetid) AND (((td.value2)::bigint) = gla.itemid))
                ->  Sort  (cost=358096.17..364817.40 rows=2688491 width=16) (actual time=2049.437..2644.799 rows=2688486 loops=1)
                      Sort Key: ((td.value1)::bigint), ((td.value2)::bigint)
                      Sort Method: external merge  Disk: 110232kB
                      ->  Seq Scan on tabulardata td  (cost=0.00..44074.91 rows=2688491 width=16) (actual time=0.513..730.277 rows=2688491 loops=1)
                ->  Sort  (cost=557.37..569.87 rows=5000 width=28) (actual time=93385.411..93386.888 rows=24180 loops=1)
                      Sort Key: rg_6.catchment_assetid, gla.itemid
                      Sort Method: quicksort  Memory: 2658kB
                      ->  Nested Loop  (cost=120.16..250.18 rows=5000 width=28) (actual time=674.386..93381.899 rows=24180 loops=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)
                            ->  Merge Join  (cost=120.16..200.16 rows=5000 width=24) (actual time=400.722..400.766 rows=104 loops=233)
                                  Merge Cond: (gla.itemid = glad.itemid)
                                  ->  Sort  (cost=60.08..62.58 rows=1000 width=12) (actual time=211.911..211.920 rows=104 loops=233)
                                        Sort Key: gla.itemid
                                        Sort Method: quicksort  Memory: 25kB
                                        ->  Function Scan on getlayer_arigrid gla  (cost=0.25..10.25 rows=1000 width=12) (actual time=211.877..211.891 rows=104 loops=233)
                                  ->  Sort  (cost=60.08..62.58 rows=1000 width=12) (actual time=189.618..189.625 rows=104 loops=232)
                                        Sort Key: glad.itemid
                                        Sort Method: quicksort  Memory: 25kB
                                        ->  Function Scan on getlayer_aridepthgrid glad  (cost=0.25..10.25 rows=1000 width=12) (actual time=189.584..189.597 rows=104 loops=232)
  ->  Nested Loop  (cost=0.00..0.05 rows=1 width=592) (actual time=174794.118..174794.118 rows=0 loops=1)
        Join Filter: (a.catchment_assetid = ga.catchment_assetid)
        ->  CTE Scan on alltogether a  (cost=0.00..0.02 rows=1 width=572) (actual time=78528.139..78528.469 rows=246 loops=1)
        ->  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: 174808.708 ms

Per node type stats

node type	count	sum of times	% of query
Append	1	0.061 ms	0.0 %
Bitmap Heap Scan	1	899.580 ms	0.5 %
Bitmap Index Scan	2	290.785 ms	0.2 %
BitmapAnd	1	3.575 ms	0.0 %
CTE Scan	14	256,552.694 ms	146.8 %
Function Scan	3	170,245.145 ms	97.4 %
GroupAggregate	1	0.003 ms	0.0 %
HashAggregate	2	5.210 ms	0.0 %
Index Scan	5	318.895 ms	0.2 %
Merge Join	2	242.040 ms	0.1 %
Nested Loop	10	97.088 ms	0.1 %
Nested Loop Left Join	1	0.813 ms	0.0 %
Seq Scan	1	730.277 ms	0.4 %
Sort	7	1,939.844 ms	1.1 %
Unique	3	7.712 ms	0.0 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
asset	1	50.794 ms	0.0 %
Index Scan	1	50.794 ms	100.0 %
gaugesummary	2	176.381 ms	0.1 %
Index Scan	2	176.381 ms	100.0 %
geometrymultipolygondata	3	991.300 ms	0.6 %
Bitmap Heap Scan	1	899.580 ms	90.7 %
Index Scan	2	91.720 ms	9.3 %
tabulardata	1	730.277 ms	0.4 %
Seq Scan	1	730.277 ms	100.0 %

explain.depesz.com

PostgreSQL's explain analyze made readable

Result: vsR3

Color mode:

Visible columns:

Per node type stats

Per table stats