konnosDashboard
WithStatements.tsx321 lines · main
1import { safeSql } from '@supabase/pg-meta/src/pg-format'
2import { LOCAL_STORAGE_KEYS, useParams } from 'common'
3import { RefreshCw, RotateCcw, X } from 'lucide-react'
4import { parseAsString, useQueryStates } from 'nuqs'
5import { useEffect, useMemo, useState } from 'react'
6import { toast } from 'sonner'
7import { Button, cn, LoadingLine } from 'ui'
8import { Admonition } from 'ui-patterns'
9import ConfirmationModal from 'ui-patterns/Dialogs/ConfirmationModal'
10
11import { Markdown } from '../../Markdown'
12import { captureQueryPerformanceError } from '../QueryPerformance.utils'
13import { QueryPerformanceFilterBar } from '../QueryPerformanceFilterBar'
14import { QueryPerformanceGrid } from '../QueryPerformanceGrid'
15import { QueryPerformanceMetrics } from '../QueryPerformanceMetrics'
16import { QueryPerformanceInfiniteHook } from '../useQueryPerformanceQuery'
17import { transformStatementDataToRows } from './WithStatements.utils'
18import { PresetHookResult } from '@/components/interfaces/Reports/Reports.utils'
19import { ButtonTooltip } from '@/components/ui/ButtonTooltip'
20import { DownloadResultsButton } from '@/components/ui/DownloadResultsButton'
21import { useReadReplicasQuery } from '@/data/read-replicas/replicas-query'
22import { formatDatabaseID } from '@/data/read-replicas/replicas.utils'
23import { executeSql } from '@/data/sql/execute-sql-query'
24import { useInfiniteScroll } from '@/hooks/misc/useInfiniteScroll'
25import { useLocalStorageQuery } from '@/hooks/misc/useLocalStorage'
26import { useSelectedProjectQuery } from '@/hooks/misc/useSelectedProject'
27import { DOCS_URL, IS_PLATFORM } from '@/lib/constants'
28import { getErrorMessage } from '@/lib/get-error-message'
29import { useDatabaseSelectorStateSnapshot } from '@/state/database-selector'
30
31interface WithStatementsProps {
32 queryHitRate: PresetHookResult
33 queryPerformanceQuery: QueryPerformanceInfiniteHook
34 queryMetrics: PresetHookResult
35}
36
37export const WithStatements = ({
38 queryHitRate,
39 queryPerformanceQuery,
40 queryMetrics,
41}: WithStatementsProps) => {
42 const { ref } = useParams()
43 const { data: project } = useSelectedProjectQuery()
44 const state = useDatabaseSelectorStateSnapshot()
45 const {
46 data,
47 isLoading,
48 isRefetching,
49 isFetchingNextPage,
50 hasNextPage,
51 error: queryError,
52 fetchNextPage,
53 refetch: runQuery,
54 } = queryPerformanceQuery
55 const isPrimaryDatabase = state.selectedDatabaseId === ref
56 const formattedDatabaseId = formatDatabaseID(state.selectedDatabaseId ?? '')
57
58 const hitRateError = 'error' in queryHitRate ? queryHitRate.error : null
59 const metricsError = 'error' in queryMetrics ? queryMetrics.error : null
60 const mainQueryError = queryError || null
61
62 const [showResetgPgStatStatements, setShowResetgPgStatStatements] = useState(false)
63
64 const [showBottomSection, setShowBottomSection] = useLocalStorageQuery(
65 LOCAL_STORAGE_KEYS.QUERY_PERF_SHOW_BOTTOM_SECTION,
66 true
67 )
68
69 const [{ indexAdvisor }] = useQueryStates({
70 indexAdvisor: parseAsString.withDefault('false'),
71 })
72
73 const handleRefresh = () => {
74 runQuery()
75 queryHitRate.runQuery()
76 queryMetrics.runQuery()
77 }
78
79 const processedData = useMemo(() => {
80 return transformStatementDataToRows(data || [], indexAdvisor === 'true')
81 }, [data, indexAdvisor])
82
83 const { data: databases } = useReadReplicasQuery({ projectRef: ref })
84
85 const handleScroll = useInfiniteScroll({
86 isLoading,
87 isFetchingNextPage,
88 hasNextPage,
89 fetchNextPage,
90 })
91
92 useEffect(() => {
93 state.setSelectedDatabaseId(ref)
94 // eslint-disable-next-line react-hooks/exhaustive-deps
95 }, [ref])
96
97 useEffect(() => {
98 if (mainQueryError) {
99 const errorMessage = getErrorMessage(mainQueryError)
100 const isNotInstalled =
101 typeof errorMessage === 'string' &&
102 errorMessage.includes('pg_stat_statements') &&
103 errorMessage.includes('does not exist')
104 if (!isNotInstalled) {
105 captureQueryPerformanceError(mainQueryError, {
106 projectRef: ref,
107 databaseIdentifier: state.selectedDatabaseId,
108 queryPreset: 'unified',
109 queryType: 'mainQuery',
110 postgresVersion: project?.dbVersion,
111 databaseType: isPrimaryDatabase ? 'primary' : 'read-replica',
112 sql: queryPerformanceQuery.resolvedSql,
113 errorMessage: errorMessage || undefined,
114 })
115 }
116 }
117 }, [
118 mainQueryError,
119 ref,
120 state.selectedDatabaseId,
121 project?.dbVersion,
122 isPrimaryDatabase,
123 queryPerformanceQuery.resolvedSql,
124 ])
125
126 useEffect(() => {
127 if (hitRateError) {
128 const errorMessage = getErrorMessage(hitRateError)
129 captureQueryPerformanceError(hitRateError, {
130 projectRef: ref,
131 databaseIdentifier: state.selectedDatabaseId,
132 queryPreset: 'queryHitRate',
133 queryType: 'hitRate',
134 postgresVersion: project?.dbVersion,
135 databaseType: isPrimaryDatabase ? 'primary' : 'read-replica',
136 errorMessage: errorMessage || undefined,
137 })
138 }
139 }, [hitRateError, ref, state.selectedDatabaseId, project?.dbVersion, isPrimaryDatabase])
140
141 useEffect(() => {
142 if (metricsError) {
143 const errorMessage = getErrorMessage(metricsError)
144 captureQueryPerformanceError(metricsError, {
145 projectRef: ref,
146 databaseIdentifier: state.selectedDatabaseId,
147 queryPreset: 'queryMetrics',
148 queryType: 'metrics',
149 postgresVersion: project?.dbVersion,
150 databaseType: isPrimaryDatabase ? 'primary' : 'read-replica',
151 errorMessage: errorMessage || undefined,
152 })
153 }
154 }, [metricsError, ref, state.selectedDatabaseId, project?.dbVersion, isPrimaryDatabase])
155
156 const hasError = mainQueryError || hitRateError || metricsError
157 const errorMessage = mainQueryError
158 ? getErrorMessage(mainQueryError) || 'Failed to load query performance data'
159 : hitRateError
160 ? getErrorMessage(hitRateError) || 'Failed to load cache hit rate data'
161 : metricsError
162 ? getErrorMessage(metricsError) || 'Failed to load query metrics'
163 : null
164
165 const isPgStatStatementsNotInstalled =
166 typeof errorMessage === 'string' &&
167 errorMessage.includes('pg_stat_statements') &&
168 errorMessage.includes('does not exist')
169
170 return (
171 <>
172 {hasError && (
173 <div className="px-6 pt-4">
174 {isPgStatStatementsNotInstalled ? (
175 <Admonition
176 type="warning"
177 title="pg_stat_statements extension is not enabled"
178 description="Query Performance requires the pg_stat_statements extension. Enable it in Database → Extensions."
179 />
180 ) : (
181 <Admonition
182 type="destructive"
183 title="Error loading query performance data"
184 description={
185 errorMessage ||
186 'An error occurred while loading query performance data. Please try refreshing the page.'
187 }
188 />
189 )}
190 </div>
191 )}
192 <QueryPerformanceMetrics />
193 <QueryPerformanceFilterBar
194 showRolesFilter
195 showSourceFilter
196 actions={
197 <>
198 <ButtonTooltip
199 type="default"
200 size="tiny"
201 icon={<RefreshCw />}
202 onClick={handleRefresh}
203 tooltip={{ content: { side: 'top', text: 'Refresh' } }}
204 className="w-[26px]"
205 />
206 <ButtonTooltip
207 type="default"
208 size="tiny"
209 icon={<RotateCcw />}
210 onClick={() => setShowResetgPgStatStatements(true)}
211 tooltip={{ content: { side: 'top', text: 'Reset report' } }}
212 className="w-[26px]"
213 />
214
215 <DownloadResultsButton
216 results={processedData}
217 fileName={`Briven Query Performance Statements (${ref})`}
218 align="end"
219 />
220 </>
221 }
222 />
223 <LoadingLine loading={isLoading || isRefetching || isFetchingNextPage} />
224 <QueryPerformanceGrid
225 aggregatedData={processedData}
226 isLoading={isLoading}
227 error={
228 mainQueryError
229 ? getErrorMessage(mainQueryError) || 'Failed to load query performance data'
230 : null
231 }
232 onRetry={handleRefresh}
233 onScroll={handleScroll}
234 />
235 <div
236 className={cn('px-6 py-6 flex gap-x-4 border-t relative', {
237 hidden: showBottomSection === false,
238 })}
239 >
240 <Button
241 className="absolute top-1.5 right-3 px-1.5"
242 type="text"
243 size="tiny"
244 onClick={() => setShowBottomSection(false)}
245 >
246 <X size="14" />
247 </Button>
248 <div className="w-[33%] flex flex-col gap-y-1 text-sm">
249 <p>Reset report</p>
250 <p className="text-xs text-foreground-light">
251 Consider resetting the analysis after optimizing any queries
252 </p>
253 <Button
254 type="default"
255 className="mt-3! w-min"
256 onClick={() => setShowResetgPgStatStatements(true)}
257 >
258 Reset report
259 </Button>
260 </div>
261
262 <div className="w-[33%] flex flex-col gap-y-1 text-sm">
263 <p>How is this report generated?</p>
264 <Markdown
265 className="text-xs"
266 content={`This report uses the pg_stat_statements table, and pg_stat_statements extension. [Learn more here](${DOCS_URL}/guides/platform/performance#examining-query-performance).`}
267 />
268 </div>
269
270 <div className="w-[33%] flex flex-col gap-y-1 text-sm">
271 <p>Inspect your database for potential issues</p>
272 <Markdown
273 className="text-xs"
274 content={`The Briven CLI comes with a range of tools to help inspect your Postgres instances for
275 potential issues. [Learn more here](${DOCS_URL}/guides/database/inspect).`}
276 />
277 </div>
278 </div>
279
280 <ConfirmationModal
281 visible={showResetgPgStatStatements}
282 size="medium"
283 variant="destructive"
284 title="Reset query performance analysis"
285 confirmLabel="Reset report"
286 confirmLabelLoading="Resetting report"
287 onCancel={() => setShowResetgPgStatStatements(false)}
288 onConfirm={async () => {
289 const connectionString = databases?.find(
290 (db) => db.identifier === state.selectedDatabaseId
291 )?.connectionString
292
293 if (IS_PLATFORM && !connectionString) {
294 return toast.error('Unable to run query: Connection string is missing')
295 }
296
297 try {
298 await executeSql({
299 projectRef: project?.ref,
300 connectionString,
301 sql: safeSql`SELECT pg_stat_statements_reset();`,
302 })
303 handleRefresh()
304 setShowResetgPgStatStatements(false)
305 } catch (error: any) {
306 toast.error(`Failed to reset analysis: ${error.message}`)
307 }
308 }}
309 >
310 <p className="text-foreground-light text-sm">
311 This will reset the pg_stat_statements table in the extensions schema on your{' '}
312 <span className="text-foreground">
313 {isPrimaryDatabase ? 'primary database' : `read replica (ID: ${formattedDatabaseId})`}
314 </span>
315 , which is used to calculate query performance. This data will repopulate immediately
316 after.
317 </p>
318 </ConfirmationModal>
319 </>
320 )
321}