konnosDashboard
QueryPerformanceGrid.tsx650 lines · main
1import { useParams } from 'common'
2import { ArrowDown, ArrowRight, ArrowUp, ChevronDown, TextSearch } from 'lucide-react'
3import { parseAsArrayOf, parseAsJson, parseAsString, useQueryStates } from 'nuqs'
4import { UIEvent, useCallback, useEffect, useMemo, useRef, useState } from 'react'
5import DataGrid, { Column, DataGridHandle, Row } from 'react-data-grid'
6import {
7 Button,
8 cn,
9 DropdownMenu,
10 DropdownMenuContent,
11 DropdownMenuItem,
12 DropdownMenuTrigger,
13 Sheet,
14 SheetContent,
15 SheetDescription,
16 SheetTitle,
17 Tabs_Shadcn_,
18 TabsContent_Shadcn_,
19 TabsList_Shadcn_,
20 TabsTrigger_Shadcn_,
21} from 'ui'
22import { Admonition } from 'ui-patterns'
23import { CodeBlock } from 'ui-patterns/CodeBlock'
24import { InfoTooltip } from 'ui-patterns/info-tooltip'
25import { GenericSkeletonLoader } from 'ui-patterns/ShimmeringLoader'
26
27import { useQueryPerformanceSort } from './hooks/useQueryPerformanceSort'
28import {
29 hasIndexRecommendations,
30 queryInvolvesProtectedSchemas,
31} from './IndexAdvisor/index-advisor.utils'
32import { IndexSuggestionIcon } from './IndexAdvisor/IndexSuggestionIcon'
33import { QueryDetail } from './QueryDetail'
34import { QueryIndexes } from './QueryIndexes'
35import {
36 QUERY_PERFORMANCE_COLUMNS,
37 QUERY_PERFORMANCE_ROLE_DESCRIPTION,
38} from './QueryPerformance.constants'
39import { QueryPerformanceRow } from './QueryPerformance.types'
40import { formatDuration } from './QueryPerformance.utils'
41import { NumericFilter } from '@/components/interfaces/Reports/v2/ReportsNumericFilter'
42import { ButtonTooltip } from '@/components/ui/ButtonTooltip'
43
44interface QueryPerformanceGridProps {
45 aggregatedData: QueryPerformanceRow[]
46 isLoading: boolean
47 error?: string | null
48 currentSelectedQuery?: string | null
49 onCurrentSelectQuery?: (query: string) => void
50 onRetry?: () => void
51 onScroll?: (event: UIEvent<HTMLDivElement>) => void
52}
53
54const calculateTimeConsumedWidth = (data: QueryPerformanceRow[]) => {
55 if (!data || data.length === 0) return 150
56
57 let maxWidth = 150
58
59 data.forEach((row) => {
60 const percentage = row.prop_total_time || 0
61 const totalTime = row.total_time || 0
62
63 if (percentage && totalTime) {
64 const percentageText = `${percentage.toFixed(1)}%`
65 const durationText = formatDuration(totalTime)
66 const fullText = `${percentageText} / ${durationText}`
67 const estimatedWidth = fullText.length * 8 + 40
68
69 maxWidth = Math.max(maxWidth, estimatedWidth)
70 }
71 })
72
73 return Math.min(maxWidth, 300)
74}
75
76export const QueryPerformanceGrid = ({
77 aggregatedData,
78 isLoading,
79 error,
80 currentSelectedQuery,
81 onCurrentSelectQuery,
82 onRetry,
83 onScroll,
84}: QueryPerformanceGridProps) => {
85 const { sort, setSortConfig } = useQueryPerformanceSort()
86 const gridRef = useRef<DataGridHandle>(null)
87 const { sort: urlSort, order } = useParams()
88 const [{ search, roles, callsFilter }] = useQueryStates({
89 search: parseAsString.withDefault(''),
90 roles: parseAsArrayOf(parseAsString).withDefault([]),
91 callsFilter: parseAsJson<NumericFilter | null>(
92 (value) => value as NumericFilter | null
93 ).withDefault({
94 operator: '>=',
95 value: 0,
96 } as NumericFilter),
97 })
98 const dataGridContainerRef = useRef<HTMLDivElement>(null)
99
100 const [view, setView] = useState<'details' | 'suggestion'>('details')
101 const [selectedRow, setSelectedRow] = useState<number>()
102
103 const columns = QUERY_PERFORMANCE_COLUMNS.map((col) => {
104 const nonSortableColumns = ['query']
105
106 const result: Column<any> = {
107 key: col.id,
108 name: col.name,
109 cellClass: `column-${col.id}`,
110 resizable: true,
111 minWidth:
112 col.id === 'prop_total_time'
113 ? calculateTimeConsumedWidth((aggregatedData as any) ?? [])
114 : (col.minWidth ?? 120),
115 sortable: !nonSortableColumns.includes(col.id),
116 headerCellClass: 'first:pl-6 cursor-pointer',
117 renderHeaderCell: () => {
118 const isSortable = !nonSortableColumns.includes(col.id)
119
120 return (
121 <div className="flex items-center justify-between text-xs w-full">
122 <div className="flex items-center gap-x-2">
123 <p className="text-foreground! font-medium">{col.name}</p>
124 {col.description && (
125 <p className="text-foreground-lighter font-normal">{col.description}</p>
126 )}
127 </div>
128
129 {isSortable && (
130 <DropdownMenu>
131 <DropdownMenuTrigger asChild>
132 <Button
133 type="text"
134 size="tiny"
135 className="p-1 h-5 w-5 shrink-0"
136 icon={<ChevronDown size={14} className="text-foreground-muted" />}
137 onClick={(e) => e.stopPropagation()}
138 />
139 </DropdownMenuTrigger>
140 <DropdownMenuContent align="end" className="w-48">
141 <DropdownMenuItem
142 onClick={() => {
143 setSortConfig(col.id, 'asc')
144 }}
145 className={cn(
146 'flex gap-2',
147 sort?.column === col.id && sort?.order === 'asc' && 'text-foreground'
148 )}
149 >
150 <ArrowUp size={14} />
151 Sort Ascending
152 </DropdownMenuItem>
153 <DropdownMenuItem
154 onClick={() => {
155 setSortConfig(col.id, 'desc')
156 }}
157 className={cn(
158 'flex gap-2',
159 sort?.column === col.id && sort?.order === 'desc' && 'text-foreground'
160 )}
161 >
162 <ArrowDown size={14} />
163 Sort Descending
164 </DropdownMenuItem>
165 </DropdownMenuContent>
166 </DropdownMenu>
167 )}
168 </div>
169 )
170 },
171 renderCell: (props) => {
172 const value = props.row?.[col.id]
173 if (col.id === 'query') {
174 return (
175 <div className="w-full flex items-center gap-x-3 group">
176 <div className="shrink-0 w-4">
177 {hasIndexRecommendations(props.row.index_advisor_result, true) && (
178 <IndexSuggestionIcon
179 indexAdvisorResult={props.row.index_advisor_result}
180 onClickIcon={() => {
181 setSelectedRow(props.rowIdx)
182 setView('suggestion')
183 gridRef.current?.scrollToCell({ idx: 0, rowIdx: props.rowIdx })
184 }}
185 />
186 )}
187 </div>
188 <CodeBlock
189 language="pgsql"
190 className="bg-transparent! p-0! m-0! border-none! truncate! whitespace-nowrap! w-full! pr-20! pointer-events-none"
191 wrapperClassName="flex-1 min-w-0 max-w-full overflow-hidden!"
192 hideLineNumbers
193 hideCopy
194 value={typeof value === 'string' ? value.replace(/\s+/g, ' ').trim() : ''}
195 wrapLines={false}
196 />
197 {onCurrentSelectQuery && (
198 <ButtonTooltip
199 tooltip={{ content: { text: 'Query details' } }}
200 icon={<ArrowRight size={14} />}
201 size="tiny"
202 type="default"
203 onClick={(e) => {
204 e.stopPropagation()
205 setSelectedRow(props.rowIdx)
206 setView('details')
207 gridRef.current?.scrollToCell({ idx: 0, rowIdx: props.rowIdx })
208 }}
209 className="p-1 shrink-0 -translate-x-2 group-hover:flex hidden"
210 />
211 )}
212 </div>
213 )
214 }
215
216 const isTime = col.name.includes('time')
217 const formattedValue =
218 !!value && typeof value === 'number' && !isNaN(value) && isFinite(value)
219 ? isTime
220 ? `${value.toFixed(0).toLocaleString()}ms`
221 : value.toLocaleString()
222 : ''
223
224 if (col.id === 'prop_total_time') {
225 const percentage = props.row.prop_total_time || 0
226 const totalTime = props.row.total_time || 0
227 const fillWidth = Math.min(percentage, 100)
228
229 return (
230 <div className="w-full flex flex-col justify-center text-xs text-right tabular-nums font-mono">
231 <div
232 className="absolute inset-0 bg-foreground transition-all duration-200 z-0"
233 style={{
234 width: `${fillWidth}%`,
235 opacity: 0.04,
236 }}
237 />
238 {percentage && totalTime ? (
239 <span className="flex items-center justify-end gap-x-1.5">
240 <span
241 className={cn(percentage.toFixed(1) === '0.0' && 'text-foreground-lighter')}
242 >
243 {percentage.toFixed(1)}%
244 </span>{' '}
245 <span className="text-muted">/</span>
246 <span
247 className={cn(
248 formatDuration(totalTime) === '0.00s' && 'text-foreground-lighter'
249 )}
250 >
251 {formatDuration(totalTime)}
252 </span>
253 </span>
254 ) : (
255 <p className="text-muted">&ndash;</p>
256 )}
257 </div>
258 )
259 }
260
261 if (col.id === 'calls') {
262 return (
263 <div className="w-full flex flex-col justify-center text-xs text-right tabular-nums font-mono">
264 {typeof value === 'number' && !isNaN(value) && isFinite(value) ? (
265 <p className={cn(value === 0 && 'text-foreground-lighter')}>
266 {value.toLocaleString()}
267 </p>
268 ) : (
269 <p className="text-muted">&ndash;</p>
270 )}
271 </div>
272 )
273 }
274
275 if (col.id === 'max_time' || col.id === 'mean_time' || col.id === 'min_time') {
276 return (
277 <div className="w-full flex flex-col justify-center text-xs text-right tabular-nums font-mono">
278 {typeof value === 'number' && !isNaN(value) && isFinite(value) ? (
279 <p className={cn(value.toFixed(0) === '0' && 'text-foreground-lighter')}>
280 {Math.round(value).toLocaleString()}ms
281 </p>
282 ) : (
283 <p className="text-muted">&ndash;</p>
284 )}
285 </div>
286 )
287 }
288
289 if (col.id === 'rows_read') {
290 return (
291 <div className="w-full flex flex-col justify-center text-xs text-right tabular-nums font-mono">
292 {typeof value === 'number' && !isNaN(value) && isFinite(value) ? (
293 <p className={cn(value === 0 && 'text-foreground-lighter')}>
294 {value.toLocaleString()}
295 </p>
296 ) : (
297 <p className="text-muted">&ndash;</p>
298 )}
299 </div>
300 )
301 }
302
303 if (col.id === 'cache_hit_rate') {
304 const numericValue = typeof value === 'number' ? value : parseFloat(value)
305 return (
306 <div className="w-full flex flex-col justify-center text-xs text-right tabular-nums font-mono">
307 {typeof numericValue === 'number' &&
308 !isNaN(numericValue) &&
309 isFinite(numericValue) ? (
310 <p className={cn(numericValue.toFixed(2) === '0.00' && 'text-foreground-lighter')}>
311 {numericValue.toLocaleString(undefined, {
312 minimumFractionDigits: 2,
313 maximumFractionDigits: 2,
314 })}
315 %
316 </p>
317 ) : (
318 <p className="text-muted">&ndash;</p>
319 )}
320 </div>
321 )
322 }
323
324 if (col.id === 'rolname') {
325 return (
326 <div className="w-full flex flex-col justify-center">
327 {value ? (
328 <span className="flex items-center gap-x-1">
329 <p className="font-mono text-xs">{value}</p>
330 <InfoTooltip align="end" alignOffset={-12} className="w-56">
331 {
332 QUERY_PERFORMANCE_ROLE_DESCRIPTION.find((role) => role.name === value)
333 ?.description
334 }
335 </InfoTooltip>
336 </span>
337 ) : (
338 <p className="text-muted">&ndash;</p>
339 )}
340 </div>
341 )
342 }
343
344 if (col.id === 'application_name') {
345 return (
346 <div className="w-full flex flex-col justify-center">
347 {value ? (
348 <p className="font-mono text-xs">{value}</p>
349 ) : (
350 <p className="text-muted">&ndash;</p>
351 )}
352 </div>
353 )
354 }
355
356 return (
357 <div className="w-full flex flex-col gap-y-0.5 justify-center text-xs">
358 <p>{formattedValue}</p>
359 </div>
360 )
361 },
362 }
363 return result
364 })
365
366 const reportData = useMemo(() => {
367 let data = [...aggregatedData]
368
369 if (search && typeof search === 'string' && search.length > 0) {
370 data = data.filter((row) => row.query.toLowerCase().includes(search.toLowerCase()))
371 }
372
373 if (roles && Array.isArray(roles) && roles.length > 0) {
374 data = data.filter((row) => row.rolname && roles.includes(row.rolname))
375 }
376
377 if (callsFilter) {
378 const { operator, value } = callsFilter
379 data = data.filter((row) => {
380 const calls = row.calls || 0
381 switch (operator) {
382 case '=':
383 return calls === value
384 case '>=':
385 return calls >= value
386 case '<=':
387 return calls <= value
388 case '>':
389 return calls > value
390 case '<':
391 return calls < value
392 case '!=':
393 return calls !== value
394 default:
395 return true
396 }
397 })
398 }
399
400 if (sort?.column === 'prop_total_time') {
401 data.sort((a, b) => {
402 const aValue = a.prop_total_time || 0
403 const bValue = b.prop_total_time || 0
404 return sort.order === 'asc' ? aValue - bValue : bValue - aValue
405 })
406 } else if (sort?.column && sort.column !== 'query') {
407 data.sort((a, b) => {
408 const aValue = a[sort.column as keyof QueryPerformanceRow] || 0
409 const bValue = b[sort.column as keyof QueryPerformanceRow] || 0
410
411 if (typeof aValue === 'number' && typeof bValue === 'number') {
412 return sort.order === 'asc' ? aValue - bValue : bValue - aValue
413 }
414 return 0
415 })
416 }
417
418 return data
419 }, [aggregatedData, sort, search, roles, callsFilter])
420
421 useEffect(() => {
422 setSelectedRow(undefined)
423 }, [search, roles, urlSort, order, callsFilter])
424
425 const handleKeyDown = useCallback(
426 (event: KeyboardEvent) => {
427 if (!reportData.length || selectedRow === undefined) return
428
429 if (event.key !== 'ArrowUp' && event.key !== 'ArrowDown') return
430
431 event.stopPropagation()
432
433 let nextIndex = selectedRow
434 if (event.key === 'ArrowUp' && selectedRow > 0) {
435 nextIndex = selectedRow - 1
436 } else if (event.key === 'ArrowDown' && selectedRow < reportData.length - 1) {
437 nextIndex = selectedRow + 1
438 }
439
440 if (nextIndex !== selectedRow) {
441 setSelectedRow(nextIndex)
442 gridRef.current?.scrollToCell({ idx: 0, rowIdx: nextIndex })
443
444 const rowQuery = reportData[nextIndex]?.query ?? ''
445 if (!rowQuery.trim().toLowerCase().startsWith('select')) {
446 setView('details')
447 }
448 }
449 },
450 [reportData, selectedRow]
451 )
452
453 useEffect(() => {
454 // run before RDG to prevent header focus (the third param: true)
455 window.addEventListener('keydown', handleKeyDown, true)
456 return () => {
457 window.removeEventListener('keydown', handleKeyDown, true)
458 }
459 }, [handleKeyDown])
460
461 const isSelectQuery = (query: string | undefined): boolean => {
462 if (!query) return false
463 const formattedQuery = query.trim().toLowerCase()
464 return (
465 formattedQuery.startsWith('select') ||
466 formattedQuery.startsWith('with pgrst_source') ||
467 formattedQuery.startsWith('with pgrst_payload')
468 )
469 }
470
471 useEffect(() => {
472 if (selectedRow !== undefined && view === 'suggestion') {
473 const query = reportData[selectedRow]?.query
474 if (!isSelectQuery(query)) {
475 setView('details')
476 }
477 }
478 }, [selectedRow, view, reportData])
479
480 if (error) {
481 return (
482 <div className="relative flex grow bg-alternative min-h-0">
483 <div className="flex-1 min-w-0 p-6">
484 <Admonition
485 type="destructive"
486 title="Failed to load query performance data"
487 description={error}
488 >
489 {onRetry && (
490 <div className="mt-4">
491 <Button type="default" onClick={onRetry}>
492 Try again
493 </Button>
494 </div>
495 )}
496 </Admonition>
497 </div>
498 </div>
499 )
500 }
501
502 const selectedQuery = selectedRow !== undefined ? reportData[selectedRow]?.query : undefined
503 const isProtectedSchemaQuery = queryInvolvesProtectedSchemas(selectedQuery)
504 const canShowIndexesTab = isSelectQuery(selectedQuery) && !isProtectedSchemaQuery
505
506 return (
507 <div className="relative flex grow bg-alternative min-h-0">
508 <div ref={dataGridContainerRef} className="flex-1 min-w-0 overflow-x-auto">
509 <DataGrid
510 ref={gridRef}
511 style={{ height: '100%' }}
512 className={cn('flex-1 grow h-full')}
513 rowHeight={44}
514 headerRowHeight={36}
515 columns={columns}
516 rows={reportData}
517 onScroll={onScroll}
518 rowClass={(_, idx) => {
519 const isSelected = idx === selectedRow
520 const query = reportData[idx]?.query
521 const isCharted = currentSelectedQuery ? currentSelectedQuery === query : false
522 const hasRecommendations = hasIndexRecommendations(
523 reportData[idx]?.index_advisor_result,
524 true
525 )
526
527 return [
528 `${isSelected ? (hasRecommendations ? 'bg-warning/10 hover:bg-warning/20' : 'bg-surface-300 dark:bg-surface-300') : hasRecommendations ? 'bg-warning/10 hover:bg-warning/20' : 'bg-200 hover:bg-surface-200'} cursor-pointer`,
529 `${isSelected ? (hasRecommendations ? '[&>div:first-child]:border-l-4 border-l-warning [&>div]:border-l-warning' : '[&>div:first-child]:border-l-4 border-l-secondary [&>div]:border-l-foreground!') : ''}`,
530 `${isCharted ? 'bg-surface-200 dark:bg-surface-200' : ''}`,
531 `${isCharted ? '[&>div:first-child]:border-l-4 border-l-secondary [&>div]:border-l-brand' : ''}`,
532 '[&>.rdg-cell]:box-border [&>.rdg-cell]:outline-hidden [&>.rdg-cell]:shadow-none',
533 '[&>.rdg-cell.column-prop_total_time]:relative',
534 ].join(' ')
535 }}
536 renderers={{
537 renderRow(idx, props) {
538 return (
539 <Row
540 {...props}
541 key={`qp-row-${props.rowIdx}`}
542 onClick={(event) => {
543 event.stopPropagation()
544
545 if (typeof idx === 'number' && idx >= 0) {
546 if (onCurrentSelectQuery) {
547 const query = reportData[idx]?.query
548 if (query) {
549 onCurrentSelectQuery(query)
550 }
551 } else {
552 setSelectedRow(idx)
553 const hasRecommendations = hasIndexRecommendations(
554 reportData[idx]?.index_advisor_result,
555 true
556 )
557 setView(hasRecommendations ? 'suggestion' : 'details')
558 gridRef.current?.scrollToCell({ idx: 0, rowIdx: idx })
559 }
560 }
561 }}
562 />
563 )
564 },
565 noRowsFallback: isLoading ? (
566 <div className="absolute top-14 px-6 w-full">
567 <GenericSkeletonLoader />
568 </div>
569 ) : (
570 <div className="absolute top-20 px-6 flex flex-col items-center justify-center w-full gap-y-2">
571 <TextSearch className="text-foreground-muted" strokeWidth={1} />
572 <div className="text-center">
573 <p className="text-foreground">No queries detected</p>
574 <p className="text-foreground-light">
575 There are no actively running queries that match the criteria
576 </p>
577 </div>
578 </div>
579 ),
580 }}
581 />
582 </div>
583
584 <Sheet
585 open={selectedRow !== undefined}
586 onOpenChange={(open) => {
587 if (!open) {
588 setSelectedRow(undefined)
589 }
590 }}
591 modal={false}
592 >
593 <SheetTitle className="sr-only">Query details</SheetTitle>
594 <SheetDescription className="sr-only">
595 Query Performance Details &amp; Indexes
596 </SheetDescription>
597 <SheetContent
598 side="right"
599 className="flex flex-col h-full bg-studio border-l lg:w-[calc(100vw-802px)]! max-w-[700px] w-full"
600 hasOverlay={false}
601 onInteractOutside={(event) => {
602 if (dataGridContainerRef.current?.contains(event.target as Node)) {
603 event.preventDefault()
604 }
605 }}
606 >
607 <Tabs_Shadcn_
608 value={view}
609 className="flex flex-col h-full"
610 onValueChange={(value: any) => setView(value)}
611 >
612 <div className="px-5 border-b">
613 <TabsList_Shadcn_ className="px-0 flex gap-x-4 min-h-[46px] border-b-0 [&>button]:h-[47px]">
614 <TabsTrigger_Shadcn_
615 value="details"
616 className="px-0 pb-0 data-[state=active]:bg-transparent shadow-none!"
617 >
618 Query details
619 </TabsTrigger_Shadcn_>
620 {selectedRow !== undefined && canShowIndexesTab && (
621 <TabsTrigger_Shadcn_
622 value="suggestion"
623 className="px-0 pb-0 data-[state=active]:bg-transparent shadow-none!"
624 >
625 Indexes
626 </TabsTrigger_Shadcn_>
627 )}
628 </TabsList_Shadcn_>
629 </div>
630
631 <TabsContent_Shadcn_ value="details" className="mt-0 grow min-h-0 overflow-y-auto">
632 {selectedRow !== undefined && (
633 <QueryDetail
634 selectedRow={reportData[selectedRow]}
635 onClickViewSuggestion={() => setView('suggestion')}
636 onClose={() => setSelectedRow(undefined)}
637 />
638 )}
639 </TabsContent_Shadcn_>
640 {selectedRow !== undefined && canShowIndexesTab && (
641 <TabsContent_Shadcn_ value="suggestion" className="mt-0 grow min-h-0 overflow-y-auto">
642 <QueryIndexes selectedRow={reportData[selectedRow]} />
643 </TabsContent_Shadcn_>
644 )}
645 </Tabs_Shadcn_>
646 </SheetContent>
647 </Sheet>
648 </div>
649 )
650}