Repository: dalibo/pev2
Pull request #767 (2025-06-24 06:13:07)

Fix exclusives computation for subplans


Patch:
diff --git a/src/filters.ts b/src/filters.ts
index 11f35e1..98eb4aa 100644
--- a/src/filters.ts
+++ b/src/filters.ts
@@ -50,8 +50,7 @@ export function cost(value: number): string {
   if (value === undefined) {
     return "N/A"
   }
-  value = parseFloat(value.toPrecision(3))
-  return value.toLocaleString()
+  return value.toLocaleString(undefined, { minimumFractionDigits: 2 })
 }
 
 export function rows(value: number): string {
diff --git a/src/services/plan-service.ts b/src/services/plan-service.ts
index e16ada1..c43f672 100644
--- a/src/services/plan-service.ts
+++ b/src/services/plan-service.ts
@@ -28,9 +28,16 @@ interface JitElement {
   node: object
 }
 
+type recurseItemType = Array<[Node, recurseItemType]>
+
 export class PlanService {
   private static instance: PlanService
   private nodeId = 0
+  private flat: Node[] = []
+
+  private recurse(nodes: Node[]): recurseItemType {
+    return _.map(nodes, (node) => [node, this.recurse(node[NodeProp.PLANS])])
+  }
 
   public createPlan(
     planName: string,
@@ -62,6 +69,16 @@ export class PlanService {
 
     this.nodeId = 1
     this.processNode(planContent.Plan, plan)
+
+    this.flat = this.flat.concat(
+      _.flattenDeep(this.recurse([plan.content.Plan as Node])),
+    )
+    _.each(plan.ctes, (cte) => {
+      this.flat = this.flat.concat(_.flattenDeep(this.recurse([cte as Node])))
+    })
+
+    this.fixCteScansDuration(plan)
+    this.fixInitPlanUsageDuration(plan)
     this.calculateMaximums(plan)
     return plan
   }
@@ -109,32 +126,22 @@ export class PlanService {
   }
 
   public calculateMaximums(plan: IPlan) {
-    type recurseItemType = Array<[Node, recurseItemType]>
-    function recurse(nodes: Node[]): recurseItemType {
-      return _.map(nodes, (node) => [node, recurse(node[NodeProp.PLANS])])
-    }
-    let flat: Node[] = []
-    flat = flat.concat(_.flattenDeep(recurse([plan.content.Plan as Node])))
-    _.each(plan.ctes, (cte) => {
-      flat = flat.concat(_.flattenDeep(recurse([cte as Node])))
-    })
-
-    const largest = _.maxBy(flat, NodeProp.ACTUAL_ROWS_REVISED)
+    const largest = _.maxBy(this.flat, NodeProp.ACTUAL_ROWS_REVISED)
     if (largest) {
       plan.content.maxRows = largest[NodeProp.ACTUAL_ROWS_REVISED] as number
     }
 
-    const costliest = _.maxBy(flat, NodeProp.EXCLUSIVE_COST)
+    const costliest = _.maxBy(this.flat, NodeProp.EXCLUSIVE_COST)
     if (costliest) {
       plan.content.maxCost = costliest[NodeProp.EXCLUSIVE_COST] as number
     }
 
-    const totalCostliest = _.maxBy(flat, NodeProp.TOTAL_COST)
+    const totalCostliest = _.maxBy(this.flat, NodeProp.TOTAL_COST)
     if (totalCostliest) {
       plan.content.maxTotalCost = totalCostliest[NodeProp.TOTAL_COST] as number
     }
 
-    const slowest = _.maxBy(flat, NodeProp.EXCLUSIVE_DURATION)
+    const slowest = _.maxBy(this.flat, NodeProp.EXCLUSIVE_DURATION)
     if (slowest) {
       plan.content.maxDuration = slowest[NodeProp.EXCLUSIVE_DURATION] as number
     }
@@ -151,7 +158,7 @@ export class PlanService {
         (o[NodeProp.EXCLUSIVE_SHARED_WRITTEN_BLOCKS] as number)
       )
     }
-    const highestShared = _.maxBy(flat, (o) => {
+    const highestShared = _.maxBy(this.flat, (o) => {
       return sumShared(o)
     }) as Node
     if (highestShared && sumShared(highestShared)) {
@@ -164,7 +171,7 @@ export class PlanService {
         (o[NodeProp.EXCLUSIVE_TEMP_WRITTEN_BLOCKS] as number)
       )
     }
-    const highestTemp = _.maxBy(flat, (o) => {
+    const highestTemp = _.maxBy(this.flat, (o) => {
       return sumTemp(o)
     }) as Node
     if (highestTemp && sumTemp(highestTemp)) {
@@ -179,7 +186,7 @@ export class PlanService {
         (o[NodeProp.EXCLUSIVE_LOCAL_WRITTEN_BLOCKS] as number)
       )
     }
-    const highestLocal = _.maxBy(flat, (o) => {
+    const highestLocal = _.maxBy(this.flat, (o) => {
       return sumLocal(o)
     })
     if (highestLocal && sumLocal(highestLocal)) {
@@ -195,7 +202,7 @@ export class PlanService {
         (o[NodeProp.EXCLUSIVE_SUM_IO_WRITE_TIME] as number)
       )
     }
-    const highestIo = _.maxBy(flat, (o) => {
+    const highestIo = _.maxBy(this.flat, (o) => {
       return sumIo(o)
     })
     if (highestIo && sumIo(highestIo)) {
@@ -203,7 +210,7 @@ export class PlanService {
     }
 
     const highestEstimateFactor = _.max(
-      _.map(flat, (node) => {
+      _.map(this.flat, (node) => {
         const f = node[NodeProp.PLANNER_ESTIMATE_FACTOR]
         if (f !== Infinity) {
           return f
@@ -240,10 +247,7 @@ export class PlanService {
     }
 
     _.each(node[NodeProp.PLANS], (subPlan) => {
-      if (
-        subPlan[NodeProp.PARENT_RELATIONSHIP] !== "InitPlan" &&
-        subPlan[NodeProp.TOTAL_COST]
-      ) {
+      if (subPlan[NodeProp.TOTAL_COST]) {
         node[NodeProp.EXCLUSIVE_COST] =
           (node[NodeProp.EXCLUSIVE_COST] as number) -
           (subPlan[NodeProp.TOTAL_COST] as number)
@@ -273,14 +277,107 @@ export class PlanService {
     )
   }
 
-  // recursive function to get the sum of actual durations of a a node children
+  public fixCteScansDuration(plan: IPlan) {
+    // No need for fix if plan is not analyzed
+    if (!plan.isAnalyze) {
+      return
+    }
+
+    // Iterate over the CTEs
+    _.each(plan.ctes, (cte) => {
+      // Time spent in the CTE itself
+      const cteDuration = cte[NodeProp.ACTUAL_TOTAL_TIME]
+
+      // Find all nodes that are "CTE Scan" for the given CTE
+      const cteScans = _.filter(
+        this.flat,
+        (node) =>
+          `CTE ${node[NodeProp.CTE_NAME]}` == cte[NodeProp.SUBPLAN_NAME],
+      )
+
+      // Sum of exclusive time for the CTE Scans
+      const sumScansDuration = _.sumBy(
+        cteScans,
+        (node) => node[NodeProp.EXCLUSIVE_DURATION],
+      )
+
+      // Subtract exclusive time proportionally
+      _.each(cteScans, (node) => {
+        node[NodeProp.EXCLUSIVE_DURATION] =
+          node[NodeProp.EXCLUSIVE_DURATION] -
+          (cteDuration * node[NodeProp.ACTUAL_TOTAL_TIME]) / sumScansDuration
+      })
+    })
+  }
+
+  public fixInitPlanUsageDuration(plan: IPlan) {
+    // No need for fix if plan is not analyzed
+    if (!plan.isAnalyze) {
+      return
+    }
+
+    // Find all initPlans
+    const initPlans = _.filter(
+      this.flat,
+      (node) => node[NodeProp.PARENT_RELATIONSHIP] == "InitPlan",
+    )
+
+    _.each(initPlans, (subPlan) => {
+      // Get the sub plan name
+      // It can be either:
+      //  - InitPlan 2 (returns $1) -> $1
+      //  - InitPlan 2 -> InitPlan 2
+      if (!subPlan[NodeProp.SUBPLAN_NAME]) {
+        return
+      }
+      const matches = /(InitPlan\s+[1-9]+)(?:\s+\(returns (\$[0-9]+)\))*/m.exec(
+        subPlan[NodeProp.SUBPLAN_NAME],
+      )
+      if (!matches) {
+        return
+      }
+      const name = matches[2] || matches[1]
+
+      // Find all nodes that are using data from this InitPlan
+      // There should be the name of the sub plan somewhere in the extra info
+      _.each(
+        _.filter(
+          this.flat,
+          (node) => node[NodeProp.PARENT_RELATIONSHIP] != "InitPlan",
+        ),
+        (node) => {
+          _.each(node, (value) => {
+            if (typeof value != "string") {
+              return
+            }
+            // Value for node property should contain sub plan name (with a number
+            // matching exactly)
+            const matches = new RegExp(
+              `.*${name.replace(/[^a-zA-Z0-9]/g, "\\$&")}[0-9]?`,
+            ).exec(value)
+            if (matches) {
+              node[NodeProp.EXCLUSIVE_DURATION] -=
+                subPlan[NodeProp.ACTUAL_TOTAL_TIME]
+              // Stop iterating for this node
+              return false
+            }
+          })
+        },
+      )
+    })
+  }
+
+  // function to get the sum of actual durations of a a node children
   public childrenDuration(node: Node, duration: number) {
     _.each(node[NodeProp.PLANS], (child) => {
       // Subtract sub plans duration from this node except for InitPlans
       // (ie. CTE)
-      if (child[NodeProp.PARENT_RELATIONSHIP] !== "InitPlan") {
-        duration += child[NodeProp.EXCLUSIVE_DURATION] || 0 // Duration may not be set
-        duration = this.childrenDuration(child, duration)
+      if (
+        child[NodeProp.PARENT_RELATIONSHIP] !== "InitPlan" ||
+        (child[NodeProp.PARENT_RELATIONSHIP] == "InitPlan" &&
+          node[NodeProp.NODE_TYPE] == "Result")
+      ) {
+        duration += child[NodeProp.ACTUAL_TOTAL_TIME] || 0 // Duration may not be set
       }
     })
     return duration
@@ -436,7 +533,7 @@ export class PlanService {
         out[out.length - 1] += line
       } else if (
         line.match(
-          /^(?:Total\s+runtime|Planning\s+time|Execution\s+time|Time|Filter|Output|JIT)/i,
+          /^(?:Total\s+runtime|Planning(\s+time)?|Execution\s+time|Time|Filter|Output|JIT)/i,
         )
       ) {
         out.push(line)
@@ -1251,9 +1348,16 @@ export class PlanService {
     ]
     _.each(properties, (property) => {
       const sum = Number(
-        _.sumBy(node[NodeProp.PLANS], (child: Node) => {
-          return (child[NodeProp[property]] as number) || 0
-        }).toFixed(3),
+        _.sumBy(
+          // Don't take subplans into account (InitPlan)
+          _.filter(
+            node[NodeProp.PLANS],
+            (child: Node) => !child[NodeProp.SUBPLAN_NAME],
+          ),
+          (child: Node) => {
+            return (child[NodeProp[property]] as number) || 0
+          },
+        ).toFixed(3),
       )
       const exclusivePropertyString = ("EXCLUSIVE_" +
         property) as keyof typeof NodeProp


Test patch:
diff --git a/src/services/__tests__/cte-exclusive-time.spec.ts b/src/services/__tests__/cte-exclusive-time.spec.ts
new file mode 100644
index 0000000..50b032c
--- /dev/null
+++ b/src/services/__tests__/cte-exclusive-time.spec.ts
@@ -0,0 +1,47 @@
+import { describe, expect, test } from "vitest"
+import type { IPlan, IPlanContent, Node } from "@/interfaces"
+import { NodeProp } from "@/enums"
+import { PlanService } from "@/services/plan-service"
+import _ from "lodash"
+import * as fs from "fs"
+import { fileURLToPath } from "url"
+import * as path from "path"
+
+const __filename = fileURLToPath(import.meta.url)
+const dir = path.join(path.dirname(__filename), "cte-exclusive-time")
+const files = fs.readdirSync(dir)
+let tests = files.map((file: string) => file.replace(/\..*/, ""))
+
+function sumExclusiveDuration(nodes: Node[]): number {
+  return _.sumBy(nodes, (node) => {
+    const baseValue = node[NodeProp.EXCLUSIVE_DURATION]
+    const childrenSum = node[NodeProp.PLANS]
+      ? sumExclusiveDuration(node[NodeProp.PLANS])
+      : 0
+    return baseValue + childrenSum
+  })
+}
+
+tests = _.uniq(tests)
+tests.forEach((planTest: string) => {
+  describe("From text: Plan " + planTest, () => {
+    test("Sum of exclusive times is correct", () => {
+      const planFile = path.join(dir, planTest + ".plan")
+      const planText = fs.readFileSync(planFile, { encoding: "utf-8" })
+      const planService = new PlanService()
+      const r = planService.fromSource(planText) as IPlanContent
+      const plan: IPlan = planService.createPlan("", r, "")
+
+      // Excpect the sum of exclusives to be approvimatively equal to the
+      // duration of the root node
+      const min = plan.content.Plan[NodeProp.ACTUAL_TOTAL_TIME] * 0.99
+      const max = plan.content.Plan[NodeProp.ACTUAL_TOTAL_TIME] * 1.01
+      expect(
+        sumExclusiveDuration([plan.content.Plan].concat(plan.ctes)),
+      ).toBeGreaterThan(min)
+      expect(
+        sumExclusiveDuration([plan.content.Plan].concat(plan.ctes)),
+      ).toBeLessThan(max)
+    })
+  })
+})
diff --git a/src/services/__tests__/cte-exclusive-time/01.plan b/src/services/__tests__/cte-exclusive-time/01.plan
new file mode 100644
index 0000000..245ae34
--- /dev/null
+++ b/src/services/__tests__/cte-exclusive-time/01.plan
@@ -0,0 +1,14 @@
+Nested Loop  (cost=19202.84..76099.33 rows=53175 width=144) (actual time=146.530..2999.096 rows=4379 loops=1)
+  ->  HashAggregate  (cost=19202.08..19733.83 rows=53175 width=16) (actual time=144.782..147.879 rows=4379 loops=1)
+        Group Key: m.principal_id
+        ->  Hash Join  (cost=7564.47..18843.90 rows=143269 width=16) (actual time=121.642..142.157 rows=4608 loops=1)
+              Hash Cond: (se.id = m.community_id)
+              ->  Function Scan on find_all_id_under_node se  (cost=0.25..10.25 rows=1000 width=16) (actual time=0.603..0.604 rows=6 loops=1)
+              ->  Hash  (cost=5280.54..5280.54 rows=118054 width=32) (actual time=121.000..121.000 rows=117942 loops=1)
+                    Buckets: 65536  Batches: 4  Memory Usage: 2688kB
+                    ->  Seq Scan on member_community_map m  (cost=0.00..5280.54 rows=118054 width=32) (actual time=0.010..54.564 rows=117942 loops=1)
+  ->  CTE Scan on acl_row  (cost=0.76..1.03 rows=1 width=144) (actual time=0.649..0.650 rows=1 loops=4379)
+        CTE acl_row
+          ->  Result  (cost=0.00..0.76 rows=1 width=112) (actual time=0.607..0.607 rows=1 loops=4379)
+Planning time: 0.650 ms
+Execution time: 2999.940 ms
diff --git a/src/services/__tests__/cte-exclusive-time/02.plan b/src/services/__tests__/cte-exclusive-time/02.plan
new file mode 100644
index 0000000..ea98076
--- /dev/null
+++ b/src/services/__tests__/cte-exclusive-time/02.plan
@@ -0,0 +1,14 @@
+                                                         QUERY PLAN
+────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
+ Append  (cost=0.07..0.35 rows=4 width=8) (actual time=101.225..504.954 rows=5 loops=1)
+   CTE x
+     ->  Function Scan on generate_series i  (cost=0.00..0.07 rows=5 width=8) (actual time=101.209..504.880 rows=5 loops=1)
+   ->  CTE Scan on x  (cost=0.00..0.11 rows=2 width=8) (actual time=101.223..504.937 rows=2 loops=1)
+         Filter: (i < 3)
+         Rows Removed by Filter: 3
+   ->  CTE Scan on x x_1  (cost=0.00..0.11 rows=2 width=8) (actual time=0.005..0.007 rows=3 loops=1)
+         Filter: (i > 2)
+         Rows Removed by Filter: 2
+ Planning Time: 0.499 ms
+ Execution Time: 505.124 ms
+(11 rows)
diff --git a/src/services/__tests__/cte-exclusive-time/03.plan b/src/services/__tests__/cte-exclusive-time/03.plan
new file mode 100644
index 0000000..af72934
--- /dev/null
+++ b/src/services/__tests__/cte-exclusive-time/03.plan
@@ -0,0 +1,76 @@
+                                                                                                                                                   QUERY PLAN
+
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+--------------------------------------------------------------------------------------------------
+ Subquery Scan location_state_templates  (cost=18119.01..18688.60 rows=200 width=100) (actual time=135738.621..136606.664 rows=19057 loops=1)
+   CTE nested_templates
+     ->  Recursive Union  (cost=2657.85..17981.86 rows=1847 width=147) (actual time=130.693..135231.371 rows=56380 loops=1)
+           ->  GroupAggregate  (cost=2657.85..3055.15 rows=1827 width=80) (actual time=130.662..196.354 rows=18266 loops=1)
+                 ->  Sort  (cost=2657.85..2703.51 rows=18266 width=80) (actual time=130.630..146.372 rows=18266 loops=1)
+                       Sort Key: bt.componenturl, bt.assetid, bt.aspect, bt.templateid
+                       Sort Method:  external merge  Disk: 1488kB
+                       ->  Hash Left Join  (cost=59.05..552.90 rows=18266 width=80) (actual time=0.718..21.493 rows=18266 loops=1)
+                             Hash Cond: (bt.assetid = tal.assetid)
+                             Join Filter: ((bt.componenturl = tal.componenturl) AND (bt.aspect = tal.template_aspect))
+                             ->  Seq Scan on base_templates bt  (cost=0.00..411.66 rows=18266 width=72) (actual time=0.006..6.723 rows=18266 loops=1)
+                             ->  Hash  (cost=58.92..58.92 rows=10 width=76) (actual time=0.706..0.706 rows=266 loops=1)
+                                   ->  Seq Scan on template_aspect_limits tal  (cost=0.00..58.92 rows=10 width=76) (actual time=0.012..0.548 rows=266 loops=1)
+                                         Filter: ((propertyid)::text = ''::text)
+           ->  Append  (cost=1488.92..1488.98 rows=2 width=147) (actual time=67454.018..67477.691 rows=19057 loops=2)
+                 CTE nested_level
+                   ->  GroupAggregate  (cost=1488.74..1488.81 rows=1 width=187) (actual time=967.564..1029.975 rows=19057 loops=2)
+                         ->  Sort  (cost=1488.74..1488.74 rows=1 width=187) (actual time=966.769..977.630 rows=19076 loops=2)
+                               Sort Key: nt.componenturl, nt.assetid, tp.propertyid, (CASE WHEN (aluatpvp.aspect <@ nt.aspect) THEN nt.aspect ELSE aluatpvp.aspect END), nt.limits, aluatpvp.templateid, nt.aspect
+                               Sort Method:  quicksort  Memory: 17kB
+                               ->  Nested Loop Left Join  (cost=1480.41..1488.73 rows=1 width=187) (actual time=672.525..819.753 rows=19076 loops=2)
+                                     Join Filter: ((CASE WHEN (aluatpvp.aspect <@ nt.aspect) THEN nt.aspect ELSE aluatpvp.aspect END) = tal.template_aspect)
+                                     ->  Unique  (cost=1480.41..1480.43 rows=1 width=152) (actual time=672.495..703.064 rows=19057 loops=2)
+                                           ->  Sort  (cost=1480.41..1480.42 rows=1 width=152) (actual time=671.605..683.163 rows=19094 loops=2)
+                                                 Sort Key: nt.componenturl, nt.assetid, tp.propertyid, (CASE WHEN (aluatpvp.aspect <@ nt.aspect) THEN nt.aspect ELSE aluatpvp.aspect END), ea.system_sort_number
+                                                 Sort Method:  quicksort  Memory: 17kB
+                                                 ->  Nested Loop  (cost=970.03..1480.40 rows=1 width=152) (actual time=17.198..506.362 rows=19094 loops=2)
+                                                       Join Filter: (aluatpvp.aspect = ea.aspect)
+                                                       ->  Nested Loop Anti Join  (cost=970.03..1442.72 rows=1 width=148) (actual time=17.187..355.264 rows=19094 loops=2)
+                                                             Join Filter: (tal.limit_aspect <@ aluatpvp.aspect)
+                                                             ->  Nested Loop  (cost=966.50..1432.28 rows=1 width=148) (actual time=17.164..285.476 rows=19197 loops=2)
+                                                                   Join Filter: ((nt.aspect @> aluatpvp.aspect) OR (nt.aspect <@ aluatpvp.aspect))
+                                                                   ->  Hash Join  (cost=966.50..1401.33 rows=32 width=112) (actual time=17.108..40.393 rows=18266 loops=2)
+                                                                         Hash Cond: (nt.templateid = tp.templateid)
+                                                                         ->  WorkTable Scan on nested_templates nt  (cost=0.00..365.40 rows=18270 width=104) (actual time=0.043..15.877 rows=28190 loops=2)
+                                                                         ->  Hash  (cost=966.49..966.49 rows=1 width=16) (actual time=1.086..1.086 rows=6 loops=1)
+                                                                               ->  Nested Loop Semi Join  (cost=16.05..966.49 rows=1 width=16) (actual time=0.165..1.073 rows=6 loops=1)
+                                                                                     Join Filter: ((((tp.propertyid)::text = (p.propertyid)::text) AND ((p.type)::text = 'templateid'::text)) OR (tp.propertyid IS NULL))
+                                                                                     ->  Seq Scan on sq_p_template_properties tp  (cost=0.00..1.69 rows=69 width=16) (actual time=0.009..0.031 rows=69 loops=1)
+                                                                                     ->  Materialize  (cost=16.05..21.55 rows=550 width=86) (actual time=0.001..0.007 rows=21 loops=69)
+                                                                                           ->  Seq Scan on sq_p_property p  (cost=0.00..15.50 rows=550 width=86) (actual time=0.035..0.045 rows=23 loops=1)
+                                                                   ->  Index Scan using all_loadable_url_asset_templateid_property_value_positions_pkey on all_loadable_url_asset_templateid_property_value_positions aluatpvp  (cost=0.00..0.94 rows=1 width=104) (actual time=0.010..0.011 rows=1 loops=36532)
+                                                                         Index Cond: ((aluatpvp.componenturl = nt.componenturl) AND (aluatpvp.assetid = nt.assetid) AND ((aluatpvp.propertyid)::text = (tp.propertyid)::text))
+                                                             ->  Bitmap Heap Scan on template_aspect_limits tal  (cost=3.53..10.19 rows=10 width=40) (actual time=0.001..0.001 rows=0 loops=38394)
+                                                                   Recheck Cond: (tal.limitid = ANY (nt.limits))
+                                                                   ->  Bitmap Index Scan on template_aspect_limits_pkey  (cost=0.00..3.53 rows=10 width=0) (actual time=0.001..0.001 rows=0 loops=38394)
+                                                                         Index Cond: (tal.limitid = ANY (nt.limits))
+                                                       ->  Seq Scan on existing_aspects ea  (cost=0.00..22.30 rows=1230 width=36) (actual time=0.001..0.003 rows=7 loops=38188)
+                                     ->  Index Scan using template_aspect_limits_idx1 on template_aspect_limits tal  (cost=0.00..8.27 rows=1 width=108) (actual time=0.004..0.005 rows=0 loops=38114)
+                                           Index Cond: ((nt.componenturl = tal.componenturl) AND (nt.assetid = tal.assetid) AND ((tp.propertyid)::text = (tal.propertyid)::text))
+                 ->  Unique  (cost=0.11..0.12 rows=1 width=147) (actual time=67454.012..67454.012 rows=0 loops=2)
+                       ->  Sort  (cost=0.11..0.12 rows=1 width=147) (actual time=67454.010..67454.010 rows=0 loops=2)
+                             Sort Key: nl1.componenturl, nl1.assetid, nl2.propertyid, nl1.aspect
+                             Sort Method:  quicksort  Memory: 17kB
+                             ->  Nested Loop Anti Join  (cost=0.00..0.10 rows=1 width=147) (actual time=67453.981..67453.981 rows=0 loops=2)
+                                   Join Filter: ((nl1.componenturl = nl3.componenturl) AND (nl1.assetid = nl3.assetid) AND (nl1.aspect = nl3.aspect) AND ((nl2.propertyid)::text = (nl3.propertyid)::text))
+                                   ->  Nested Loop  (cost=0.00..0.06 rows=1 width=147) (actual time=968.533..35256.406 rows=1582 loops=2)
+                                         Join Filter: ((nl1.componenturl = nl2.componenturl) AND (nl1.assetid = nl2.assetid) AND (nl1.source_aspect = nl2.aspect))
+                                         ->  CTE Scan on nested_level nl1  (cost=0.00..0.02 rows=1 width=132) (actual time=967.757..984.136 rows=791 loops=2)
+                                               Filter: (aspect <> source_aspect)
+                                         ->  CTE Scan on nested_level nl2  (cost=0.00..0.02 rows=1 width=115) (actual time=0.005..24.307 rows=38114 loops=1582)
+                                   ->  CTE Scan on nested_level nl3  (cost=0.00..0.02 rows=1 width=111) (actual time=0.006..11.188 rows=17996 loops=3164)
+                 ->  CTE Scan on nested_level  (cost=0.00..0.02 rows=1 width=147) (actual time=0.004..14.390 rows=19057 loops=2)
+   ->  GroupAggregate  (cost=137.15..162.74 rows=200 width=72) (actual time=135738.483..135903.633 rows=19057 loops=1)
+         ->  Sort  (cost=137.15..141.77 rows=1847 width=72) (actual time=135738.428..135812.668 rows=56380 loops=1)
+               Sort Key: nt.componenturl, nt.assetid, nt.aspect
+               Sort Method:  external merge  Disk: 4632kB
+               ->  CTE Scan on nested_templates nt  (cost=0.00..36.94 rows=1847 width=72) (actual time=130.699..135337.593 rows=56380 loops=1)
+   SubPlan 3
+     ->  Aggregate  (cost=2.70..2.71 rows=1 width=12) (actual time=0.034..0.035 rows=1 loops=19057)
+           ->  Seq Scan on sq_p_template_properties  (cost=0.00..2.55 rows=58 width=12) (actual time=0.003..0.021 rows=22 loops=19057)
+                 Filter: (templateid = ANY ($3))
diff --git a/src/services/__tests__/cte-exclusive-time/04.plan b/src/services/__tests__/cte-exclusive-time/04.plan
new file mode 100644
index 0000000..bec13f5
--- /dev/null
+++ b/src/services/__tests__/cte-exclusive-time/04.plan
@@ -0,0 +1,13 @@
+ Limit  (cost=3474.45..3474.45 rows=1 width=6) (actual time=0.078..0.080 rows=0 loops=1)
+   InitPlan 1 (returns $0)
+     ->  Limit  (cost=0.56..2.58 rows=1 width=4) (actual time=0.060..0.060 rows=0 loops=1)
+           ->  Index Scan using pond_item_common_pkey on pond_item_common pond_item_common_1  (cost=0.56..2.58 rows=1 width=4) (actual time=0.060..0.060 rows=0 loops=1)
+                 Index Cond: (objectid = 333)
+   ->  Sort  (cost=3471.87..3471.87 rows=1 width=6) (actual time=0.078..0.078 rows=0 loops=1)
+         Sort Key: pond_item_common.nbr_sale, pond_item_common.objectid
+         Sort Method: quicksort  Memory: 25kB
+         ->  Index Scan using pond_item_common_x1 on pond_item_common  (cost=0.56..3471.86 rows=1 width=6) (actual time=0.063..0.063 rows=0 loops=1)
+               Index Cond: (pond_user = $0)
+               Filter: ((objectid <> 111) AND (status = 30) AND (1 = (misc_bits & 16)) AND (video_standard = ANY ('{1,2,3,4,5,6,7,8,9}'::integer[])))
+ Total runtime: 0.109 ms
+
diff --git a/src/services/__tests__/cte-exclusive-time/05.plan b/src/services/__tests__/cte-exclusive-time/05.plan
new file mode 100644
index 0000000..414c5c1
--- /dev/null
+++ b/src/services/__tests__/cte-exclusive-time/05.plan
@@ -0,0 +1,16 @@
+Aggregate  (cost=259470.50..259470.51 rows=1 width=8) (actual time=1938.292..1938.292 rows=1 loops=1)
+  InitPlan 2 (returns $1)
+    ->  Result  (cost=259419.20..259419.21 rows=1 width=32) (actual time=1736.419..1736.419 rows=1 loops=1)
+          InitPlan 1 (returns $0)
+            ->  Bitmap Heap Scan on user_data_read_access udr  (cost=2990.72..259419.20 rows=116503 width=4) (actual time=86.519..1727.318 rows=124468 loops=1)
+                  Recheck Cond: ((user_id = 1478) AND (entity_type_id = 3))
+                  Rows Removed by Index Recheck: 14687029
+                  Heap Blocks: exact=38322 lossy=79844
+                  ->  Bitmap Index Scan on user_data_read_access_uk1  (cost=0.00..2961.60 rows=116503 width=0) (actual time=71.005..71.005 rows=124468 loops=1)
+                        Index Cond: ((user_id = 1478) AND (entity_type_id = 3))
+  ->  Index Scan using pk_vendor on vendor v  (cost=0.42..51.27 rows=10 width=4) (actual time=1759.869..1931.146 rows=124468 loops=1)
+        Index Cond: (id = ANY ($1))
+        Filter: ((NOT deleted) AND (client_id = 1007))
+Planning time: 1.555 ms
+Execution time: 1938.457 ms
+
diff --git a/src/services/__tests__/from-text/41-planning-buffers-expect b/src/services/__tests__/from-text/41-planning-buffers-expect
new file mode 100644
index 0000000..5dea707
--- /dev/null
+++ b/src/services/__tests__/from-text/41-planning-buffers-expect
@@ -0,0 +1,7 @@
+{
+  "Plan": {
+    "Node Type": "Sort",
+    "Shared Hit Blocks": 27082613,
+    "Shared Read Blocks": 2278
+  }
+}
diff --git a/src/services/__tests__/from-text/41-planning-buffers-plan b/src/services/__tests__/from-text/41-planning-buffers-plan
new file mode 100644
index 0000000..83cc013
--- /dev/null
+++ b/src/services/__tests__/from-text/41-planning-buffers-plan
@@ -0,0 +1,9 @@
+Sort  (cost=585066.84..585066.85 rows=1 width=473) (actual time=156773.141..156784.582 rows=498 loops=1)
+  Output: (replace((au.login)::text, ((eeof.nom_enseigne)::text || '_'::text), ''::text)), ((ph.attr -> '96'::text)), ((ph.attr -> '253'::text)), eau.civilite, au.nom, au.prenom, eau.mail_representant, eau.telephone, ((ph.attr -> '382'::text)), ((ph.attr -> '11'::text)), eau.date_inscription, eau.nombre_connexion, eau.date_derniere_connexion, (COALESCE((sum(euuo.points)), '0'::numeric)), (COALESCE((sum(euuo_1.points)), '0'::numeric)), (COALESCE((sum(eodc.points)), '0'::numeric)), (COALESCE((sum(vpruo.points_restants_ope)), '0'::bigint)), (COALESCE((sum(vpruo_1.points_restants_ope)), '0'::bigint)), (CASE WHEN ((count(af.fk_utilisateur)) IS NULL) THEN '0'::bigint ELSE (count(af.fk_utilisateur)) END), (CASE WHEN ((count(af_1.fk_utilisateur)) IS NULL) THEN '0'::bigint ELSE (count(af_1.fk_utilisateur)) END), (CASE WHEN ((count(af_2.fk_utilisateur)) IS NULL) THEN '0'::bigint ELSE (count(af_2.fk_utilisateur)) END), (CASE WHEN ((count(af_3.fk_utilisateur)) IS NULL) THEN '0'::bigint ELSE (count(af_3.fk_utilisateur)) END), (CASE WHEN ((count(af_4.fk_utilisateur)) IS NULL) THEN '0'::bigint ELSE (count(af_4.fk_utilisateur)) END), (CASE WHEN ((count(af_5.fk_utilisateur)) IS NULL) THEN '0'::bigint ELSE (count(af_5.fk_utilisateur)) END), (CASE WHEN ((count(af_6.fk_utilisateur)) IS NULL) THEN '0'::bigint ELSE (count(af_6.fk_utilisateur)) END), (CASE WHEN ((count(af_7.fk_utilisateur)) IS NULL) THEN '0'::bigint ELSE (count(af_7.fk_utilisateur)) END), (CASE WHEN ((count(af_8.fk_utilisateur)) IS NULL) THEN '0'::bigint ELSE (count(af_8.fk_utilisateur)) END), (CASE WHEN ((count(af_9.fk_utilisateur)) IS NULL) THEN '0'::bigint ELSE (count(af_9.fk_utilisateur)) END), (CASE WHEN ((count(af_10.fk_utilisateur)) IS NULL) THEN '0'::bigint ELSE (count(af_10.fk_utilisateur)) END), (CASE WHEN ((count(af_11.fk_utilisateur)) IS NULL) THEN '0'::bigint ELSE (count(af_11.fk_utilisateur)) END), (string_agg(DISTINCT (er.role_enseigne)::text, ', '::text)), au.login
+  Sort Key: au.login
+  Sort Method: quicksort  Memory: 281kB
+  Buffers: shared hit=27082613 read=2278
+Planning:
+  Buffers: shared hit=2055 read=10
+Planning Time: 38.350 ms
+Execution Time: 156803.034 ms