konnosDashboard
Reports.queryPerformance.test.ts116 lines · main
1import { describe, expect, it } from 'vitest'
2
3import { PRESET_CONFIG } from './Reports.constants'
4import { Presets } from './Reports.types'
5
6const queries = PRESET_CONFIG[Presets.QUERY_PERFORMANCE].queries as Record<
7 string,
8 { safeSql: (...args: any[]) => string }
9>
10
11const queryNames = [
12 'mostFrequentlyInvoked',
13 'mostTimeConsuming',
14 'slowestExecutionTime',
15 'unified',
16 'slowQueriesCount',
17 'queryMetrics',
18] as const
19
20describe('QUERY_PERFORMANCE SQL queries', () => {
21 describe('calls > 0 base filter', () => {
22 it.each(queryNames)('%s includes calls > 0 without user filters', (name) => {
23 const sql = queries[name].safeSql([], undefined, undefined)
24 expect(sql).toContain('calls > 0')
25 })
26
27 it.each(queryNames)('%s still includes calls > 0 when user filters are provided', (name) => {
28 const sql = queries[name].safeSql([], "WHERE auth.rolname in ('postgres')", undefined)
29 expect(sql).toContain('calls > 0')
30 })
31 })
32
33 describe('WHERE clause composition with user filters', () => {
34 const userWhere = "WHERE auth.rolname in ('postgres')"
35
36 it.each([
37 'mostFrequentlyInvoked',
38 'mostTimeConsuming',
39 'slowestExecutionTime',
40 'unified',
41 ] as const)('%s: user filters appended with AND (no duplicate WHERE)', (name) => {
42 const sql = queries[name].safeSql([], userWhere, undefined)
43 // Should not have two WHERE keywords in a row / duplicate WHERE
44 expect(sql).not.toMatch(/WHERE\s+.*WHERE/s)
45 // User filter condition should be present
46 expect(sql).toContain("auth.rolname in ('postgres')")
47 // Should use AND to join base filter and user filter
48 expect(sql).toMatch(/calls > 0\s+AND/)
49 })
50
51 it('queryMetrics: user filters appended with AND (no duplicate WHERE in FROM clause)', () => {
52 const sql = queries.queryMetrics.safeSql([], userWhere, undefined)
53 // queryMetrics uses COUNT(*) FILTER (WHERE ...) which is valid SQL and not a duplicate
54 // Just verify the base filter + user filter are correctly composed
55 expect(sql).toContain("auth.rolname in ('postgres')")
56 expect(sql).toMatch(/calls > 0\s+AND/)
57 // Should not have two WHERE keywords after the FROM keyword
58 expect(sql).not.toMatch(/FROM[\s\S]*WHERE[\s\S]*WHERE[\s\S]*WHERE/s)
59 })
60
61 it.each([
62 'mostFrequentlyInvoked',
63 'mostTimeConsuming',
64 'slowestExecutionTime',
65 'unified',
66 'queryMetrics',
67 ] as const)('%s: no trailing junk when no user filters', (name) => {
68 const sql = queries[name].safeSql([], undefined, undefined)
69 // Should not have a dangling undefined or 'WHERE' with nothing after the base filter
70 expect(sql).not.toContain('undefined')
71 expect(sql).not.toMatch(/calls > 0\s+AND\s+(ORDER|LIMIT|$)/im)
72 })
73 })
74
75 describe('slowQueriesCount bug fix', () => {
76 it('uses table alias "statements"', () => {
77 const sql = queries.slowQueriesCount.safeSql()
78 expect(sql).toContain('pg_stat_statements as statements')
79 })
80
81 it('filters by mean_exec_time using the alias', () => {
82 const sql = queries.slowQueriesCount.safeSql()
83 expect(sql).toContain('statements.mean_exec_time > 1000')
84 })
85 })
86
87 describe('window function elimination', () => {
88 it('unified uses grand_total CTE instead of OVER()', () => {
89 const sql = queries.unified.safeSql([], undefined, undefined)
90 expect(sql).toContain('grand_total')
91 expect(sql).not.toContain('OVER()')
92 })
93
94 it('mostTimeConsuming uses grand_total CTE instead of OVER()', () => {
95 const sql = queries.mostTimeConsuming.safeSql([], undefined, undefined)
96 expect(sql).toContain('grand_total')
97 expect(sql).not.toContain('OVER()')
98 })
99
100 it('grand_total CTE references calls > 0', () => {
101 const sql = queries.unified.safeSql([], undefined, undefined)
102 expect(sql).toMatch(/grand_total[\s\S]*calls > 0/)
103 })
104 })
105
106 describe('multiple user filters', () => {
107 it('handles multiple user filter conditions', () => {
108 const multiWhere = "WHERE auth.rolname in ('postgres') AND statements.calls >= 10"
109 const sql = queries.mostFrequentlyInvoked.safeSql([], multiWhere, undefined)
110 expect(sql).toContain('calls > 0')
111 expect(sql).toContain("auth.rolname in ('postgres')")
112 expect(sql).toContain('statements.calls >= 10')
113 expect(sql).not.toMatch(/WHERE\s+.*WHERE/s)
114 })
115 })
116})