konnosDashboard
ExplainVisualizer.parser.test.ts1142 lines · main
1import { describe, expect, test } from 'vitest'
2
3import {
4 calculateMaxCost,
5 calculateSummary,
6 parseExplainOutput,
7 parseNodeDetails,
8} from '@/components/interfaces/ExplainVisualizer/ExplainVisualizer.parser'
9import type {
10 ExplainNode,
11 QueryPlanRow,
12} from '@/components/interfaces/ExplainVisualizer/ExplainVisualizer.types'
13
14// Helper to create QueryPlanRow array from strings
15const toQueryPlanRows = (lines: string[]): QueryPlanRow[] =>
16 lines.map((line) => ({ 'QUERY PLAN': line }))
17
18describe('parseExplainOutput', () => {
19 describe('simple operations', () => {
20 test('parses a simple Seq Scan', () => {
21 const input = toQueryPlanRows(['Seq Scan on users (cost=0.00..10.50 rows=100 width=36)'])
22
23 const result = parseExplainOutput(input)
24
25 expect(result).toHaveLength(1)
26 expect(result[0].operation).toBe('Seq Scan')
27 expect(result[0].details).toBe('on users')
28 expect(result[0].cost).toEqual({ start: 0, end: 10.5 })
29 expect(result[0].rows).toBe(100)
30 expect(result[0].width).toBe(36)
31 expect(result[0].actualTime).toBeUndefined()
32 expect(result[0].actualRows).toBeUndefined()
33 expect(result[0].level).toBe(0)
34 expect(result[0].children).toHaveLength(0)
35 })
36
37 test('parses an Index Scan', () => {
38 const input = toQueryPlanRows([
39 'Index Scan using users_pkey on users (cost=0.29..8.30 rows=1 width=48)',
40 ])
41
42 const result = parseExplainOutput(input)
43
44 expect(result).toHaveLength(1)
45 // Parser keeps "using indexname" as part of operation when "on tablename" is present
46 expect(result[0].operation).toBe('Index Scan using users_pkey')
47 expect(result[0].details).toBe('on users')
48 expect(result[0].cost).toEqual({ start: 0.29, end: 8.3 })
49 expect(result[0].rows).toBe(1)
50 expect(result[0].width).toBe(48)
51 })
52
53 test('parses an Index Only Scan', () => {
54 const input = toQueryPlanRows([
55 'Index Only Scan using idx_users_email on users (cost=0.15..4.17 rows=1 width=32)',
56 ])
57
58 const result = parseExplainOutput(input)
59
60 expect(result).toHaveLength(1)
61 // Parser keeps "using indexname" as part of operation when "on tablename" is present
62 expect(result[0].operation).toBe('Index Only Scan using idx_users_email')
63 expect(result[0].details).toBe('on users')
64 })
65
66 test('parses Bitmap Index Scan and Bitmap Heap Scan', () => {
67 const input = toQueryPlanRows([
68 'Bitmap Heap Scan on users (cost=4.18..13.65 rows=3 width=36)',
69 ' -> Bitmap Index Scan on idx_users_status (cost=0.00..4.18 rows=3 width=0)',
70 ])
71
72 const result = parseExplainOutput(input)
73
74 expect(result).toHaveLength(1)
75 expect(result[0].operation).toBe('Bitmap Heap Scan')
76 expect(result[0].children).toHaveLength(1)
77 expect(result[0].children[0].operation).toBe('Bitmap Index Scan')
78 expect(result[0].children[0].details).toBe('on idx_users_status')
79 })
80 })
81
82 describe('EXPLAIN ANALYZE output', () => {
83 test('parses actual time and actual rows from EXPLAIN ANALYZE', () => {
84 const input = toQueryPlanRows([
85 'Seq Scan on users (cost=0.00..10.50 rows=100 width=36) (actual time=0.015..0.123 rows=85 loops=1)',
86 ])
87
88 const result = parseExplainOutput(input)
89
90 expect(result).toHaveLength(1)
91 expect(result[0].cost).toEqual({ start: 0, end: 10.5 })
92 expect(result[0].rows).toBe(100) // estimated rows
93 expect(result[0].actualTime).toEqual({ start: 0.015, end: 0.123 })
94 expect(result[0].actualRows).toBe(85) // actual rows
95 })
96
97 test('skips Planning Time and Execution Time lines', () => {
98 const input = toQueryPlanRows([
99 'Seq Scan on users (cost=0.00..10.50 rows=100 width=36) (actual time=0.015..0.123 rows=85 loops=1)',
100 'Planning Time: 0.089 ms',
101 'Execution Time: 0.156 ms',
102 ])
103
104 const result = parseExplainOutput(input)
105
106 expect(result).toHaveLength(1)
107 expect(result[0].operation).toBe('Seq Scan')
108 })
109 })
110
111 describe('nested operations', () => {
112 test('parses nested Hash Join with children', () => {
113 const input = toQueryPlanRows([
114 'Hash Join (cost=10.50..25.30 rows=50 width=72)',
115 ' Hash Cond: (orders.user_id = users.id)',
116 ' -> Seq Scan on orders (cost=0.00..12.00 rows=200 width=36)',
117 ' -> Hash (cost=10.50..10.50 rows=100 width=36)',
118 ' -> Seq Scan on users (cost=0.00..10.50 rows=100 width=36)',
119 ])
120
121 const result = parseExplainOutput(input)
122
123 expect(result).toHaveLength(1)
124 expect(result[0].operation).toBe('Hash Join')
125 expect(result[0].details).toContain('Hash Cond:')
126 expect(result[0].children).toHaveLength(2)
127 expect(result[0].children[0].operation).toBe('Seq Scan')
128 expect(result[0].children[0].details).toBe('on orders')
129 expect(result[0].children[1].operation).toBe('Hash')
130 expect(result[0].children[1].children).toHaveLength(1)
131 expect(result[0].children[1].children[0].operation).toBe('Seq Scan')
132 })
133
134 test('parses Merge Join', () => {
135 const input = toQueryPlanRows([
136 'Merge Join (cost=200.00..350.00 rows=1000 width=80)',
137 ' Merge Cond: (a.id = b.a_id)',
138 ' -> Index Scan using a_pkey on a (cost=0.29..50.00 rows=500 width=40)',
139 ' -> Sort (cost=150.00..155.00 rows=2000 width=40)',
140 ' Sort Key: b.a_id',
141 ' -> Seq Scan on b (cost=0.00..30.00 rows=2000 width=40)',
142 ])
143
144 const result = parseExplainOutput(input)
145
146 expect(result).toHaveLength(1)
147 expect(result[0].operation).toBe('Merge Join')
148 expect(result[0].children).toHaveLength(2)
149 expect(result[0].children[1].operation).toBe('Sort')
150 expect(result[0].children[1].details).toContain('Sort Key:')
151 })
152
153 test('parses Nested Loop', () => {
154 const input = toQueryPlanRows([
155 'Nested Loop (cost=0.29..16.60 rows=1 width=72)',
156 ' -> Index Scan using users_pkey on users (cost=0.29..8.30 rows=1 width=36)',
157 ' -> Index Scan using orders_user_id_idx on orders (cost=0.00..8.28 rows=1 width=36)',
158 ' Index Cond: (user_id = users.id)',
159 ])
160
161 const result = parseExplainOutput(input)
162
163 expect(result).toHaveLength(1)
164 expect(result[0].operation).toBe('Nested Loop')
165 expect(result[0].children).toHaveLength(2)
166 expect(result[0].children[1].details).toContain('Index Cond:')
167 })
168 })
169
170 describe('aggregate operations', () => {
171 test('parses Aggregate operation', () => {
172 const input = toQueryPlanRows([
173 'Aggregate (cost=12.50..12.51 rows=1 width=8)',
174 ' -> Seq Scan on users (cost=0.00..10.50 rows=100 width=0)',
175 ])
176
177 const result = parseExplainOutput(input)
178
179 expect(result).toHaveLength(1)
180 expect(result[0].operation).toBe('Aggregate')
181 expect(result[0].children).toHaveLength(1)
182 })
183
184 test('parses HashAggregate with Group Key', () => {
185 const input = toQueryPlanRows([
186 'HashAggregate (cost=15.00..17.00 rows=10 width=12)',
187 ' Group Key: status',
188 ' -> Seq Scan on orders (cost=0.00..12.00 rows=200 width=4)',
189 ])
190
191 const result = parseExplainOutput(input)
192
193 expect(result).toHaveLength(1)
194 expect(result[0].operation).toBe('HashAggregate')
195 expect(result[0].details).toContain('Group Key: status')
196 expect(result[0].children).toHaveLength(1)
197 })
198
199 test('parses GroupAggregate with Sort', () => {
200 const input = toQueryPlanRows([
201 'GroupAggregate (cost=20.00..25.00 rows=10 width=12)',
202 ' Group Key: category',
203 ' -> Sort (cost=18.00..19.00 rows=200 width=8)',
204 ' Sort Key: category',
205 ' -> Seq Scan on products (cost=0.00..15.00 rows=200 width=8)',
206 ])
207
208 const result = parseExplainOutput(input)
209
210 expect(result).toHaveLength(1)
211 expect(result[0].operation).toBe('GroupAggregate')
212 expect(result[0].children).toHaveLength(1)
213 expect(result[0].children[0].operation).toBe('Sort')
214 expect(result[0].children[0].children).toHaveLength(1)
215 })
216 })
217
218 describe('sorting and limiting', () => {
219 test('parses Sort operation with Sort Key', () => {
220 const input = toQueryPlanRows([
221 'Sort (cost=25.00..27.50 rows=100 width=36)',
222 ' Sort Key: created_at DESC',
223 ' -> Seq Scan on events (cost=0.00..20.00 rows=100 width=36)',
224 ])
225
226 const result = parseExplainOutput(input)
227
228 expect(result).toHaveLength(1)
229 expect(result[0].operation).toBe('Sort')
230 expect(result[0].details).toContain('Sort Key: created_at DESC')
231 expect(result[0].children).toHaveLength(1)
232 })
233
234 test('parses Sort with Sort Method in EXPLAIN ANALYZE', () => {
235 const input = toQueryPlanRows([
236 'Sort (cost=25.00..27.50 rows=100 width=36) (actual time=0.100..0.150 rows=100 loops=1)',
237 ' Sort Key: created_at DESC',
238 ' Sort Method: quicksort Memory: 32kB',
239 ' -> Seq Scan on events (cost=0.00..20.00 rows=100 width=36) (actual time=0.010..0.050 rows=100 loops=1)',
240 ])
241
242 const result = parseExplainOutput(input)
243
244 expect(result).toHaveLength(1)
245 expect(result[0].details).toContain('Sort Key: created_at DESC')
246 expect(result[0].details).toContain('Sort Method: quicksort Memory: 32kB')
247 })
248
249 test('parses Limit operation', () => {
250 const input = toQueryPlanRows([
251 'Limit (cost=0.00..1.05 rows=10 width=36)',
252 ' -> Seq Scan on users (cost=0.00..10.50 rows=100 width=36)',
253 ])
254
255 const result = parseExplainOutput(input)
256
257 expect(result).toHaveLength(1)
258 expect(result[0].operation).toBe('Limit')
259 expect(result[0].children).toHaveLength(1)
260 })
261 })
262
263 describe('filter conditions', () => {
264 test('parses Filter detail', () => {
265 const input = toQueryPlanRows([
266 'Seq Scan on users (cost=0.00..10.50 rows=50 width=36)',
267 " Filter: (status = 'active'::text)",
268 ])
269
270 const result = parseExplainOutput(input)
271
272 expect(result).toHaveLength(1)
273 expect(result[0].details).toContain("Filter: (status = 'active'::text)")
274 })
275
276 test('parses Rows Removed by Filter', () => {
277 const input = toQueryPlanRows([
278 'Seq Scan on users (cost=0.00..10.50 rows=50 width=36) (actual time=0.010..0.100 rows=50 loops=1)',
279 " Filter: (status = 'active'::text)",
280 ' Rows Removed by Filter: 50',
281 ])
282
283 const result = parseExplainOutput(input)
284 parseNodeDetails(result[0])
285
286 expect(result[0].details).toContain('Rows Removed by Filter: 50')
287 expect(result[0].rowsRemovedByFilter).toBe(50)
288 })
289
290 test('parses Index Cond', () => {
291 const input = toQueryPlanRows([
292 'Index Scan using users_pkey on users (cost=0.29..8.30 rows=1 width=48)',
293 ' Index Cond: (id = 123)',
294 ])
295
296 const result = parseExplainOutput(input)
297
298 expect(result).toHaveLength(1)
299 expect(result[0].details).toContain('Index Cond: (id = 123)')
300 })
301
302 test('parses Recheck Cond for Bitmap scans', () => {
303 const input = toQueryPlanRows([
304 'Bitmap Heap Scan on users (cost=4.18..13.65 rows=3 width=36)',
305 " Recheck Cond: (status = 'active'::text)",
306 ' -> Bitmap Index Scan on idx_users_status (cost=0.00..4.18 rows=3 width=0)',
307 " Index Cond: (status = 'active'::text)",
308 ])
309
310 const result = parseExplainOutput(input)
311
312 expect(result[0].details).toContain("Recheck Cond: (status = 'active'::text)")
313 expect(result[0].children[0].details).toContain("Index Cond: (status = 'active'::text)")
314 })
315 })
316
317 describe('subplans and CTEs', () => {
318 test('parses CTE Scan', () => {
319 const input = toQueryPlanRows([
320 'CTE Scan on recent_users (cost=10.50..12.50 rows=100 width=36)',
321 ])
322
323 const result = parseExplainOutput(input)
324
325 expect(result).toHaveLength(1)
326 expect(result[0].operation).toBe('CTE Scan')
327 expect(result[0].details).toBe('on recent_users')
328 })
329
330 test('parses SubPlan reference', () => {
331 const input = toQueryPlanRows([
332 'Seq Scan on orders (cost=0.00..25.00 rows=100 width=36)',
333 ' Filter: (total > (SubPlan 1))',
334 ' SubPlan 1',
335 ' -> Aggregate (cost=10.50..10.51 rows=1 width=8)',
336 ' -> Seq Scan on orders orders_1 (cost=0.00..10.50 rows=100 width=4)',
337 ])
338
339 const result = parseExplainOutput(input)
340
341 expect(result).toHaveLength(1)
342 expect(result[0].details).toContain('Filter: (total > (SubPlan 1))')
343 expect(result[0].details).toContain('SubPlan 1')
344 })
345
346 test('parses InitPlan', () => {
347 const input = toQueryPlanRows([
348 'Result (cost=10.51..10.52 rows=1 width=8)',
349 ' InitPlan 1 (returns $0)',
350 ' -> Aggregate (cost=10.50..10.51 rows=1 width=8)',
351 ' -> Seq Scan on users (cost=0.00..10.50 rows=100 width=0)',
352 ])
353
354 const result = parseExplainOutput(input)
355
356 expect(result).toHaveLength(1)
357 expect(result[0].operation).toBe('Result')
358 expect(result[0].details).toContain('InitPlan 1 (returns $0)')
359 })
360 })
361
362 describe('set operations', () => {
363 test('parses Append for UNION ALL', () => {
364 const input = toQueryPlanRows([
365 'Append (cost=0.00..21.00 rows=200 width=36)',
366 ' -> Seq Scan on users_2023 (cost=0.00..10.50 rows=100 width=36)',
367 ' -> Seq Scan on users_2024 (cost=0.00..10.50 rows=100 width=36)',
368 ])
369
370 const result = parseExplainOutput(input)
371
372 expect(result).toHaveLength(1)
373 expect(result[0].operation).toBe('Append')
374 expect(result[0].children).toHaveLength(2)
375 })
376
377 test('parses Unique for UNION (distinct)', () => {
378 const input = toQueryPlanRows([
379 'Unique (cost=25.00..30.00 rows=150 width=36)',
380 ' -> Sort (cost=25.00..26.00 rows=200 width=36)',
381 ' Sort Key: id',
382 ' -> Append (cost=0.00..21.00 rows=200 width=36)',
383 ' -> Seq Scan on users_2023 (cost=0.00..10.50 rows=100 width=36)',
384 ' -> Seq Scan on users_2024 (cost=0.00..10.50 rows=100 width=36)',
385 ])
386
387 const result = parseExplainOutput(input)
388
389 expect(result).toHaveLength(1)
390 expect(result[0].operation).toBe('Unique')
391 expect(result[0].children).toHaveLength(1)
392 expect(result[0].children[0].operation).toBe('Sort')
393 })
394 })
395
396 describe('parallel queries', () => {
397 test('parses Gather with parallel workers', () => {
398 const input = toQueryPlanRows([
399 'Gather (cost=1000.00..15000.00 rows=100000 width=36)',
400 ' Workers Planned: 2',
401 ' -> Parallel Seq Scan on large_table (cost=0.00..14000.00 rows=41667 width=36)',
402 " Filter: (status = 'active'::text)",
403 ])
404
405 const result = parseExplainOutput(input)
406
407 expect(result).toHaveLength(1)
408 expect(result[0].operation).toBe('Gather')
409 expect(result[0].children).toHaveLength(1)
410 expect(result[0].children[0].operation).toBe('Parallel Seq Scan')
411 })
412
413 test('parses Gather Merge', () => {
414 const input = toQueryPlanRows([
415 'Gather Merge (cost=5000.00..10000.00 rows=50000 width=36)',
416 ' Workers Planned: 2',
417 ' -> Sort (cost=4000.00..4125.00 rows=25000 width=36)',
418 ' Sort Key: created_at DESC',
419 ' -> Parallel Seq Scan on events (cost=0.00..3000.00 rows=25000 width=36)',
420 ])
421
422 const result = parseExplainOutput(input)
423
424 expect(result).toHaveLength(1)
425 expect(result[0].operation).toBe('Gather Merge')
426 })
427 })
428
429 describe('buffer information', () => {
430 test('parses Buffers information in EXPLAIN (ANALYZE, BUFFERS)', () => {
431 const input = toQueryPlanRows([
432 'Seq Scan on users (cost=0.00..10.50 rows=100 width=36) (actual time=0.010..0.100 rows=100 loops=1)',
433 ' Buffers: shared hit=5',
434 ])
435
436 const result = parseExplainOutput(input)
437
438 expect(result).toHaveLength(1)
439 expect(result[0].details).toContain('Buffers: shared hit=5')
440 })
441 })
442
443 describe('edge cases', () => {
444 test('handles empty input', () => {
445 const result = parseExplainOutput([])
446 expect(result).toHaveLength(0)
447 })
448
449 test('handles input with only empty strings', () => {
450 const input = toQueryPlanRows(['', ' ', ''])
451 const result = parseExplainOutput(input)
452 expect(result).toHaveLength(0)
453 })
454
455 test('handles malformed metric strings gracefully', () => {
456 const input = toQueryPlanRows(['Seq Scan on users (malformed metrics)'])
457 const result = parseExplainOutput(input)
458
459 expect(result).toHaveLength(1)
460 expect(result[0].operation).toBe('Seq Scan')
461 expect(result[0].cost).toBeUndefined()
462 })
463
464 test('handles operation without metrics', () => {
465 const input = toQueryPlanRows(['Seq Scan on users'])
466 const result = parseExplainOutput(input)
467
468 expect(result).toHaveLength(1)
469 expect(result[0].operation).toBe('Seq Scan')
470 expect(result[0].details).toBe('on users')
471 expect(result[0].cost).toBeUndefined()
472 })
473
474 test('handles deeply nested query plans', () => {
475 const input = toQueryPlanRows([
476 'Limit (cost=100.00..100.10 rows=10 width=36)',
477 ' -> Sort (cost=100.00..102.50 rows=1000 width=36)',
478 ' Sort Key: total DESC',
479 ' -> Hash Join (cost=50.00..80.00 rows=1000 width=36)',
480 ' Hash Cond: (o.user_id = u.id)',
481 ' -> Seq Scan on orders o (cost=0.00..20.00 rows=1000 width=20)',
482 ' -> Hash (cost=40.00..40.00 rows=500 width=16)',
483 ' -> Seq Scan on users u (cost=0.00..40.00 rows=500 width=16)',
484 ' Filter: (active = true)',
485 ])
486
487 const result = parseExplainOutput(input)
488
489 expect(result).toHaveLength(1)
490 expect(result[0].operation).toBe('Limit')
491 expect(result[0].children[0].operation).toBe('Sort')
492 expect(result[0].children[0].children[0].operation).toBe('Hash Join')
493 expect(result[0].children[0].children[0].children).toHaveLength(2)
494 expect(result[0].children[0].children[0].children[1].children[0].operation).toBe('Seq Scan')
495 })
496
497 test('handles One-Time Filter', () => {
498 const input = toQueryPlanRows([
499 'Result (cost=0.00..0.01 rows=1 width=0)',
500 ' One-Time Filter: false',
501 ])
502
503 const result = parseExplainOutput(input)
504
505 expect(result).toHaveLength(1)
506 expect(result[0].details).toContain('One-Time Filter: false')
507 })
508
509 test('handles Output detail line', () => {
510 const input = toQueryPlanRows([
511 'Seq Scan on users (cost=0.00..10.50 rows=100 width=36)',
512 ' Output: id, name, email',
513 ])
514
515 const result = parseExplainOutput(input)
516
517 expect(result).toHaveLength(1)
518 expect(result[0].details).toContain('Output: id, name, email')
519 })
520
521 test('handles invalid cost values gracefully', () => {
522 const input = toQueryPlanRows([
523 'Seq Scan on users (cost=invalid..notanumber rows=100 width=36)',
524 ])
525
526 const result = parseExplainOutput(input)
527
528 expect(result).toHaveLength(1)
529 expect(result[0].operation).toBe('Seq Scan')
530 // Should not parse invalid cost at all
531 expect(result[0].cost).toBeUndefined()
532 })
533
534 test('handles invalid rows value gracefully', () => {
535 const input = toQueryPlanRows([
536 'Seq Scan on users (cost=0.00..10.50 rows=notanumber width=36)',
537 ])
538
539 const result = parseExplainOutput(input)
540
541 expect(result).toHaveLength(1)
542 expect(result[0].operation).toBe('Seq Scan')
543 // Should not parse invalid rows at all (regex won't match)
544 expect(result[0].rows).toBeUndefined()
545 })
546
547 test('handles invalid actual time values gracefully', () => {
548 const input = toQueryPlanRows([
549 'Seq Scan on users (cost=0.00..10.50 rows=100 width=36) (actual time=invalid..notanumber rows=85 loops=1)',
550 ])
551
552 const result = parseExplainOutput(input)
553
554 expect(result).toHaveLength(1)
555 // Should not parse invalid actual time at all
556 expect(result[0].actualTime).toBeUndefined()
557 })
558
559 test('handles invalid rowsRemovedByFilter value gracefully', () => {
560 const input = toQueryPlanRows([
561 'Seq Scan on users (cost=0.00..10.50 rows=100 width=36)',
562 " Filter: (status = 'active')",
563 ' Rows Removed by Filter: notanumber',
564 ])
565
566 const result = parseExplainOutput(input)
567 parseNodeDetails(result[0])
568
569 expect(result).toHaveLength(1)
570 // Should not parse invalid value at all (regex won't match)
571 expect(result[0].rowsRemovedByFilter).toBeUndefined()
572 })
573
574 test('parsed numeric fields are finite for valid input', () => {
575 const input = toQueryPlanRows([
576 'Seq Scan on users (cost=0.00..10.50 rows=100 width=36) (actual time=0.015..0.123 rows=85 loops=1)',
577 " Filter: (status = 'active')",
578 ' Rows Removed by Filter: 15',
579 ])
580
581 const result = parseExplainOutput(input)
582 parseNodeDetails(result[0])
583
584 expect(result).toHaveLength(1)
585 const node = result[0]
586
587 // Verify all numeric values are finite
588 if (node.cost) {
589 expect(Number.isFinite(node.cost.start)).toBe(true)
590 expect(Number.isFinite(node.cost.end)).toBe(true)
591 }
592 if (node.actualTime) {
593 expect(Number.isFinite(node.actualTime.start)).toBe(true)
594 expect(Number.isFinite(node.actualTime.end)).toBe(true)
595 }
596 if (node.rows !== undefined) {
597 expect(Number.isFinite(node.rows)).toBe(true)
598 }
599 if (node.actualRows !== undefined) {
600 expect(Number.isFinite(node.actualRows)).toBe(true)
601 }
602 if (node.width !== undefined) {
603 expect(Number.isFinite(node.width)).toBe(true)
604 }
605 if (node.rowsRemovedByFilter !== undefined) {
606 expect(Number.isFinite(node.rowsRemovedByFilter)).toBe(true)
607 }
608 })
609 })
610
611 describe('complex real-world queries', () => {
612 test('parses a complex analytical query', () => {
613 const input = toQueryPlanRows([
614 'Limit (cost=1500.00..1500.05 rows=20 width=48) (actual time=15.234..15.240 rows=20 loops=1)',
615 ' -> Sort (cost=1500.00..1525.00 rows=10000 width=48) (actual time=15.232..15.235 rows=20 loops=1)',
616 ' Sort Key: (sum(o.total)) DESC',
617 ' Sort Method: top-N heapsort Memory: 27kB',
618 ' -> HashAggregate (cost=1200.00..1300.00 rows=10000 width=48) (actual time=12.456..14.789 rows=8543 loops=1)',
619 ' Group Key: u.id',
620 ' Batches: 1 Memory Usage: 1169kB',
621 ' -> Hash Join (cost=125.00..950.00 rows=50000 width=20) (actual time=1.234..8.567 rows=50000 loops=1)',
622 ' Hash Cond: (o.user_id = u.id)',
623 ' -> Seq Scan on orders o (cost=0.00..750.00 rows=50000 width=12) (actual time=0.012..3.456 rows=50000 loops=1)',
624 ' -> Hash (cost=100.00..100.00 rows=2000 width=8) (actual time=1.111..1.111 rows=2000 loops=1)',
625 ' Buckets: 2048 Batches: 1 Memory Usage: 95kB',
626 ' -> Seq Scan on users u (cost=0.00..100.00 rows=2000 width=8) (actual time=0.008..0.567 rows=2000 loops=1)',
627 ' Filter: (active = true)',
628 ' Rows Removed by Filter: 500',
629 ])
630
631 const result = parseExplainOutput(input)
632
633 expect(result).toHaveLength(1)
634 expect(result[0].operation).toBe('Limit')
635 expect(result[0].actualTime).toEqual({ start: 15.234, end: 15.24 })
636 expect(result[0].actualRows).toBe(20)
637
638 // Navigate to the deepest Seq Scan on users
639 const hashJoin = result[0].children[0].children[0].children[0]
640 expect(hashJoin.operation).toBe('Hash Join')
641 const hash = hashJoin.children[1]
642 expect(hash.operation).toBe('Hash')
643 const usersScan = hash.children[0]
644 expect(usersScan.operation).toBe('Seq Scan')
645 expect(usersScan.details).toContain('Filter: (active = true)')
646 expect(usersScan.details).toContain('Rows Removed by Filter: 500')
647 })
648 })
649})
650
651describe('parseNodeDetails', () => {
652 test('parses Rows Removed by Filter from node details', () => {
653 const node: ExplainNode = {
654 operation: 'Seq Scan',
655 details: "on users\nFilter: (status = 'active')\nRows Removed by Filter: 150",
656 cost: { start: 0, end: 10.5 },
657 rows: 100,
658 width: 36,
659
660 level: 0,
661 children: [],
662 raw: '',
663 }
664
665 parseNodeDetails(node)
666
667 expect(node.rowsRemovedByFilter).toBe(150)
668 })
669
670 test('handles node without Rows Removed by Filter', () => {
671 const node: ExplainNode = {
672 operation: 'Seq Scan',
673 details: 'on users',
674 cost: { start: 0, end: 10.5 },
675 rows: 100,
676 width: 36,
677 level: 0,
678 children: [],
679 raw: '',
680 }
681
682 parseNodeDetails(node)
683
684 expect(node.rowsRemovedByFilter).toBeUndefined()
685 })
686
687 test('recursively parses details for children', () => {
688 const node: ExplainNode = {
689 operation: 'Hash Join',
690 details: '',
691 cost: { start: 0, end: 50 },
692 rows: 100,
693 width: 72,
694 level: 0,
695 children: [
696 {
697 operation: 'Seq Scan',
698 details: 'on orders\nRows Removed by Filter: 200',
699 cost: { start: 0, end: 20 },
700 rows: 50,
701 width: 36,
702
703 level: 1,
704 children: [],
705 raw: '',
706 },
707 {
708 operation: 'Seq Scan',
709 details: 'on users\nRows Removed by Filter: 75',
710 cost: { start: 0, end: 15 },
711 rows: 25,
712 width: 36,
713
714 level: 1,
715 children: [],
716 raw: '',
717 },
718 ],
719 raw: '',
720 }
721
722 parseNodeDetails(node)
723
724 expect(node.children[0].rowsRemovedByFilter).toBe(200)
725 expect(node.children[1].rowsRemovedByFilter).toBe(75)
726 })
727})
728
729describe('calculateMaxCost', () => {
730 test('returns 0 for empty tree', () => {
731 const result = calculateMaxCost([])
732 expect(result).toBe(0)
733 })
734
735 test('returns cost.end for single node', () => {
736 const tree: ExplainNode[] = [
737 {
738 operation: 'Seq Scan',
739 details: 'on users',
740 cost: { start: 0, end: 25.5 },
741 rows: 100,
742 width: 36,
743 level: 0,
744 children: [],
745 raw: '',
746 },
747 ]
748
749 const result = calculateMaxCost(tree)
750 expect(result).toBe(25.5)
751 })
752
753 test('prefers cost.end over actualTime.end (uses actualTime as fallback)', () => {
754 // When both cost and actualTime are present, cost takes precedence
755 const treeWithBoth: ExplainNode[] = [
756 {
757 operation: 'Seq Scan',
758 details: 'on users',
759 cost: { start: 0, end: 10.5 },
760 rows: 100,
761 width: 36,
762 actualTime: { start: 0.01, end: 50.123 },
763 actualRows: 100,
764 level: 0,
765 children: [],
766 raw: '',
767 },
768 ]
769
770 expect(calculateMaxCost(treeWithBoth)).toBe(10.5)
771
772 // When only actualTime is present, it's used as fallback
773 const treeOnlyActualTime: ExplainNode[] = [
774 {
775 operation: 'Seq Scan',
776 details: 'on users',
777
778 rows: 100,
779 width: 36,
780 actualTime: { start: 0.01, end: 50.123 },
781 actualRows: 100,
782 level: 0,
783 children: [],
784 raw: '',
785 },
786 ]
787
788 expect(calculateMaxCost(treeOnlyActualTime)).toBe(50.123)
789 })
790
791 test('finds maximum across nested children', () => {
792 const tree: ExplainNode[] = [
793 {
794 operation: 'Limit',
795 details: '',
796 cost: { start: 0, end: 100 },
797 rows: 10,
798 width: 36,
799 level: 0,
800 children: [
801 {
802 operation: 'Sort',
803 details: '',
804 cost: { start: 0, end: 250 }, // This is the maximum
805 rows: 1000,
806 width: 36,
807 level: 1,
808 children: [
809 {
810 operation: 'Seq Scan',
811 details: 'on users',
812 cost: { start: 0, end: 150 },
813 rows: 1000,
814 width: 36,
815 level: 2,
816 children: [],
817 raw: '',
818 },
819 ],
820 raw: '',
821 },
822 ],
823 raw: '',
824 },
825 ]
826
827 const result = calculateMaxCost(tree)
828 expect(result).toBe(250)
829 })
830
831 test('handles multiple root nodes', () => {
832 const tree: ExplainNode[] = [
833 {
834 operation: 'Seq Scan',
835 details: 'on users',
836 cost: { start: 0, end: 30 },
837 rows: 100,
838 width: 36,
839 level: 0,
840 children: [],
841 raw: '',
842 },
843 {
844 operation: 'Seq Scan',
845 details: 'on orders',
846 cost: { start: 0, end: 75 },
847 rows: 200,
848 width: 36,
849 level: 0,
850 children: [],
851 raw: '',
852 },
853 ]
854
855 const result = calculateMaxCost(tree)
856 expect(result).toBe(75)
857 })
858
859 test('handles nodes without cost or actualTime', () => {
860 const tree: ExplainNode[] = [
861 {
862 operation: 'Result',
863 details: '',
864 level: 0,
865 children: [],
866 raw: '',
867 },
868 ]
869
870 const result = calculateMaxCost(tree)
871 expect(result).toBe(0)
872 })
873})
874
875describe('calculateSummary', () => {
876 test('returns default values for empty tree', () => {
877 const result = calculateSummary([])
878
879 expect(result).toEqual({
880 totalTime: 0,
881 totalCost: 0,
882 maxCost: 0,
883 hasSeqScan: false,
884 seqScanTables: [],
885 hasIndexScan: false,
886 })
887 })
888
889 test('calculates totalCost from root node cost.end', () => {
890 const tree: ExplainNode[] = [
891 {
892 operation: 'Seq Scan',
893 details: 'on users',
894 cost: { start: 0, end: 45.5 },
895 rows: 100,
896 width: 36,
897 level: 0,
898 children: [],
899 raw: '',
900 },
901 ]
902
903 const result = calculateSummary(tree)
904 expect(result.totalCost).toBe(45.5)
905 })
906
907 test('calculates maxCost from maximum cost across all nodes', () => {
908 const tree: ExplainNode[] = [
909 {
910 operation: 'Limit',
911 details: '',
912 cost: { start: 0, end: 100 },
913 rows: 10,
914 width: 36,
915 level: 0,
916 children: [
917 {
918 operation: 'Sort',
919 details: '',
920 cost: { start: 0, end: 250 }, // This is the maximum
921 rows: 1000,
922 width: 36,
923 level: 1,
924 children: [
925 {
926 operation: 'Seq Scan',
927 details: 'on users',
928 cost: { start: 0, end: 150 },
929 rows: 1000,
930 width: 36,
931 level: 2,
932 children: [],
933 raw: '',
934 },
935 ],
936 raw: '',
937 },
938 ],
939 raw: '',
940 },
941 ]
942
943 const result = calculateSummary(tree)
944 expect(result.totalCost).toBe(100) // Root node cost
945 expect(result.maxCost).toBe(250) // Maximum across all nodes
946 })
947
948 test('calculates totalTime from actualTime.end', () => {
949 const tree: ExplainNode[] = [
950 {
951 operation: 'Seq Scan',
952 details: 'on users',
953 cost: { start: 0, end: 10.5 },
954 rows: 100,
955 width: 36,
956 actualTime: { start: 0.01, end: 123.456 },
957 actualRows: 100,
958 level: 0,
959 children: [],
960 raw: '',
961 },
962 ]
963
964 const result = calculateSummary(tree)
965 expect(result.totalTime).toBe(123.456)
966 })
967
968 test('detects Seq Scan and extracts table name', () => {
969 const tree: ExplainNode[] = [
970 {
971 operation: 'Seq Scan',
972 details: 'on users',
973 cost: { start: 0, end: 10.5 },
974 rows: 100,
975 width: 36,
976 level: 0,
977 children: [],
978 raw: '',
979 },
980 ]
981
982 const result = calculateSummary(tree)
983 expect(result.hasSeqScan).toBe(true)
984 expect(result.seqScanTables).toEqual(['users'])
985 })
986
987 test('detects multiple Seq Scans on different tables', () => {
988 const tree: ExplainNode[] = [
989 {
990 operation: 'Hash Join',
991 details: '',
992 cost: { start: 0, end: 50 },
993 rows: 100,
994 width: 72,
995 level: 0,
996 children: [
997 {
998 operation: 'Seq Scan',
999 details: 'on orders',
1000 cost: { start: 0, end: 20 },
1001 rows: 100,
1002 width: 36,
1003 level: 1,
1004 children: [],
1005 raw: '',
1006 },
1007 {
1008 operation: 'Seq Scan',
1009 details: 'on users',
1010 cost: { start: 0, end: 15 },
1011 rows: 50,
1012 width: 36,
1013 level: 1,
1014 children: [],
1015 raw: '',
1016 },
1017 ],
1018 raw: '',
1019 },
1020 ]
1021
1022 const result = calculateSummary(tree)
1023 expect(result.hasSeqScan).toBe(true)
1024 expect(result.seqScanTables).toEqual(['orders', 'users'])
1025 })
1026
1027 test('detects Index Scan', () => {
1028 const tree: ExplainNode[] = [
1029 {
1030 operation: 'Index Scan using users_pkey',
1031 details: 'on users',
1032 cost: { start: 0.29, end: 8.3 },
1033 rows: 1,
1034 width: 48,
1035 level: 0,
1036 children: [],
1037 raw: '',
1038 },
1039 ]
1040
1041 const result = calculateSummary(tree)
1042 expect(result.hasIndexScan).toBe(true)
1043 expect(result.hasSeqScan).toBe(false)
1044 })
1045
1046 test('detects Index Only Scan', () => {
1047 const tree: ExplainNode[] = [
1048 {
1049 operation: 'Index Only Scan',
1050 details: 'using idx_users_email on users',
1051 cost: { start: 0.15, end: 4.17 },
1052 rows: 1,
1053 width: 32,
1054 level: 0,
1055 children: [],
1056 raw: '',
1057 },
1058 ]
1059
1060 const result = calculateSummary(tree)
1061 expect(result.hasIndexScan).toBe(true)
1062 })
1063
1064 test('detects Bitmap Index Scan', () => {
1065 const tree: ExplainNode[] = [
1066 {
1067 operation: 'Bitmap Heap Scan',
1068 details: 'on users',
1069 cost: { start: 4.18, end: 13.65 },
1070 rows: 3,
1071 width: 36,
1072 level: 0,
1073 children: [
1074 {
1075 operation: 'Bitmap Index Scan',
1076 details: 'on idx_users_status',
1077 cost: { start: 0, end: 4.18 },
1078 rows: 3,
1079 width: 0,
1080 level: 1,
1081 children: [],
1082 raw: '',
1083 },
1084 ],
1085 raw: '',
1086 },
1087 ]
1088
1089 const result = calculateSummary(tree)
1090 expect(result.hasIndexScan).toBe(true)
1091 })
1092
1093 test('handles complex query with both seq and index scans', () => {
1094 const tree: ExplainNode[] = [
1095 {
1096 operation: 'Hash Join',
1097 details: '',
1098 cost: { start: 10.5, end: 35.8 },
1099 rows: 50,
1100 width: 72,
1101 actualTime: { start: 0.5, end: 2.345 },
1102 actualRows: 48,
1103 level: 0,
1104 children: [
1105 {
1106 operation: 'Seq Scan',
1107 details: 'on orders',
1108 cost: { start: 0, end: 20 },
1109 rows: 100,
1110 width: 36,
1111 actualTime: { start: 0.01, end: 0.5 },
1112 actualRows: 95,
1113 level: 1,
1114 children: [],
1115 raw: '',
1116 },
1117 {
1118 operation: 'Index Scan using users_pkey',
1119 details: 'on users',
1120 cost: { start: 0.29, end: 8.3 },
1121 rows: 1,
1122 width: 36,
1123 actualTime: { start: 0.005, end: 0.015 },
1124 actualRows: 1,
1125 level: 1,
1126 children: [],
1127 raw: '',
1128 },
1129 ],
1130 raw: '',
1131 },
1132 ]
1133
1134 const result = calculateSummary(tree)
1135 expect(result.totalCost).toBe(35.8) // Root node cost
1136 expect(result.maxCost).toBe(35.8) // Maximum cost across all nodes (root is highest)
1137 expect(result.totalTime).toBe(2.345)
1138 expect(result.hasSeqScan).toBe(true)
1139 expect(result.hasIndexScan).toBe(true)
1140 expect(result.seqScanTables).toEqual(['orders'])
1141 })
1142})