konnosDashboard
ExplainVisualizer.parser.ts308 lines · main
1import type { ExplainNode, QueryPlanRow } from './ExplainVisualizer.types'
2
3export interface ExplainSummary {
4 totalTime: number
5 totalCost: number
6 maxCost: number
7 hasSeqScan: boolean
8 seqScanTables: string[]
9 hasIndexScan: boolean
10}
11
12function parseFloatMetric(value: string): number | undefined {
13 const parsed = parseFloat(value)
14 return Number.isFinite(parsed) ? parsed : undefined
15}
16
17function parseIntMetric(value: string): number | undefined {
18 const parsed = parseInt(value, 10)
19 return Number.isNaN(parsed) ? undefined : parsed
20}
21
22// Parse the QUERY PLAN text into a tree structure
23export function parseExplainOutput(rows: readonly QueryPlanRow[]): ExplainNode[] {
24 const lines = rows.map((row) => row['QUERY PLAN'] || '').filter(Boolean)
25 const root: ExplainNode[] = []
26 const stack: { node: ExplainNode; indent: number }[] = []
27
28 // Detail line patterns that should be attached to the previous node
29 const detailPatterns =
30 /^(Filter|Sort Key|Group Key|Hash Cond|Join Filter|Index Cond|Recheck Cond|Rows Removed by Filter|Rows Removed by Index Recheck|Output|Merge Cond|Sort Method|Worker \d+|Buffers|Planning Time|Execution Time|One-Time Filter|InitPlan|SubPlan):/
31
32 for (let i = 0; i < lines.length; i++) {
33 const line = lines[i]
34
35 // Skip empty lines
36 if (!line.trim()) continue
37
38 // Calculate the indentation (number of leading spaces)
39 const leadingMatch = line.match(/^(\s*)/)
40 const leadingSpaces = leadingMatch ? leadingMatch[1].length : 0
41
42 // Check if this line has an arrow (indicates a child operation node)
43 const hasArrow = line.includes('->')
44
45 // Extract the content after any arrow
46 let content = line
47 let effectiveIndent = leadingSpaces
48
49 if (hasArrow) {
50 // Find position of -> and use that for indent calculation
51 const arrowIndex = line.indexOf('->')
52 effectiveIndent = arrowIndex
53 content = line.substring(arrowIndex + 2).trim()
54 } else {
55 content = line.trim()
56 }
57
58 // Skip Planning Time and Execution Time summary lines (at root level)
59 if (
60 content.startsWith('Planning Time:') ||
61 content.startsWith('Execution Time:') ||
62 content.startsWith('Planning:') ||
63 content.startsWith('Execution:')
64 ) {
65 continue
66 }
67
68 // Check if this is a detail line (like Filter:, Sort Key:, etc.)
69 if (detailPatterns.test(content) && stack.length > 0) {
70 // Attach to the most recent node at or above this indentation
71 const currentNode = stack[stack.length - 1].node
72 currentNode.details += (currentNode.details ? '\n' : '') + content
73 continue
74 }
75
76 // Check if this is a continuation of details (indented text without operation pattern)
77 // These are typically wrapped condition expressions
78 if (!hasArrow && stack.length > 0 && leadingSpaces > 0) {
79 const lastItem = stack[stack.length - 1]
80 // If it's more indented than the last node and doesn't look like an operation
81 if (leadingSpaces > lastItem.indent && !content.match(/^\w+.*\(cost=/)) {
82 lastItem.node.details += (lastItem.node.details ? '\n' : '') + content
83 continue
84 }
85 }
86
87 // Parse main operation line: "Operation on table (metrics)"
88 // Match operation with optional metrics in parentheses
89 // Handle multiple metric groups like (cost=...) (actual time=...)
90 const metricsMatch = content.match(/^(.+?)\s*(\([^)]*cost=[^)]+\)(?:\s*\([^)]+\))*)?\s*$/)
91
92 if (!metricsMatch) {
93 continue
94 }
95
96 const [, operationPart, metricsStr] = metricsMatch
97 const metrics = metricsStr
98 ? metricsStr.replace(/^\(|\)$/g, '').replace(/\)\s*\(/g, ' ')
99 : undefined
100
101 // Split operation and object name (e.g., "Seq Scan on users" -> operation: "Seq Scan", details: "users")
102 let operation = operationPart.trim()
103 let details = ''
104
105 // Check for "on tablename" or "using indexname" patterns
106 const onMatch = operationPart.match(/^(.+?)\s+on\s+(.+)$/i)
107 const usingMatch = operationPart.match(/^(.+?)\s+using\s+(.+)$/i)
108
109 if (onMatch) {
110 operation = onMatch[1].trim()
111 details = 'on ' + onMatch[2].trim()
112 } else if (usingMatch) {
113 operation = usingMatch[1].trim()
114 details = 'using ' + usingMatch[2].trim()
115 }
116
117 // Calculate the tree level based on indentation
118 // PostgreSQL typically uses 6 spaces per level for -> nodes
119 const level = hasArrow ? Math.floor(effectiveIndent / 6) + 1 : 0
120
121 const node = createNode(operation, details, metrics, level, line)
122 addNodeToTree(node, effectiveIndent, root, stack)
123 }
124
125 return root
126}
127
128function createNode(
129 operation: string,
130 details: string | undefined,
131 metrics: string | undefined,
132 level: number,
133 raw: string
134): ExplainNode {
135 const node: ExplainNode = {
136 operation: operation.trim(),
137 details: details?.trim() || '',
138 level,
139 children: [],
140 raw,
141 }
142
143 if (metrics) {
144 // Parse cost=start..end
145 const costMatch = metrics.match(/cost=([\d.]+)\.\.([\d.]+)/)
146 if (costMatch) {
147 const start = parseFloatMetric(costMatch[1])
148 const end = parseFloatMetric(costMatch[2])
149 // Only set cost if both values are valid numbers
150 if (start !== undefined && end !== undefined) {
151 node.cost = { start, end }
152 }
153 }
154
155 // Parse rows=N (estimated rows, always the first occurrence)
156 const rowsMatch = metrics.match(/rows=(\d+)/)
157 if (rowsMatch) {
158 node.rows = parseIntMetric(rowsMatch[1])
159 }
160
161 // Parse width=N
162 const widthMatch = metrics.match(/width=(\d+)/)
163 if (widthMatch) {
164 node.width = parseIntMetric(widthMatch[1])
165 }
166
167 // Parse actual time=start..end
168 const actualTimeMatch = metrics.match(/actual time=([\d.]+)\.\.([\d.]+)/)
169 if (actualTimeMatch) {
170 const start = parseFloatMetric(actualTimeMatch[1])
171 const end = parseFloatMetric(actualTimeMatch[2])
172 // Only set actualTime if both values are valid numbers
173 if (start !== undefined && end !== undefined) {
174 node.actualTime = { start, end }
175 }
176
177 // When EXPLAIN ANALYZE is used, the second rows= value (after actual time) is the actual rows
178 const actualTimePart = metrics.substring(metrics.indexOf('actual time='))
179 const actualRowsMatch = actualTimePart.match(/rows=(\d+)/)
180 if (actualRowsMatch) {
181 node.actualRows = parseIntMetric(actualRowsMatch[1])
182 }
183 }
184 }
185
186 return node
187}
188
189// After node creation, parse detail fields like "Rows Removed by Filter"
190export function parseNodeDetails(node: ExplainNode): void {
191 if (node.details) {
192 const rowsRemovedMatch = node.details.match(/Rows Removed by Filter:\s*(\d+)/)
193 if (rowsRemovedMatch) {
194 node.rowsRemovedByFilter = parseIntMetric(rowsRemovedMatch[1])
195 }
196 }
197 node.children.forEach(parseNodeDetails)
198}
199
200function addNodeToTree(
201 node: ExplainNode,
202 indent: number,
203 root: ExplainNode[],
204 stack: { node: ExplainNode; indent: number }[]
205) {
206 // Remove nodes from stack that are at the same or deeper indentation
207 while (stack.length > 0 && stack[stack.length - 1].indent >= indent) {
208 stack.pop()
209 }
210
211 if (stack.length === 0) {
212 root.push(node)
213 } else {
214 stack[stack.length - 1].node.children.push(node)
215 }
216
217 stack.push({ node, indent })
218}
219
220// Calculate max cost for scaling the visualization bars
221function getNodeMaxCost(node: ExplainNode): number {
222 const nodeCost = node.cost?.end || node.actualTime?.end || 0
223 const childrenMax = node.children.reduce((max, child) => Math.max(max, getNodeMaxCost(child)), 0)
224 return Math.max(nodeCost, childrenMax)
225}
226
227export function calculateMaxCost(tree: ExplainNode[]): number {
228 return tree.reduce((max, node) => Math.max(max, getNodeMaxCost(node)), 0)
229}
230
231// Calculate max duration across all nodes for scaling the visualization bars
232function getNodeMaxDuration(node: ExplainNode): number {
233 const nodeDuration = node.actualTime ? node.actualTime.end - node.actualTime.start : 0
234 const childrenMax = node.children.reduce(
235 (max, child) => Math.max(max, getNodeMaxDuration(child)),
236 0
237 )
238 return Math.max(nodeDuration, childrenMax)
239}
240
241export function calculateMaxDuration(tree: ExplainNode[]): number {
242 return tree.reduce((max, node) => Math.max(max, getNodeMaxDuration(node)), 0)
243}
244
245// Calculate summary stats
246export function calculateSummary(tree: ExplainNode[]): ExplainSummary {
247 const stats: ExplainSummary = {
248 totalTime: 0,
249 totalCost: 0,
250 maxCost: 0,
251 hasSeqScan: false,
252 seqScanTables: [],
253 hasIndexScan: false,
254 }
255
256 const traverse = (node: ExplainNode) => {
257 if (node.actualTime) {
258 stats.totalTime = Math.max(stats.totalTime, node.actualTime.end)
259 }
260 if (node.cost) {
261 stats.maxCost = Math.max(stats.maxCost, node.cost.end)
262 }
263 const op = node.operation.toLowerCase()
264 if (op.includes('seq scan')) {
265 stats.hasSeqScan = true
266 const tableMatch = node.details.match(/on\s+((?:"[^"]+"|[\w]+)(?:\.(?:"[^"]+"|[\w]+))*)/)
267 if (tableMatch) stats.seqScanTables.push(tableMatch[1])
268 }
269 if (op.includes('index')) {
270 stats.hasIndexScan = true
271 }
272 node.children.forEach(traverse)
273 }
274 tree.forEach(traverse)
275
276 stats.totalCost = tree[0]?.cost?.end ?? 0
277 return stats
278}
279
280export function createNodeTree(rows: readonly QueryPlanRow[]): ExplainNode[] {
281 const tree = parseExplainOutput(rows)
282
283 // Parse additional details from each node
284 tree.forEach(parseNodeDetails)
285 return tree
286}
287
288export function parseDetailLines(details: string): { label: string; value: string }[] {
289 if (!details) return []
290
291 const lines = details.split('\n').filter(Boolean)
292 const result: { label: string; value: string }[] = []
293
294 for (const line of lines) {
295 const colonIndex = line.indexOf(':')
296 if (colonIndex > 0) {
297 result.push({
298 label: line.substring(0, colonIndex + 1),
299 value: line.substring(colonIndex + 1).trim(),
300 })
301 } else if (line.trim()) {
302 // Lines without colons (like table names)
303 result.push({ label: '', value: line.trim() })
304 }
305 }
306
307 return result
308}