useSlowQueriesCount.ts50 lines · main

1	import { safeSql, type SafeSqlFragment } from '@supabase/pg-meta'
2	import { useMemo } from 'react'
3
4	import useDbQuery from '@/hooks/analytics/useDbQuery'
5
6	export function buildSlowQueriesCountSql(): SafeSqlFragment {
7	return safeSql`
8	-- observability-slow-queries-count
9	set search_path to public, extensions;
10
11	SELECT
12	CASE
13	WHEN EXISTS (
14	SELECT 1 FROM pg_extension WHERE extname = 'pg_stat_statements'
15	)
16	THEN (
17	SELECT count(*)::int
18	FROM pg_stat_statements
19	WHERE total_exec_time + total_plan_time > 1000
20	)
21	ELSE 0
22	END AS slow_queries_count;
23	`
24	}
25
26	export const useSlowQueriesCount = (_projectRef?: string, refreshKey: number = 0) => {
27	// refreshKey is used in useMemo to force recomputation when refresh is triggered
28	const sql = useMemo(
29	() => buildSlowQueriesCountSql(),
30	// eslint-disable-next-line react-hooks/exhaustive-deps
31	[refreshKey]
32	)
33
34	const { data, isLoading, error } = useDbQuery({
35	sql,
36	})
37
38	const slowQueriesCount = useMemo(() => {
39	if (!data || !Array.isArray(data) || data.length === 0) {
40	return 0
41	}
42	return data[0]?.slow_queries_count ?? 0
43	}, [data])
44
45	return {
46	slowQueriesCount,
47	isLoading,
48	error,
49	}
50	}