Repository: dalibo/pev2
Pull request #735 (2025-04-07 18:41:52)

samples.ts: sample query with many features
Query showing as many features as possible.

I'm not happy with the name. Perhaps should it replace the  'With buffers' query?

pgiraud: What is the `tBl` CTE doing? It doesn't seem to be used in the query. Not linked with any node.
pgiraud: What about renaming this name: "With many options"?

Patch:
diff --git a/example/src/samples.ts b/example/src/samples.ts
index f066416..a96ae47 100644
--- a/example/src/samples.ts
+++ b/example/src/samples.ts
@@ -5534,6 +5534,145 @@ const plan8_source = String.raw`{
 }
 `
 
+const plan_asmany_source =` Merge on public.tb  (cost=262602.88..306621.45 rows=0 width=0) (actual time=7798.118..7798.122 rows=0 loops=1)
+   Tuples: inserted=9995 updated=9679 deleted=326
+   Buffers: shared hit=7731564 read=22226 dirtied=27295 written=38115, temp read=9593 written=27592
+   I/O Timings: shared read=22.960 write=186.426, temp read=17.017 write=119.861
+   WAL: records=5049414 fpi=5192 bytes=416135793
+   CTE tbl
+     ->  Insert on pg_temp.tblog  (cost=8704.82..8705.65 rows=0 width=0) (actual time=509.761..509.761 rows=0 loops=1)
+           Buffers: shared hit=3 read=5448 written=5276, local hit=494411 dirtied=2644 written=4269, temp read=1559 written=1568
+           I/O Timings: shared read=10.578 write=13.760, local write=54.430, temp read=2.942 write=6.888
+           ->  Sort  (cost=8704.82..8705.65 rows=333 width=12) (actual time=286.491..347.529 rows=489127 loops=1)
+                 Output: tb_1.i, tb_1.x
+                 Sort Key: tb_1.x
+                 Sort Method: external merge  Disk: 12472kB
+                 Buffers: shared hit=3 read=5448 written=5276, temp read=1559 written=1568
+                 I/O Timings: shared read=10.578 write=13.760, temp read=2.942 write=6.888
+                 ->  Bitmap Heap Scan on public.tb tb_1  (cost=180.48..8690.86 rows=333 width=12) (actual time=30.582..122.685 rows=489127 loops=1)
+                       Output: tb_1.i, tb_1.x
+                       Recheck Cond: ((tb_1.i >= 4000000) AND (tb_1.i <= 5000000))
+                       Filter: ((mod(tb_1.i, 99) > 0) AND (mod(tb_1.i, 98) > 0) AND (mod(tb_1.i, 98) > 0) AND ((tb_1.i + 1) > 0))
+                       Rows Removed by Filter: 10068
+                       Heap Blocks: exact=3181
+                       Buffers: shared hit=3 read=5448 written=5276
+                       I/O Timings: shared read=10.578 write=13.760
+                       ->  Bitmap Index Scan on tb_pkey  (cost=0.00..180.40 rows=8997 width=0) (actual time=30.251..30.251 rows=499195 loops=1)
+                             Index Cond: ((tb_1.i >= 4000000) AND (tb_1.i <= 5000000))
+                             Buffers: shared hit=2 read=2268 written=2181
+                             I/O Timings: shared read=4.315 write=5.384
+   CTE tabb
+     ->  Update on public.ta  (cost=96.13..93079.47 rows=1667044 width=18) (actual time=11.270..6418.935 rows=2499947 loops=1)
+           Output: ta_1.i, ta_1.x, ta_1.filler
+           Update on public.ta1 ta_1
+           Update on public.ta2 ta_2
+           Buffers: shared hit=7542762 read=22126 dirtied=26988 written=37604
+           I/O Timings: shared read=22.722 write=184.053
+           WAL: records=4999894 fpi=5162 bytes=412139886
+           InitPlan 3
+             ->  Result  (cost=96.12..96.13 rows=1 width=4) (actual time=0.052..0.053 rows=1 loops=1)
+                   Output: ((InitPlan 2).col1 / 2)
+                   Buffers: shared hit=2 read=2
+                   I/O Timings: shared read=0.007
+                   InitPlan 2
+                     ->  Limit  (cost=0.43..96.12 rows=1 width=4) (actual time=0.048..0.048 rows=1 loops=1)
+                           Output: tb_2.i
+                           Buffers: shared hit=2 read=2
+                           I/O Timings: shared read=0.007
+                           ->  Index Only Scan Backward using tb_pkey on public.tb tb_2  (cost=0.43..95690.29 rows=1000 width=4) (actual time=0.045..0.045 rows=1 loops=1)
+                                 Output: tb_2.i
+                                 Filter: (((tb_2.i + 1) > 0) AND ((tb_2.i + 2) > 0) AND (mod(tb_2.i, 99) = 0))
+                                 Rows Removed by Filter: 38
+                                 Heap Fetches: 39
+                                 Buffers: shared hit=2 read=2
+                                 I/O Timings: shared read=0.007
+           ->  Append  (cost=0.00..92983.35 rows=1667044 width=18) (actual time=0.078..650.328 rows=2499947 loops=1)
+                 Buffers: shared hit=2 read=22126 written=8891
+                 I/O Timings: shared read=22.722 write=25.883
+                 ->  Seq Scan on public.ta1 ta_1  (cost=0.00..84624.00 rows=1666667 width=18) (actual time=0.016..486.444 rows=2499947 loops=1)
+                       Output: '2'::double precision, ta_1.tableoid, ta_1.ctid
+                       Filter: (ta_1.i < (InitPlan 3).col1)
+                       Rows Removed by Filter: 2500053
+                       Buffers: shared read=22124 written=8891
+                       I/O Timings: shared read=22.715 write=25.883
+                 ->  Seq Scan on public.ta2 ta_2  (cost=0.00..24.12 rows=377 width=18) (never executed)
+                       Output: '2'::double precision, ta_2.tableoid, ta_2.ctid
+                       Filter: (ta_2.i < (InitPlan 3).col1)
+   CTE tab
+     ->  Limit  (cost=160767.33..160817.33 rows=20000 width=44) (actual time=7700.673..7704.219 rows=20000 loops=1)
+           Output: tabb.i, tabb.x, tabb.filler
+           Buffers: shared hit=7542762 read=22126 dirtied=26988 written=37604, temp read=9593 written=27592
+           I/O Timings: shared read=22.722 write=184.053, temp read=17.017 write=119.861
+           WAL: records=4999894 fpi=5162 bytes=412139886
+           ->  Sort  (cost=160767.33..164934.94 rows=1667044 width=44) (actual time=7563.572..7565.984 rows=20000 loops=1)
+                 Output: tabb.i, tabb.x, tabb.filler
+                 Sort Key: tabb.x
+                 Sort Method: external merge  Disk: 73440kB
+                 Buffers: shared hit=7542762 read=22126 dirtied=26988 written=37604, temp read=9593 written=27592
+                 I/O Timings: shared read=22.722 write=184.053, temp read=17.017 write=119.861
+                 WAL: records=4999894 fpi=5162 bytes=412139886
+                 ->  CTE Scan on tabb  (cost=0.00..33340.88 rows=1667044 width=44) (actual time=11.274..7104.296 rows=2499947 loops=1)
+                       Output: tabb.i, tabb.x, tabb.filler
+                       Buffers: shared hit=7542762 read=22126 dirtied=26988 written=37604, temp written=9155
+                       I/O Timings: shared read=22.722 write=184.053, temp write=58.774
+                       WAL: records=4999894 fpi=5162 bytes=412139886
+   ->  Nested Loop Left Join  (cost=0.43..44019.00 rows=20000 width=102) (actual time=7700.749..7733.331 rows=20000 loops=1)
+         Output: tb.ctid, tab.i, tab.filler, tab.*
+         Inner Unique: true
+         Buffers: shared hit=7612704 read=22189 dirtied=26988 written=37663, temp read=9593 written=27592
+         I/O Timings: shared read=22.871 write=184.243, temp read=17.017 write=119.861
+         WAL: records=4999894 fpi=5162 bytes=412139886
+         ->  CTE Scan on tab  (cost=0.00..400.00 rows=20000 width=96) (actual time=7700.708..7709.042 rows=20000 loops=1)
+               Output: tab.i, tab.filler, tab.*
+               Buffers: shared hit=7542762 read=22126 dirtied=26988 written=37604, temp read=9593 written=27592
+               I/O Timings: shared read=22.722 write=184.053, temp read=17.017 write=119.861
+               WAL: records=4999894 fpi=5162 bytes=412139886
+         ->  Index Scan using tb_pkey on public.tb  (cost=0.43..2.18 rows=1 width=10) (actual time=0.001..0.001 rows=1 loops=20000)
+               Output: tb.ctid, tb.i
+               Index Cond: (tb.i = tab.i)
+               Buffers: shared hit=69942 read=63 written=59
+               I/O Timings: shared read=0.149 write=0.190
+ Settings: work_mem = '3MB', random_page_cost = '1.5', parallel_tuple_cost = '0', jit_above_cost = '0', jit_inline_above_cost = '0', jit_optimize_above_cost = '0'
+ Planning:
+   Buffers: shared hit=26 read=1
+   I/O Timings: shared read=0.003
+   Memory: used=165kB  allocated=264kB
+ Planning Time: 0.289 ms
+ Trigger RI_ConstraintTrigger_a_18153 for constraint tc1_fkey on tb: time=4399.335 calls=326
+ Trigger RI_ConstraintTrigger_a_18161 for constraint tc2_fkey on tc1: time=6000.010 calls=326
+ JIT:
+   Functions: 31
+   Options: Inlining true, Optimization true, Expressions true, Deforming true
+   Timing: Generation 1.340 ms (Deform 0.303 ms), Inlining 9.145 ms, Optimization 82.044 ms, Emission 57.043 ms, Total 149.572 ms
+ Serialization: time=0.000 ms  output=0kB  format=text
+ Execution Time: 18733.542 ms
+(112 lignes)
+`
+
+// Query with as many features as possible
+// Complete script: src/services/__tests__/20-as_many_features_as_possible.sql
+const plan_asmany_query = `EXPLAIN (ANALYZE,BUFFERS,VERBOSE,SETTINGS,WAL,SERIALIZE,MEMORY,FORMAT TEXT)
+WITH
+tBl  AS ( INSERT INTO tBlog SELECT i,x FROM tB
+          WHERE i BETWEEN 4000000 AND 5000000
+          AND mod(i,99)>0 AND mod(i,98)>0 AND mod(i,98)>0 and i+1>0 /* force bitmap index */
+          ORDER BY x),
+tAbb AS ( UPDATE tA SET x=2 WHERE i < (
+            SELECT max(i)/2 FROM tB  WHERE mod(i,99)=0  AND i+1>0 AND i+2>0  /* for bad stats */
+            )
+          RETURNING * ),
+tAb  AS MATERIALIZED ( SELECT * FROM tAbb ORDER BY x LIMIT 20000)
+MERGE INTO tB
+USING tAb ON (tB.i=tAb.i)
+WHEN NOT MATCHED THEN
+   INSERT VALUES (tAb.i, 0.0, '000')
+WHEN MATCHED AND tB.x > 0.97 THEN
+   DELETE  /*  will raise the ON DELETE trigger (very costly) */
+WHEN MATCHED THEN
+   UPDATE SET filler=tAb.filler
+;
+`
+
 const plan_parallel_source = `[
   {
     "Plan": {
@@ -5770,6 +5909,7 @@ const samples = <Sample[]>[
   ["Many CTEs", plan_many_ctes, ""],
   ["Very large plan", plan8_source, ""],
   ["DELETE with triggers", plan_trigger_source, plan_trigger_query],
+  ["With many options", plan_asmany_source, plan_asmany_query],
   ["Parallel (verbose)", plan_parallel_source, ""],
   ["Parallel (4 workers)", plan_parallel_2_source, plan_parallel_2_query],
 ]
diff --git a/src/enums.ts b/src/enums.ts
index 1a3ffcb..3ac4d70 100644
--- a/src/enums.ts
+++ b/src/enums.ts
@@ -97,6 +97,8 @@ export enum NodeProp {
   PRESORTED_KEY = "Presorted Key",
   FILTER = "Filter",
   STRATEGY = "Strategy",
+  PARTIAL_MODE = "Partial Mode",
+  OPERATION = "Operation",
 
   // computed by pev
   NODE_ID = "nodeId",
diff --git a/src/interfaces.ts b/src/interfaces.ts
index 67edfb4..5330c20 100644
--- a/src/interfaces.ts
+++ b/src/interfaces.ts
@@ -114,6 +114,7 @@ export class Node {
   [NodeProp.AVERAGE_IO_WRITE_SPEED]!: number;
   [NodeProp.IO_READ_TIME]!: number;
   [NodeProp.IO_WRITE_TIME]!: number;
+  [NodeProp.PARTIAL_MODE]!: string;
   [k: string]:
     | Node[]
     | Options
@@ -132,8 +133,8 @@ export class Node {
     }
     this[NodeProp.NODE_TYPE] = type
     // tslint:disable-next-line:max-line-length
-    const scanMatches =
-      /^((?:Parallel\s+)?(?:Seq\sScan|Tid.*Scan|Bitmap\s+Heap\s+Scan|(?:Async\s+)?Foreign\s+Scan|Update|Insert|Delete))\son\s(\S+)(?:\s+(\S+))?$/.exec(
+    const scanAndOperationMatches =
+      /^((?:Parallel\s+)?(?:Seq\sScan|Tid.*Scan|Bitmap\s+Heap\s+Scan|(?:Async\s+)?Foreign\s+Scan|Update|Insert|Delete|Merge))\son\s(\S+)(?:\s+(\S+))?$/.exec(
         type
       )
     const bitmapMatches = /^(Bitmap\s+Index\s+Scan)\son\s(\S+)$/.exec(type)
@@ -147,11 +148,11 @@ export class Node {
       type
     )
     const subqueryMatches = /^(Subquery\sScan)\son\s(.+)$/.exec(type)
-    if (scanMatches) {
-      this[NodeProp.NODE_TYPE] = scanMatches[1]
-      this[NodeProp.RELATION_NAME] = scanMatches[2]
-      if (scanMatches[3]) {
-        this[NodeProp.ALIAS] = scanMatches[3]
+    if (scanAndOperationMatches) {
+      this[NodeProp.NODE_TYPE] = scanAndOperationMatches[1]
+      this[NodeProp.RELATION_NAME] = scanAndOperationMatches[2]
+      if (scanAndOperationMatches[3]) {
+        this[NodeProp.ALIAS] = scanAndOperationMatches[3]
       }
     } else if (bitmapMatches) {
       this[NodeProp.NODE_TYPE] = bitmapMatches[1]
diff --git a/src/node.ts b/src/node.ts
index 318bfcb..7d892b8 100644
--- a/src/node.ts
+++ b/src/node.ts
@@ -87,6 +87,7 @@ export default function useNode(
 
   const nodeName = computed((): string => {
     let nodeName = isParallelAware.value ? "Parallel " : ""
+    nodeName += node[NodeProp.PARTIAL_MODE] ? node[NodeProp.PARTIAL_MODE] + " " : ""
     nodeName += node[NodeProp.NODE_TYPE]
     return nodeName
   })
diff --git a/src/services/help-service.ts b/src/services/help-service.ts
index 5436bd3..d6d70da 100644
--- a/src/services/help-service.ts
+++ b/src/services/help-service.ts
@@ -360,6 +360,7 @@ const notMiscProperties: string[] = [
   NodeProp.ALIAS,
   NodeProp.FUNCTION_NAME,
   NodeProp.STRATEGY,
+  NodeProp.PARTIAL_MODE,
 ]
 
 export function shouldShowProp(key: string, value: unknown): boolean {
diff --git a/src/services/plan-service.ts b/src/services/plan-service.ts
index 78e7b06..bd734f3 100644
--- a/src/services/plan-service.ts
+++ b/src/services/plan-service.ts
@@ -542,6 +542,7 @@ export class PlanService {
       const emptyLineRegex = "^s*$"
       const headerRegex = "^\\s*(QUERY|---|#).*$"
       const prefixRegex = "^(\\s*->\\s*|\\s*)"
+      const partialModeRegex = "(Finalize|Simple|Partial)*"
       const typeRegex = "([^\\r\\n\\t\\f\\v\\:\\(]*?)"
       // tslint:disable-next-line:max-line-length
       const estimationRegex =
@@ -561,29 +562,32 @@ export class PlanService {
       /*
        * Groups
        * 1: prefix
-       * 2: type
-       * 3: estimated_startup_cost
-       * 4: estimated_total_cost
-       * 5: estimated_rows
-       * 6: estimated_row_width
-       * 7: actual_time_first
-       * 8: actual_time_last
-       * 9: actual_rows
-       * 10: actual_loops
-       * 11: actual_rows_
-       * 12: actual_loops_
-       * 13: never_executed
-       * 14: estimated_startup_cost
-       * 15: estimated_total_cost
-       * 16: estimated_rows
-       * 17: estimated_row_width
-       * 18: actual_time_first
-       * 19: actual_time_last
-       * 20: actual_rows
-       * 21: actual_loops
+       * 2: partial mode
+       * 3: type
+       * 4: estimated_startup_cost
+       * 5: estimated_total_cost
+       * 6: estimated_rows
+       * 7: estimated_row_width
+       * 8: actual_time_first
+       * 9: actual_time_last
+       * 10: actual_rows
+       * 11: actual_loops
+       * 12: actual_rows_
+       * 13: actual_loops_
+       * 15: never_executed
+       * 15: estimated_startup_cost
+       * 16: estimated_total_cost
+       * 17: estimated_rows
+       * 18: estimated_row_width
+       * 19: actual_time_first
+       * 20: actual_time_last
+       * 21: actual_rows
+       * 22: actual_loops
        */
       const nodeRegex = new RegExp(
         prefixRegex +
+          partialModeRegex +
+          "\\s*" +
           typeRegex +
           "\\s*" +
           nonCapturingGroupOpen +
@@ -662,55 +666,59 @@ export class PlanService {
         return
       } else if (nodeMatches && !cteMatches && !subMatches) {
         //const prefix = nodeMatches[1]
-        const neverExecuted = nodeMatches[13]
-        const newNode: Node = new Node(nodeMatches[2])
+        const neverExecuted = nodeMatches[14]
+        const newNode: Node = new Node(nodeMatches[3])
         if (
-          (nodeMatches[3] && nodeMatches[4]) ||
-          (nodeMatches[14] && nodeMatches[15])
+          (nodeMatches[4] && nodeMatches[5]) ||
+          (nodeMatches[15] && nodeMatches[16])
         ) {
           newNode[NodeProp.STARTUP_COST] = parseFloat(
-            nodeMatches[3] || nodeMatches[14]
+            nodeMatches[4] || nodeMatches[15]
           )
           newNode[NodeProp.TOTAL_COST] = parseFloat(
-            nodeMatches[4] || nodeMatches[15]
+            nodeMatches[5] || nodeMatches[16]
           )
           newNode[NodeProp.PLAN_ROWS] = parseInt(
-            nodeMatches[5] || nodeMatches[16],
+            nodeMatches[6] || nodeMatches[17],
             0
           )
           newNode[NodeProp.PLAN_WIDTH] = parseInt(
-            nodeMatches[6] || nodeMatches[17],
+            nodeMatches[7] || nodeMatches[18],
             0
           )
         }
         if (
-          (nodeMatches[7] && nodeMatches[8]) ||
-          (nodeMatches[18] && nodeMatches[19])
+          (nodeMatches[8] && nodeMatches[9]) ||
+          (nodeMatches[19] && nodeMatches[20])
         ) {
           newNode[NodeProp.ACTUAL_STARTUP_TIME] = parseFloat(
-            nodeMatches[7] || nodeMatches[18]
+            nodeMatches[8] || nodeMatches[19]
           )
           newNode[NodeProp.ACTUAL_TOTAL_TIME] = parseFloat(
-            nodeMatches[8] || nodeMatches[19]
+            nodeMatches[9] || nodeMatches[20]
           )
         }
 
         if (
-          (nodeMatches[9] && nodeMatches[10]) ||
-          (nodeMatches[11] && nodeMatches[12]) ||
-          (nodeMatches[20] && nodeMatches[21])
+          (nodeMatches[10] && nodeMatches[11]) ||
+          (nodeMatches[12] && nodeMatches[13]) ||
+          (nodeMatches[21] && nodeMatches[22])
         ) {
-          const actual_rows = nodeMatches[9] || nodeMatches[11] || nodeMatches[20]
+          const actual_rows = nodeMatches[10] || nodeMatches[12] || nodeMatches[21]
           if (actual_rows.indexOf(".") != -1) {
             newNode[NodeProp.ACTUAL_ROWS_FRACTIONAL] = true
           }
           newNode[NodeProp.ACTUAL_ROWS] = parseFloat(actual_rows)
           newNode[NodeProp.ACTUAL_LOOPS] = parseInt(
-            nodeMatches[10] || nodeMatches[12] || nodeMatches[21],
+            nodeMatches[11] || nodeMatches[13] || nodeMatches[22],
             0
           )
         }
 
+        if (nodeMatches[2]) {
+          newNode[NodeProp.PARTIAL_MODE] = nodeMatches[2]
+        }
+
         if (neverExecuted) {
           newNode[NodeProp.ACTUAL_LOOPS] = 0
           newNode[NodeProp.ACTUAL_ROWS] = 0
@@ -1267,5 +1275,9 @@ export class PlanService {
       }
       node[NodeProp.NODE_TYPE] = prefix + "Aggregate"
     }
+
+    if (node[NodeProp.NODE_TYPE] == "ModifyTable") {
+      node[NodeProp.NODE_TYPE] = node[NodeProp.OPERATION] as string
+    }
   }
 }


Test patch:
diff --git a/src/services/__tests__/05-parallel-pg12.spec.ts b/src/services/__tests__/05-parallel-pg12.spec.ts
index 324b7fb..2430824 100644
--- a/src/services/__tests__/05-parallel-pg12.spec.ts
+++ b/src/services/__tests__/05-parallel-pg12.spec.ts
@@ -100,7 +100,8 @@ Finalize Aggregate  (cost=423.63..423.64 rows=1 width=8)
   const root = plan.content.Plan
   const partialAggregateNode = root && root.Plans[0].Plans[0]
   test("Node is parallel", () => {
-    expect(partialAggregateNode["Node Type"]).toEqual("Partial Aggregate")
+    expect(partialAggregateNode["Node Type"]).toEqual("Aggregate")
+    expect(partialAggregateNode["Partial Mode"]).toEqual("Partial")
     expect(partialAggregateNode).toHaveProperty("*Workers Planned By Gather")
   })
   const bitmapIndexScanNode = root && root.Plans[0].Plans[0].Plans[0].Plans[0]
@@ -131,7 +132,8 @@ Execution Time: 9.025 ms
   const root = plan.content.Plan
   const partialAggregateNode = root && root.Plans[0].Plans[0]
   test("Node is parallel", () => {
-    expect(partialAggregateNode["Node Type"]).toEqual("Partial Aggregate")
+    expect(partialAggregateNode["Node Type"]).toEqual("Aggregate")
+    expect(partialAggregateNode["Partial Mode"]).toEqual("Partial")
     expect(partialAggregateNode).toHaveProperty("*Workers Planned By Gather")
   })
   const bitmapIndexScanNode = root && root.Plans[0].Plans[0].Plans[0].Plans[0]
@@ -160,7 +162,8 @@ Finalize Aggregate  (cost=423.63..423.64 rows=1 width=8)
   const root = plan.content.Plan
   const partialAggregateNode = root && root.Plans[0].Plans[0]
   test("Node is parallel", () => {
-    expect(partialAggregateNode["Node Type"]).toEqual("Partial Aggregate")
+    expect(partialAggregateNode["Node Type"]).toEqual("Aggregate")
+    expect(partialAggregateNode["Partial Mode"]).toEqual("Partial")
     expect(partialAggregateNode).toHaveProperty("*Workers Planned By Gather")
   })
   const bitmapIndexScanNode = root && root.Plans[0].Plans[0].Plans[0].Plans[0]
@@ -202,7 +205,8 @@ Execution Time: 5.646 ms
   const root = plan.content.Plan
   const partialAggregateNode = root && root.Plans[0].Plans[0]
   test("Node is parallel", () => {
-    expect(partialAggregateNode["Node Type"]).toEqual("Partial Aggregate")
+    expect(partialAggregateNode["Node Type"]).toEqual("Aggregate")
+    expect(partialAggregateNode["Partial Mode"]).toEqual("Partial")
     expect(partialAggregateNode).toHaveProperty("*Workers Planned By Gather")
   })
   const bitmapIndexScanNode = root && root.Plans[0].Plans[0].Plans[0].Plans[0]
diff --git a/src/services/__tests__/20-as_many_features_as_possible.sql b/src/services/__tests__/20-as_many_features_as_possible.sql
new file mode 100644
index 0000000..c69c9c9
--- /dev/null
+++ b/src/services/__tests__/20-as_many_features_as_possible.sql
@@ -0,0 +1,66 @@
+/* Script to show as many features as possible: nested & writing CTEs,
+temp/local/shared buffers, written/read, I/O timings, different nodes,
+partitions, a not-executed node, setting, triggers, JIT, settings,
+bad estimations, heap fetches
+Parallelism is not possible in a writing query
+
+Tested on: PostgreSQL 17 with default configuration
+
+*/
+\timing off
+\set ON_ERROR_STOP 1
+\set ECHO queries
+SET maintenance_work_mem TO '1GB';
+SET synchronous_commit TO off ;
+/* Parameters shown in SETTINGS */
+SET work_mem TO '3MB' ;
+SET random_page_cost TO 1.5;
+SET parallel_tuple_cost TO 0;
+/* Timings */
+SET track_io_timing TO on;
+SET track_wal_io_timing TO on ;
+/* Force JIT */
+SET jit TO on ;
+SET jit_above_cost TO 0 ;
+SET jit_inline_above_cost TO 0 ;
+SET jit_optimize_above_cost TO 0 ;
+/* Cleaning */
+DROP TABLE  IF EXISTS tB, tA, tC1, tC2 ;
+/* Tables */
+CREATE TABLE tB (i int PRIMARY KEY, x float, filler text DEFAULT 'abc') ;
+CREATE UNLOGGED TABLE tA (i int, x float, filler text DEFAULT 'efg') PARTITION BY RANGE (i);
+CREATE TABLE tA1 PARTITION OF tA FOR VALUES FROM (MINVALUE) TO (10000000) ;
+CREATE TABLE tA2 PARTITION OF tA FOR VALUES FROM (10000000) TO (MAXVALUE) ;
+INSERT INTO tA (i) SELECT i FROM generate_series (1,5000000) i ;
+INSERT INTO tB (i, x) SELECT i,random() FROM tA WHERE random() < 0.50 ORDER BY x;
+/* for triggers  */
+CREATE UNLOGGED TABLE tC1 AS SELECT i FROM tB  ;
+ALTER TABLE tC1 ADD CONSTRAINT tc1_fkey FOREIGN KEY (i) REFERENCES tB ON DELETE CASCADE, ADD CONSTRAINT tc1_pkey PRIMARY KEY (i);
+CREATE UNLOGGED TABLE tC2 AS SELECT i FROM tC1 ;
+ALTER TABLE tC2 ADD CONSTRAINT tc2_fkey FOREIGN KEY (i) REFERENCES tC1 ON DELETE CASCADE ; CREATE INDEX ON tC2(i);
+VACUUM (ANALYZE) tC1, tC2, tA1 ; /* not tA2 to have different plans on both partitions  */
+\dt+
+SELECT COUNT(*) as nb_tc1 FROM tc1; SELECT COUNT(*) as nb_tc2 FROM tc2;
+BEGIN ;
+CREATE TEMP TABLE tBlog (i int, x float) ; /* local buffers */
+EXPLAIN (ANALYZE,BUFFERS,VERBOSE,SETTINGS,WAL,SERIALIZE,MEMORY,FORMAT TEXT)
+WITH
+tBl  AS ( INSERT INTO tBlog SELECT i,x FROM tB
+          WHERE i BETWEEN 4000000 AND 5000000
+          AND mod(i,99)>0 AND mod(i,98)>0 AND mod(i,98)>0 and i+1>0 /* force bitmap index */
+          ORDER BY x),
+tAbb AS ( UPDATE tA SET x=2 WHERE i < (
+            SELECT max(i)/2 FROM tB  WHERE mod(i,99)=0  AND i+1>0 AND i+2>0  /* for bad stats */
+            )
+          RETURNING * ),
+tAb  AS MATERIALIZED ( SELECT * FROM tAbb ORDER BY x LIMIT 20000)
+MERGE INTO tB
+USING tAb ON (tB.i=tAb.i)
+WHEN NOT MATCHED THEN
+   INSERT VALUES (tAb.i, 0.0, '000')
+WHEN MATCHED AND tB.x > 0.97 THEN
+   DELETE  /*  will raise the ON DELETE trigger (very costly) */
+WHEN MATCHED THEN
+   UPDATE SET filler=tAb.filler
+;
+ROLLBACK ;
diff --git a/src/services/__tests__/20-triggers-info.spec.ts b/src/services/__tests__/20-triggers-info.spec.ts
new file mode 100644
index 0000000..f3c06e3
--- /dev/null
+++ b/src/services/__tests__/20-triggers-info.spec.ts
@@ -0,0 +1,81 @@
+import { describe, expect, test } from "vitest"
+import { PlanService } from "@/services/plan-service"
+import type { IPlan, IPlanContent } from "@/interfaces"
+
+describe("PlanService", () => {
+  test("Insert node type is correct from text", () => {
+    const planService = new PlanService()
+    const source = `
+      Insert on foo  (cost=0.00..0.01 rows=1 width=24) (actual time=0.504..0.505 rows=0 loops=1)
+        ->  Result  (cost=0.00..0.01 rows=1 width=24) (actual time=0.019..0.020 rows=1 loops=1)
+      Planning Time: 0.072 ms
+      Trigger foo_system_time_generated_always: time=0.401 calls=1
+      Trigger foo_system_time_write_history: time=0.260 calls=1
+      Execution Time: 0.842 ms
+    `
+    const r = planService.fromSource(source) as IPlanContent
+    const plan: IPlan = planService.createPlan("", r, "")
+    const root = plan.content.Plan
+    expect(root["Node Type"]).toEqual("Insert")
+  })
+
+  test("Insert node type is correct from text", () => {
+    const planService = new PlanService()
+    const source = `
+      [
+        {
+          "Plan": {
+            "Node Type": "ModifyTable",
+            "Operation": "Insert",
+            "Parallel Aware": false,
+            "Relation Name": "foo",
+            "Alias": "foo",
+            "Startup Cost": 0.00,
+            "Total Cost": 0.01,
+            "Plan Rows": 1,
+            "Plan Width": 24,
+            "Actual Startup Time": 0.568,
+            "Actual Total Time": 0.568,
+            "Actual Rows": 0,
+            "Actual Loops": 1,
+            "Plans": [
+              {
+                "Node Type": "Result",
+                "Parent Relationship": "Member",
+                "Parallel Aware": false,
+                "Startup Cost": 0.00,
+                "Total Cost": 0.01,
+                "Plan Rows": 1,
+                "Plan Width": 24,
+                "Actual Startup Time": 0.018,
+                "Actual Total Time": 0.018,
+                "Actual Rows": 1,
+                "Actual Loops": 1
+              }
+            ]
+          },
+          "Planning Time": 0.056,
+          "Triggers": [
+            {
+              "Trigger Name": "foo_system_time_generated_always",
+              "Relation": "foo",
+              "Time": 0.435,
+              "Calls": 1
+            },
+            {
+              "Trigger Name": "foo_system_time_write_history",
+              "Relation": "foo",
+              "Time": 0.207,
+              "Calls": 1
+            }
+          ],
+          "Execution Time": 0.824
+        }
+      ]
+    `
+    const r = planService.fromSource(source) as IPlanContent
+    const plan: IPlan = planService.createPlan("", r, "")
+    const root = plan.content.Plan
+    expect(root["Node Type"]).toEqual("Insert")
+  })
+})
diff --git a/src/services/__tests__/from-text.spec.ts b/src/services/__tests__/from-text.spec.ts
index 1896588..17d0b17 100644
--- a/src/services/__tests__/from-text.spec.ts
+++ b/src/services/__tests__/from-text.spec.ts
@@ -14,7 +14,7 @@ const __filename = fileURLToPath(import.meta.url)
 
 const dir = path.join(path.dirname(__filename), "from-text")
 const files = fs.readdirSync(dir)
-let tests = files.map((file: string) => file.replace(/-.*/, ""))
+let tests = files.map((file: string) => file.replace(/-([^-]+)$/, ""))
 tests = _.uniq(tests)
 tests.forEach((planTest: string) => {
   describe("From text: Plan " + planTest, () => {
diff --git a/src/services/__tests__/from-text/40-merge-nodetype-expect b/src/services/__tests__/from-text/40-merge-nodetype-expect
new file mode 100644
index 0000000..b0ebedb
--- /dev/null
+++ b/src/services/__tests__/from-text/40-merge-nodetype-expect
@@ -0,0 +1,5 @@
+{
+  "Plan": {
+    "Node Type": "Merge"
+  }
+}
diff --git a/src/services/__tests__/from-text/40-merge-nodetype-plan b/src/services/__tests__/from-text/40-merge-nodetype-plan
new file mode 100644
index 0000000..3227ec6
--- /dev/null
+++ b/src/services/__tests__/from-text/40-merge-nodetype-plan
@@ -0,0 +1,1 @@
+Merge on public.tb  (cost=1.00..1.05 rows=0 width=0)