konnosDashboard
QueryIndexes.tsx447 lines · main
1import { AccordionTrigger } from '@ui/components/shadcn/ui/accordion'
2import { Check, Lightbulb, Table2 } from 'lucide-react'
3import { useEffect, useState } from 'react'
4import {
5 Accordion,
6 AccordionContent,
7 AccordionItem,
8 Alert,
9 AlertDescription,
10 AlertTitle,
11 Button,
12 cn,
13 Collapsible,
14 CollapsibleContent,
15 CollapsibleTrigger,
16} from 'ui'
17import { Admonition } from 'ui-patterns'
18import { CodeBlock } from 'ui-patterns/CodeBlock'
19import { GenericSkeletonLoader } from 'ui-patterns/ShimmeringLoader'
20
21import { useIndexInvalidation } from './hooks/useIndexInvalidation'
22import { EnableIndexAdvisorButton } from './IndexAdvisor/EnableIndexAdvisorButton'
23import {
24 calculateImprovement,
25 createIndexes,
26 hasIndexRecommendations,
27} from './IndexAdvisor/index-advisor.utils'
28import { IndexAdvisorDisabledState } from './IndexAdvisor/IndexAdvisorDisabledState'
29import { IndexImprovementText } from './IndexAdvisor/IndexImprovementText'
30import { QueryPanelContainer, QueryPanelScoreSection, QueryPanelSection } from './QueryPanel'
31import { QueryPerformanceRow } from './QueryPerformance.types'
32import { useIndexAdvisorStatus } from '@/components/interfaces/QueryPerformance/hooks/useIsIndexAdvisorStatus'
33import AlertError from '@/components/ui/AlertError'
34import { DocsButton } from '@/components/ui/DocsButton'
35import { useDatabaseExtensionsQuery } from '@/data/database-extensions/database-extensions-query'
36import {
37 GetIndexAdvisorResultResponse,
38 useGetIndexAdvisorResult,
39} from '@/data/database/retrieve-index-advisor-result-query'
40import { useGetIndexesFromSelectQuery } from '@/data/database/retrieve-index-from-select-query'
41import { useSelectedProjectQuery } from '@/hooks/misc/useSelectedProject'
42import { DOCS_URL } from '@/lib/constants'
43import { useTrack } from '@/lib/telemetry/track'
44
45interface QueryIndexesProps {
46 selectedRow: Pick<QueryPerformanceRow, 'query'>
47 columnName?: string
48 suggestedSelectQuery?: string
49 prefetchedIndexAdvisorResult?: GetIndexAdvisorResultResponse | null
50
51 onClose?: () => void
52}
53
54// [Joshen] There's several more UX things we can do to help ease the learning curve of indexes I think
55// e.g understanding "costs", what numbers of "costs" are actually considered insignificant
56
57export const QueryIndexes = ({
58 selectedRow,
59 columnName,
60 suggestedSelectQuery,
61 prefetchedIndexAdvisorResult,
62 onClose,
63}: QueryIndexesProps) => {
64 // [Joshen] TODO implement this logic once the linter rules are in
65 const isLinterWarning = false
66 const { data: project } = useSelectedProjectQuery()
67 const [showStartupCosts, setShowStartupCosts] = useState(false)
68 const [isExecuting, setIsExecuting] = useState(false)
69 const track = useTrack()
70 const [hasTrackedTabView, setHasTrackedTabView] = useState(false)
71
72 const {
73 data: usedIndexes,
74 isSuccess,
75 isPending: isLoading,
76 isError,
77 error,
78 } = useGetIndexesFromSelectQuery({
79 projectRef: project?.ref,
80 connectionString: project?.connectionString,
81 query: selectedRow?.['query'],
82 })
83
84 const { isPending: isLoadingExtensions } = useDatabaseExtensionsQuery({
85 projectRef: project?.ref,
86 connectionString: project?.connectionString,
87 })
88
89 const { isIndexAdvisorEnabled } = useIndexAdvisorStatus()
90
91 const hasPrefetchedResult = prefetchedIndexAdvisorResult !== undefined
92
93 const {
94 data: fetchedIndexAdvisorResult,
95 error: indexAdvisorError,
96 refetch,
97 isError: isErrorIndexAdvisorResult,
98 isSuccess: isFetchSuccessIndexAdvisorResult,
99 isLoading: isFetchLoadingIndexAdvisorResult,
100 } = useGetIndexAdvisorResult(
101 {
102 projectRef: project?.ref,
103 connectionString: project?.connectionString,
104 query: selectedRow?.['query'],
105 },
106 { enabled: isIndexAdvisorEnabled && !hasPrefetchedResult }
107 )
108
109 const indexAdvisorResult = hasPrefetchedResult
110 ? prefetchedIndexAdvisorResult
111 : fetchedIndexAdvisorResult
112 const isSuccessIndexAdvisorResult = hasPrefetchedResult || isFetchSuccessIndexAdvisorResult
113 const isLoadingIndexAdvisorResult = hasPrefetchedResult ? false : isFetchLoadingIndexAdvisorResult
114
115 const {
116 index_statements,
117 startup_cost_after,
118 startup_cost_before,
119 total_cost_after,
120 total_cost_before,
121 } = indexAdvisorResult ?? { index_statements: [], total_cost_after: 0, total_cost_before: 0 }
122 const hasIndexRecommendation = hasIndexRecommendations(
123 indexAdvisorResult,
124 isSuccessIndexAdvisorResult
125 )
126 const totalImprovement = calculateImprovement(total_cost_before, total_cost_after)
127
128 const invalidateQueries = useIndexInvalidation()
129
130 useEffect(() => {
131 if (!isLoadingIndexAdvisorResult && !hasTrackedTabView) {
132 track('index_advisor_tab_clicked', {
133 hasRecommendations: hasIndexRecommendation,
134 isIndexAdvisorEnabled: isIndexAdvisorEnabled,
135 })
136 setHasTrackedTabView(true)
137 }
138 }, [
139 isLoadingIndexAdvisorResult,
140 hasIndexRecommendation,
141 hasTrackedTabView,
142 track,
143 isIndexAdvisorEnabled,
144 ])
145
146 const createIndex = async () => {
147 if (index_statements.length === 0) return
148
149 setIsExecuting(true)
150 track('index_advisor_create_indexes_button_clicked')
151
152 try {
153 await createIndexes({
154 projectRef: project?.ref,
155 connectionString: project?.connectionString,
156 indexStatements: index_statements,
157 onSuccess: () => refetch(),
158 })
159
160 // Only invalidate queries if index creation was successful
161 invalidateQueries()
162 } catch (error) {
163 // Error is already handled by createIndexes with a toast notification
164 // But we could add component-specific error handling here if needed
165 console.error('Failed to create index:', error)
166 setIsExecuting(false)
167 } finally {
168 setIsExecuting(false)
169
170 onClose?.()
171 }
172 }
173
174 if (!isLoadingExtensions && !isIndexAdvisorEnabled) {
175 return (
176 <QueryPanelContainer className="h-full">
177 <QueryPanelSection className="pt-2">
178 <div className="border rounded-sm border-dashed flex flex-col items-center justify-center py-4 px-12 gap-y-1 text-center">
179 <p className="text-sm text-foreground-light">Enable Index Advisor</p>
180 <p className="text-center text-xs text-foreground-lighter mb-2">
181 Recommends indexes to improve query performance.
182 </p>
183 <div className="flex items-center gap-x-2">
184 <DocsButton href={`${DOCS_URL}/guides/database/extensions/index_advisor`} />
185 <EnableIndexAdvisorButton />
186 </div>
187 </div>
188 </QueryPanelSection>
189 </QueryPanelContainer>
190 )
191 }
192
193 return (
194 <QueryPanelContainer className="h-full overflow-y-auto py-0 pt-4">
195 {(columnName || suggestedSelectQuery) && (
196 <QueryPanelSection className="pt-2 pb-6 border-b">
197 <div className="flex flex-col gap-y-3">
198 <div>
199 <h4 className="mb-2">Recommendation reason</h4>
200 {columnName && (
201 <p className="text-sm text-foreground-light">
202 Recommendation for column: <span className="font-mono">{columnName}</span>
203 </p>
204 )}
205 </div>
206 {suggestedSelectQuery && (
207 <div className="flex flex-col gap-y-4">
208 <p className="text-sm text-foreground-light">Based on the following query:</p>
209 <CodeBlock
210 hideLineNumbers
211 value={suggestedSelectQuery}
212 language="sql"
213 className={cn(
214 'max-w-full max-h-[200px]',
215 'py-2! px-2.5! prose dark:prose-dark',
216 '[&>code]:m-0 [&>code>span]:flex [&>code>span]:flex-wrap'
217 )}
218 />
219 </div>
220 )}
221 </div>
222 </QueryPanelSection>
223 )}
224 <QueryPanelSection
225 className={cn('mb-6', !suggestedSelectQuery && !columnName ? 'pt-2' : 'pt-6')}
226 >
227 <div className="mb-4 flex flex-col gap-y-1">
228 <h4 className="mb-2">Indexes in use</h4>
229 <p className="text-sm text-foreground-light">
230 This query is using the following index{(usedIndexes ?? []).length > 1 ? 's' : ''}:
231 </p>
232 </div>
233 {isLoading && <GenericSkeletonLoader />}
234 {isError && (
235 <AlertError
236 projectRef={project?.ref}
237 error={error}
238 subject="Failed to retrieve indexes in use"
239 />
240 )}
241 {isSuccess && (
242 <div>
243 {usedIndexes.length === 0 && (
244 <div className="border rounded-sm border-dashed flex flex-col items-center justify-center py-4 px-12 gap-y-1 text-center">
245 <p className="text-sm text-foreground-light">
246 No indexes are involved in this query
247 </p>
248 <p className="text-center text-xs text-foreground-lighter">
249 Indexes may not necessarily be used if they incur a higher cost when executing the
250 query
251 </p>
252 </div>
253 )}
254 {usedIndexes.map((index) => {
255 return (
256 <div
257 key={index.name}
258 className="flex items-center gap-x-4 bg-surface-100 border first:rounded-tl first:rounded-tr border-b-0 last:border-b last:rounded-b px-2 py-2"
259 >
260 <div className="flex items-center gap-x-2">
261 <Table2 size={14} className="text-foreground-light" />
262 <span className="text-xs font-mono text-foreground-light">
263 {index.schema}.{index.table}
264 </span>
265 </div>
266 <span className="text-xs font-mono">{index.name}</span>
267 </div>
268 )
269 })}
270 </div>
271 )}
272 </QueryPanelSection>
273 <QueryPanelSection className="flex flex-col gap-y-6 py-6 border-t">
274 <div className="flex flex-col gap-y-1">
275 {(!isSuccessIndexAdvisorResult || indexAdvisorResult !== null) && (
276 <h4 className="mb-2">New index recommendations</h4>
277 )}
278 {isLoadingExtensions ? (
279 <GenericSkeletonLoader />
280 ) : !isIndexAdvisorEnabled ? (
281 <IndexAdvisorDisabledState />
282 ) : (
283 <>
284 {isLoadingIndexAdvisorResult && <GenericSkeletonLoader />}
285 {isErrorIndexAdvisorResult && (
286 <AlertError
287 projectRef={project?.ref}
288 error={indexAdvisorError}
289 subject="Failed to retrieve result from index advisor"
290 />
291 )}
292 {isSuccessIndexAdvisorResult && (
293 <>
294 {indexAdvisorResult === null ? (
295 <Admonition
296 type="default"
297 showIcon={true}
298 title="Index recommendations not available"
299 description="Index advisor could not analyze this query. This can happen if the query references tables, functions, or extensions that no longer exist or were deleted."
300 />
301 ) : (index_statements ?? []).length === 0 ? (
302 <Alert className="[&>svg]:rounded-full">
303 <Check />
304 <AlertTitle>This query is optimized</AlertTitle>
305 <AlertDescription>
306 Recommendations for indexes will show here
307 </AlertDescription>
308 </Alert>
309 ) : (
310 <>
311 {isLinterWarning ? (
312 <Alert
313 variant="default"
314 className="border-brand-400 bg-alternative [&>svg]:p-0.5 [&>svg]:bg-transparent [&>svg]:text-brand my-3"
315 >
316 <Lightbulb />
317 <AlertTitle>
318 We have {index_statements.length} index recommendation
319 {index_statements.length > 1 ? 's' : ''}
320 </AlertTitle>
321 <AlertDescription>
322 You can improve this query's performance by{' '}
323 <span className="text-brand">{totalImprovement.toFixed(2)}%</span> by
324 adding the following suggested{' '}
325 {index_statements.length > 1 ? 'indexes' : 'index'}
326 </AlertDescription>
327 </Alert>
328 ) : (
329 <IndexImprovementText
330 indexStatements={index_statements}
331 totalCostBefore={total_cost_before}
332 totalCostAfter={total_cost_after}
333 className="text-sm text-foreground-light"
334 />
335 )}
336 <CodeBlock
337 hideLineNumbers
338 value={index_statements.join(';\n') + ';'}
339 language="sql"
340 className={cn(
341 'max-w-full max-h-[310px]',
342 'py-3! px-3.5! prose dark:prose-dark transition',
343 '[&>code]:m-0 [&>code>span]:flex [&>code>span]:flex-wrap'
344 )}
345 />
346 <p className="text-sm text-foreground-light mt-3">
347 This recommendation serves to prevent your queries from slowing down as your
348 application grows, and hence the index may not be used immediately after
349 it's created (e.g If your table is still small at this time).
350 </p>
351 </>
352 )}
353 </>
354 )}
355 </>
356 )}
357 </div>
358 </QueryPanelSection>
359 {isIndexAdvisorEnabled && hasIndexRecommendation && (
360 <>
361 <QueryPanelSection className="py-6 border-t">
362 <div className="flex flex-col gap-y-1">
363 <h4 className="mb-2">Query costs</h4>
364 <div className="border rounded-md flex flex-col bg-surface-100">
365 <QueryPanelScoreSection
366 name="Total cost of query"
367 description="An estimate of how long it will take to return all the rows (Includes start up cost)"
368 before={total_cost_before}
369 after={total_cost_after}
370 />
371 <Collapsible open={showStartupCosts} onOpenChange={setShowStartupCosts}>
372 <CollapsibleContent asChild className="pb-3">
373 <QueryPanelScoreSection
374 hideArrowMarkers
375 className="border-t"
376 name="Start up cost"
377 description="An estimate of how long it will take to fetch the first row"
378 before={startup_cost_before}
379 after={startup_cost_after}
380 />
381 </CollapsibleContent>
382 <CollapsibleTrigger className="text-xs py-1.5 border-t text-foreground-light bg-studio w-full rounded-b-md">
383 View {showStartupCosts ? 'less' : 'more'}
384 </CollapsibleTrigger>
385 </Collapsible>
386 </div>
387 </div>
388 </QueryPanelSection>
389 <QueryPanelSection className="py-6 border-t">
390 <div className="flex flex-col gap-y-2">
391 <h4 className="mb-2">FAQ</h4>
392 <Accordion collapsible type="single" className="border rounded-md">
393 <AccordionItem value="1">
394 <AccordionTrigger className="px-4 py-3 text-sm font-normal text-foreground-light hover:text-foreground transition data-open:text-foreground">
395 What units are cost in?
396 </AccordionTrigger>
397 <AccordionContent className="px-4 text-foreground-light">
398 Costs are in an arbitrary unit, and do not represent a unit of time. The units
399 are anchored (by default) to a single sequential page read costing 1.0 units.
400 They do, however, serve as a predictor of higher execution times.
401 </AccordionContent>
402 </AccordionItem>
403 <AccordionItem value="2" className="border-b-0">
404 <AccordionTrigger className="px-4 py-3 text-sm font-normal text-foreground-light hover:text-foreground transition data-open:text-foreground">
405 How should I prioritize start up and total cost?
406 </AccordionTrigger>
407 <AccordionContent className="px-4 text-foreground-light [&>div]:space-y-2">
408 <p>This depends on the expected size of the result set from the query.</p>
409 <p>
410 For queries that return a small number or rows, the startup cost is more
411 critical and minimizing startup cost can lead to faster response times,
412 especially in interactive applications.
413 </p>
414 <p>
415 For queries that return a large number of rows, the total cost becomes more
416 important, and optimizing it will help in efficiently using resources and
417 reducing overall query execution time.
418 </p>
419 </AccordionContent>
420 </AccordionItem>
421 </Accordion>
422 </div>
423 </QueryPanelSection>
424 </>
425 )}
426
427 {isIndexAdvisorEnabled && hasIndexRecommendation && (
428 <div className="bg-studio sticky bottom-0 border-t py-3 flex items-center justify-between px-5">
429 <div className="flex flex-col gap-y-0.5 text-xs">
430 <span>Apply index to database</span>
431 <span className="text-xs text-foreground-light">
432 This will run the SQL that is shown above
433 </span>
434 </div>
435 <Button
436 disabled={isExecuting}
437 loading={isExecuting}
438 type="primary"
439 onClick={() => createIndex()}
440 >
441 Create index
442 </Button>
443 </div>
444 )}
445 </QueryPanelContainer>
446 )
447}