konnosDashboard
QueryPerformance.ai.ts140 lines · main
1import { QueryPerformanceRow } from './QueryPerformance.types'
2import type { QueryPlanRow } from '@/components/interfaces/ExplainVisualizer/ExplainVisualizer.types'
3import type { ClassifiedQuery } from '@/components/interfaces/QueryInsights/QueryInsightsHealth/QueryInsightsHealth.types'
4import {
5 getColumnName,
6 getTableName,
7} from '@/components/interfaces/QueryInsights/QueryInsightsTable/QueryInsightsTable.utils'
8
9export interface QueryExplanationPrompt {
10 query: string
11 prompt: string
12}
13
14export function buildQueryExplanationPrompt(
15 selectedRow: QueryPerformanceRow
16): QueryExplanationPrompt {
17 const metadata = [
18 `Total Time: ${selectedRow.total_time.toLocaleString()}`,
19 `Calls: ${selectedRow.calls.toLocaleString()}`,
20 `Mean Time: ${selectedRow.mean_time.toLocaleString()}`,
21 `Max Time: ${selectedRow.max_time.toLocaleString()}`,
22 `Min Time: ${selectedRow.min_time.toLocaleString()}`,
23 `Rows Read: ${selectedRow.rows_read.toLocaleString()}`,
24 `Cache Hit Rate: ${selectedRow.cache_hit_rate.toLocaleString()}%`,
25 `Role: ${selectedRow.rolname}`,
26 ].join('\n')
27
28 let additionalContext = ''
29 if (selectedRow.index_advisor_result) {
30 const indexResult = selectedRow.index_advisor_result
31
32 if (indexResult.index_statements.length > 0) {
33 additionalContext = `\n\nIndex Advisor Recommendations:\n${indexResult.index_statements.join('\n')}\n\nCost Analysis:\n- Startup Cost Before: ${indexResult.startup_cost_before}\n- Startup Cost After: ${indexResult.startup_cost_after}\n- Total Cost Before: ${indexResult.total_cost_before}\n- Total Cost After: ${indexResult.total_cost_after}`
34 }
35 }
36
37 const prompt = `Analyze this database query and provide a brief, concise explanation:
38
39**Performance Metrics:**
40${metadata}
41
42${additionalContext}
43
44Provide a short response covering:
451. What the query does (1-2 sentences)
462. Performance assessment (good/concerning and why, keep it brief 2-3 sentences)
473. Actionable optimization suggestions (if any, keep it brief 2-3 sentences)
48
49Keep your response concise and focused on actionable insights. We can continue the conversation if needed to get more details.`
50
51 return {
52 query: selectedRow.query,
53 prompt,
54 }
55}
56
57export function buildQueryInsightFixPrompt(item: ClassifiedQuery): QueryExplanationPrompt {
58 const stats = [
59 `Mean time: ${Math.round(item.mean_time).toLocaleString()}ms`,
60 `Total time: ${Math.round(item.total_time).toLocaleString()}ms`,
61 `Calls: ${item.calls.toLocaleString()}`,
62 ].join('\n')
63
64 const issueContext: Record<NonNullable<ClassifiedQuery['issueType']>, string> = {
65 slow: `This query is running slowly (mean ${Math.round(item.mean_time)}ms). The goal is to reduce mean execution time.`,
66 index: `This query is missing an index. The planner is doing a sequential scan where an index scan would be faster.`,
67 error: `This query has a performance error or anti-pattern: ${item.hint}`,
68 }
69
70 const context = item.issueType ? issueContext[item.issueType] : ''
71
72 const tableName = getTableName(item.query)
73 const columnName = getColumnName(item.query)
74 const queryContext = [tableName && `Table: ${tableName}`, columnName && `Column: ${columnName}`]
75 .filter(Boolean)
76 .join('\n')
77
78 const prompt = `You are a PostgreSQL performance expert. A query has been flagged in our Query Insights triage view.
79
80**Issue:** ${item.hint}
81${context}
82
83**Performance stats:**
84${stats}
85${queryContext ? `\n**Query context:**\n${queryContext}` : ''}
86
87**Full query:**
88\`\`\`sql
89${item.query}
90\`\`\`
91
92Your task is to provide a concrete fix the user can run directly in their SQL Editor.
93
94Respond with:
951. A short diagnosis (1-2 sentences max) explaining why the query is slow or problematic
962. The exact SQL to fix it — this could be a \`CREATE INDEX\` statement, a rewritten version of the query, or another SQL command
973. A brief explanation of why the fix helps and what improvement to expect
98
99Format the SQL as a runnable code block. Do not add caveats or lengthy explanations — focus on the fix.
100
101**Important:** The SQL Editor runs statements inside a transaction block. Do not use \`CREATE INDEX CONCURRENTLY\` — use plain \`CREATE INDEX\` instead.
102
103**Important:** Use table and column names exactly as they appear in the full query above, including their schema prefix (e.g. \`auth.users\`, not \`public.users\`). Do not guess or change schema names.`
104
105 return { query: item.query, prompt }
106}
107
108export function buildExplainOptimizationPrompt(
109 query: string,
110 planRows: QueryPlanRow[],
111 stats?: Pick<QueryPerformanceRow, 'mean_time' | 'calls' | 'total_time'>
112): QueryExplanationPrompt {
113 const explainText = planRows.map((r) => r['QUERY PLAN']).join('\n')
114
115 const statLines = stats
116 ? [
117 `Mean time: ${stats.mean_time.toLocaleString()}ms`,
118 `Total time: ${stats.total_time.toLocaleString()}ms`,
119 `Calls: ${stats.calls.toLocaleString()}`,
120 ].join('\n')
121 : null
122
123 const prompt = `You are a PostgreSQL performance expert. Analyze the EXPLAIN ANALYZE output below and suggest specific optimizations.
124
125${statLines ? `**Performance stats:**\n${statLines}\n` : ''}
126**EXPLAIN ANALYZE output:**
127\`\`\`
128${explainText}
129\`\`\`
130
131Focus on:
1321. The most expensive nodes (widest bars / highest actual time)
1332. Any Seq Scans on large tables that could be replaced with an Index Scan
1343. Large gaps between estimated rows and actual rows (stale statistics)
1354. Inefficient join strategies (Nested Loop on large tables)
136
137For each issue found, suggest a concrete fix (e.g. the exact \`CREATE INDEX\` statement, or \`ANALYZE table_name\`). Be concise and actionable.`
138
139 return { query, prompt }
140}