konnosDashboard
useQueryPerformanceQuery.ts220 lines · main
1import {
2 ident,
3 joinSqlFragments,
4 keyword,
5 literal,
6 safeSql,
7 type SafeSqlFragment,
8} from '@supabase/pg-meta'
9import { useInfiniteQuery, useQueryClient } from '@tanstack/react-query'
10
11import { PRESET_CONFIG } from '../Reports/Reports.constants'
12import { Presets } from '../Reports/Reports.types'
13import {
14 QueryPerformanceRow,
15 QueryPerformanceSort,
16 QueryPerformanceSQLParams,
17} from './QueryPerformance.types'
18import { useReadReplicasQuery } from '@/data/read-replicas/replicas-query'
19import { executeSql } from '@/data/sql/execute-sql-query'
20import useDbQuery from '@/hooks/analytics/useDbQuery'
21import { useSelectedProjectQuery } from '@/hooks/misc/useSelectedProject'
22import { IS_PLATFORM } from '@/lib/constants'
23import { useDatabaseSelectorStateSnapshot } from '@/state/database-selector'
24
25const VALID_SORT_COLUMNS: ReadonlySet<string> = new Set<QueryPerformanceSort['column']>([
26 'query',
27 'rolname',
28 'total_time',
29 'prop_total_time',
30 'calls',
31 'avg_rows',
32 'max_time',
33 'mean_time',
34 'min_time',
35])
36
37export function generateQueryPerformanceSql({
38 preset,
39 orderBy,
40 searchQuery = '',
41 roles = [],
42 sources = [],
43 minCalls = 0,
44 minTotalTime = 0,
45 runIndexAdvisor = false,
46 filterIndexAdvisor = false,
47 page = 1,
48 pageSize = 20,
49}: QueryPerformanceSQLParams) {
50 const safePage = Number.isFinite(page) ? Math.max(1, Math.floor(page)) : 1
51 const safePageSize = Number.isFinite(pageSize)
52 ? Math.min(Math.max(1, Math.floor(pageSize)), 100)
53 : 20
54
55 const queryPerfQueries = PRESET_CONFIG[Presets.QUERY_PERFORMANCE]
56 const baseSQL = queryPerfQueries.queries[preset]
57
58 const isValidOrderBy =
59 orderBy != null &&
60 VALID_SORT_COLUMNS.has(orderBy.column) &&
61 (orderBy.order === 'asc' || orderBy.order === 'desc')
62
63 const orderBySql = isValidOrderBy
64 ? safeSql`ORDER BY ${ident(orderBy!.column)} ${keyword(orderBy!.order)}`
65 : undefined
66
67 const whereConditions: SafeSqlFragment[] = []
68 if (roles.length > 0) {
69 whereConditions.push(
70 safeSql`auth.rolname in (${joinSqlFragments(
71 roles.map((r) => literal(r)),
72 ', '
73 )})`
74 )
75 }
76 if (searchQuery.length > 0) {
77 whereConditions.push(safeSql`statements.query ~* ${literal(searchQuery)}`)
78 }
79 if (sources.includes('dashboard') && !sources.includes('non-dashboard')) {
80 whereConditions.push(safeSql`statements.query ~* 'source: dashboard'`)
81 }
82 if (sources.includes('non-dashboard') && !sources.includes('dashboard')) {
83 whereConditions.push(safeSql`statements.query !~* 'source: dashboard'`)
84 }
85 if (Number.isFinite(minCalls) && minCalls > 0) {
86 whereConditions.push(safeSql`statements.calls >= ${literal(minCalls)}`)
87 }
88 if (Number.isFinite(minTotalTime) && minTotalTime > 0) {
89 whereConditions.push(
90 safeSql`(statements.total_exec_time + statements.total_plan_time) >= ${literal(minTotalTime)}`
91 )
92 }
93
94 const whereSql = joinSqlFragments(whereConditions, ' AND ')
95
96 if (baseSQL.queryType !== 'db') {
97 throw new Error(
98 `Query performance presets must be db queries; got ${baseSQL.queryType} for preset ${preset}`
99 )
100 }
101
102 const sql = baseSQL.safeSql(
103 [],
104 whereSql.length > 0 ? safeSql`WHERE ${whereSql}` : undefined,
105 orderBySql,
106 runIndexAdvisor,
107 filterIndexAdvisor,
108 safePage,
109 safePageSize
110 )
111
112 return { sql, whereSql, orderBySql }
113}
114
115export const useQueryPerformanceQuery = (props: QueryPerformanceSQLParams) => {
116 const { sql, whereSql, orderBySql } = generateQueryPerformanceSql(props)
117 return useDbQuery({ sql, params: undefined, where: whereSql, orderBy: orderBySql })
118}
119
120export interface QueryPerformanceInfiniteHook {
121 data: QueryPerformanceRow[] | undefined
122 isLoading: boolean
123 isRefetching: boolean
124 isFetchingNextPage: boolean
125 hasNextPage: boolean
126 error: unknown
127 fetchNextPage: () => void
128 refetch: () => void
129 resolvedSql: string
130}
131
132export const useQueryPerformanceInfiniteQuery = (
133 props: Omit<QueryPerformanceSQLParams, 'page'>
134): QueryPerformanceInfiniteHook => {
135 const queryClient = useQueryClient()
136 const { data: project } = useSelectedProjectQuery()
137 const state = useDatabaseSelectorStateSnapshot()
138 const { data: databases } = useReadReplicasQuery({ projectRef: project?.ref })
139 const connectionString = (databases || []).find(
140 (db) => db.identifier === state.selectedDatabaseId
141 )?.connectionString
142
143 // Clamp pageSize the same way generateQueryPerformanceSql does so getNextPageParam
144 // and the queryKey are always consistent with the SQL actually executed.
145 const rawPageSize = props.pageSize
146 const safePageSize = Number.isFinite(rawPageSize)
147 ? Math.min(Math.max(1, Math.floor(rawPageSize!)), 100)
148 : 20
149 const { sql: page1Sql } = generateQueryPerformanceSql({
150 ...props,
151 page: 1,
152 pageSize: safePageSize,
153 })
154
155 // When a read-replica is selected, require its connection string before fetching.
156 // Falling back to the primary's connection string would silently query the wrong database.
157 const isPrimarySelected = !state.selectedDatabaseId || state.selectedDatabaseId === project?.ref
158 const effectiveConnectionString = isPrimarySelected
159 ? (connectionString ?? project?.connectionString)
160 : connectionString
161
162 const { data, isPending, isRefetching, isFetchingNextPage, hasNextPage, error, fetchNextPage } =
163 useInfiniteQuery({
164 queryKey: [
165 'projects',
166 project?.ref,
167 'query-performance-infinite',
168 {
169 ...props,
170 pageSize: safePageSize,
171 identifier: state.selectedDatabaseId,
172 connectionString: effectiveConnectionString,
173 },
174 ],
175 initialPageParam: 1,
176 queryFn: ({ pageParam, signal }) => {
177 const { sql } = generateQueryPerformanceSql({
178 ...props,
179 page: pageParam,
180 pageSize: safePageSize,
181 })
182 return executeSql<QueryPerformanceRow[]>(
183 {
184 projectRef: project?.ref,
185 connectionString: effectiveConnectionString,
186 sql,
187 },
188 signal
189 ).then((res) => res.result)
190 },
191 getNextPageParam: (lastPage, allPages) => {
192 return lastPage.length < safePageSize ? undefined : allPages.length + 1
193 },
194 // Don't run until we have a connection string for the selected database.
195 // For replicas this prevents a silent fallback to the primary before replicas load.
196 // In self-hosted mode (IS_PLATFORM=false) there is no real connection string, so we
197 // skip the check — executeSql works fine without one on self-hosted deployments.
198 enabled: Boolean(project?.ref) && (!IS_PLATFORM || Boolean(effectiveConnectionString)),
199 refetchOnWindowFocus: false,
200 refetchOnReconnect: false,
201 })
202
203 return {
204 data: data?.pages.flatMap((page) => page) ?? undefined,
205 isLoading: isPending,
206 isRefetching,
207 isFetchingNextPage,
208 hasNextPage: hasNextPage ?? false,
209 error,
210 fetchNextPage,
211 // Reset to page 1 instead of re-fetching all loaded pages, avoiding a burst
212 // of N requests when the user clicks Refresh after scrolling through multiple pages.
213 refetch: () =>
214 queryClient.resetQueries({
215 queryKey: ['projects', project?.ref, 'query-performance-infinite'],
216 exact: false,
217 }),
218 resolvedSql: page1Sql,
219 }
220}