Performance Auditor

Capabilities

• Analyze query performance
• Suggest index improvements
• Review React Query usage
• Identify expensive AI calls
• Monitor database metrics

Performance Targets

Usage Examples

Example 1: Analyze Query Performance

User: "Check performance of verse lookup queries"

Assistant:

-- Test single verse lookup performance
EXPLAIN ANALYZE
SELECT * FROM public.get_verse_by_ref(
  'John', 3, 16, 'finstlk201', 'fi'
);

-- Test chapter lookup performance
EXPLAIN ANALYZE
SELECT * FROM public.get_chapter_by_ref(
  'Genesis', 1, 'finstlk201', 'fi'
);

-- Test search performance
EXPLAIN ANALYZE
SELECT * FROM public.search_text('rakkaus', 'finstlk201', 50);

-- Check execution time statistics
SELECT
  query,
  calls,
  total_exec_time,
  mean_exec_time,
  max_exec_time
FROM pg_stat_statements
WHERE query LIKE '%get_verse_by_ref%'
  OR query LIKE '%get_chapter_by_ref%'
  OR query LIKE '%search_text%'
ORDER BY mean_exec_time DESC
LIMIT 10;

Example 2: Identify Missing Indexes

User: "Find queries that need indexes"

Assistant:

-- Check for sequential scans (potential missing indexes)
SELECT
  schemaname,
  tablename,
  seq_scan,
  seq_tup_read,
  idx_scan,
  seq_tup_read / NULLIF(seq_scan, 0) as avg_seq_tup_read,
  CASE
    WHEN seq_scan > idx_scan THEN 'Consider index'
    ELSE 'OK'
  END as recommendation
FROM pg_stat_user_tables
WHERE schemaname IN ('public', 'bible_schema')
ORDER BY seq_scan DESC
LIMIT 20;

-- Check index usage
SELECT
  schemaname,
  tablename,
  indexname,
  idx_scan,
  idx_tup_read,
  idx_tup_fetch
FROM pg_stat_user_indexes
WHERE schemaname IN ('public', 'bible_schema')
ORDER BY idx_scan ASC
LIMIT 20;

-- Suggest indexes for frequently queried columns
SELECT
  'CREATE INDEX idx_' || table_name || '_' || column_name ||
  ' ON ' || table_schema || '.' || table_name || '(' || column_name || ');' as suggested_index
FROM information_schema.columns
WHERE table_schema IN ('public', 'bible_schema')
  AND table_name IN (
    SELECT tablename
    FROM pg_stat_user_tables
    WHERE seq_scan > 1000 AND seq_scan > idx_scan
  )
  AND column_name IN ('user_id', 'verse_id', 'created_at', 'osis', 'status');

Example 3: Monitor AI Call Performance

User: "Analyze AI call latency and costs"

Assistant:

-- AI performance metrics (last 7 days)
SELECT
  feature,
  ai_vendor,
  ai_model,
  COUNT(*) as call_count,
  ROUND(AVG(latency_ms), 2) as avg_latency_ms,
  ROUND(MAX(latency_ms), 2) as max_latency_ms,
  ROUND(MIN(latency_ms), 2) as min_latency_ms,
  ROUND(AVG(total_tokens), 0) as avg_tokens,
  ROUND(SUM(cost_usd), 4) as total_cost_usd,
  ROUND(AVG(cost_usd), 6) as avg_cost_per_call,
  COUNT(CASE WHEN status = 'error' THEN 1 END) as errors,
  ROUND(100.0 * COUNT(CASE WHEN status = 'error' THEN 1 END) / COUNT(*), 2) as error_rate_percent
FROM bible_schema.ai_usage_logs
WHERE created_at > NOW() - INTERVAL '7 days'
GROUP BY feature, ai_vendor, ai_model
ORDER BY call_count DESC;

-- Slowest AI calls (last 24 hours)
SELECT
  feature,
  ai_model,
  latency_ms,
  total_tokens,
  cost_usd,
  context_ref,
  created_at,
  status,
  error_message
FROM bible_schema.ai_usage_logs
WHERE created_at > NOW() - INTERVAL '24 hours'
  AND status = 'success'
ORDER BY latency_ms DESC
LIMIT 20;

-- High-cost AI calls
SELECT
  feature,
  ai_model,
  cost_usd,
  total_tokens,
  latency_ms,
  context_ref,
  created_at
FROM bible_schema.ai_usage_logs
WHERE created_at > NOW() - INTERVAL '7 days'
ORDER BY cost_usd DESC
LIMIT 20;

-- AI cache effectiveness (for translations)
WITH cache_stats AS (
  SELECT
    COUNT(*) as total_requests,
    COUNT(CASE WHEN source IN ('topic', 'manual') THEN 1 END) as cache_hits,
    COUNT(CASE WHEN source = 'ai' THEN 1 END) as ai_calls
  FROM bible_schema.term_translations
  WHERE created_at > NOW() - INTERVAL '30 days'
)
SELECT
  total_requests,
  cache_hits,
  ai_calls,
  ROUND(100.0 * cache_hits / total_requests, 2) as cache_hit_rate_percent,
  ROUND(100.0 * ai_calls / total_requests, 2) as ai_call_rate_percent
FROM cache_stats;

Example 4: Optimize React Query Usage

User: "Review React Query configuration for optimal caching"

Assistant:

Create performance monitoring hook:

// hooks/usePerformanceMonitor.ts
import { useEffect } from 'react';
import { useQueryClient } from '@tanstack/react-query';

export const usePerformanceMonitor = () => {
  const queryClient = useQueryClient();

  useEffect(() => {
    // Log cache statistics
    const cache = queryClient.getQueryCache();
    const queries = cache.getAll();

    console.log('React Query Cache Stats:', {
      totalQueries: queries.length,
      activeQueries: queries.filter(q => q.state.fetchStatus === 'fetching').length,
      staleQueries: queries.filter(q => q.isStale()).length,
      cacheSize: JSON.stringify(queries).length / 1024 + ' KB'
    });

    // Monitor slow queries
    queries.forEach(query => {
      if (query.state.dataUpdateCount > 0) {
        const lastFetchTime = query.state.dataUpdatedAt - (query.state.dataUpdatedAt - 1000);
        if (lastFetchTime > 1000) {
          console.warn('Slow query detected:', {
            queryKey: query.queryKey,
            fetchTime: lastFetchTime + 'ms'
          });
        }
      }
    });
  }, [queryClient]);
};

// Usage in main component
import { usePerformanceMonitor } from '@/hooks/usePerformanceMonitor';

function App() {
  usePerformanceMonitor(); // Monitor in dev mode
  // ...
}

Optimize query configuration:

// Recommended React Query configuration
const queryClient = new QueryClient({
  defaultOptions: {
    queries: {
      staleTime: 5 * 60 * 1000, // 5 minutes
      gcTime: 10 * 60 * 1000, // 10 minutes (formerly cacheTime)
      retry: 1,
      refetchOnWindowFocus: false,
      refetchOnMount: false,
    },
  },
});

// Bible verses - longer cache (rarely change)
const { data: verses } = useQuery({
  queryKey: ['chapter', book, chapter, version],
  queryFn: () => fetchChapter(book, chapter, version),
  staleTime: 30 * 60 * 1000, // 30 minutes
  gcTime: 60 * 60 * 1000, // 1 hour
});

// User data - shorter cache (may change frequently)
const { data: bookmarks } = useQuery({
  queryKey: ['bookmarks', userId],
  queryFn: () => fetchBookmarks(userId),
  staleTime: 1 * 60 * 1000, // 1 minute
  gcTime: 5 * 60 * 1000, // 5 minutes
});

// AI results - cache aggressively
const { data: translation } = useQuery({
  queryKey: ['translation', term],
  queryFn: () => translateTerm(term),
  staleTime: Infinity, // Never goes stale
  gcTime: 24 * 60 * 60 * 1000, // 24 hours
});

Example 5: Identify N+1 Query Problems

User: "Find N+1 query issues in the codebase"

Assistant:

Common N+1 patterns to avoid:

// ❌ BAD: N+1 queries
const { data: topics } = useQuery(['topics'], fetchTopics);

// For each topic, fetch verses separately
topics?.forEach(topic => {
  const { data: verses } = useQuery(
    ['verses', topic.id],
    () => fetchTopicVerses(topic.id) // N queries!
  );
});

// ✅ GOOD: Single query with JOIN
const { data: topicsWithVerses } = useQuery(
  ['topics-with-verses'],
  async () => {
    const { data } = await supabase
      .from('topics')
      .select(`
        *,
        topic_verses(
          verse:verses(*)
        )
      `);
    return data;
  }
);

// ✅ BETTER: Use RPC function
const { data: topicsWithVerses } = useQuery(
  ['topics-with-verses'],
  async () => {
    const { data } = await supabase.rpc('get_topics_with_verses');
    return data;
  }
);

Detect N+1 in logs:

// Add query logging in development
if (process.env.NODE_ENV === 'development') {
  const queryClient = new QueryClient({
    defaultOptions: {
      queries: {
        onSuccess: (data, query) => {
          console.log('Query executed:', {
            queryKey: query.queryKey,
            dataSize: JSON.stringify(data).length,
            timestamp: Date.now()
          });
        }
      }
    }
  });

  // Alert on rapid sequential queries
  let queryTimes: number[] = [];
  setInterval(() => {
    if (queryTimes.length > 10) {
      console.warn('Potential N+1 detected: ', queryTimes.length, 'queries in short succession');
    }
    queryTimes = [];
  }, 1000);
}

Performance Optimization Checklist

Database

• Indexes on foreign keys
• Indexes on frequently filtered columns
• GIN indexes for full-text search
• Composite indexes for common query patterns
• VACUUM and ANALYZE run regularly
• Connection pooling configured

React Query

• Appropriate staleTime for each query type
• No unnecessary refetches
• Prefetching for predictable navigation
• Query invalidation on mutations
• No N+1 query patterns
• Cache size monitored

AI Calls

• Caching enabled for translations
• Appropriate model selection (cost vs performance)
• Token limits set
• Timeout handling
• Retry logic with exponential backoff
• Batch processing where possible

Frontend

• Code splitting for routes
• Lazy loading components
• Image optimization
• Debouncing for search inputs
• Virtual scrolling for long lists
• Service worker for caching

Monitoring Tools

Supabase Dashboard

• Database → Performance
• Database → Query Performance
• Edge Functions → Logs

Browser DevTools

// Measure page load performance
window.addEventListener('load', () => {
  const perfData = performance.getEntriesByType('navigation')[0];
  console.log('Page Performance:', {
    domContentLoaded: perfData.domContentLoadedEventEnd - perfData.fetchStart,
    loadComplete: perfData.loadEventEnd - perfData.fetchStart,
    firstPaint: performance.getEntriesByName('first-contentful-paint')[0]?.startTime
  });
});

// Monitor API calls
const originalFetch = window.fetch;
window.fetch = async (...args) => {
  const start = performance.now();
  const result = await originalFetch(...args);
  const duration = performance.now() - start;

  if (duration > 500) {
    console.warn('Slow API call:', {
      url: args[0],
      duration: duration.toFixed(2) + 'ms'
    });
  }

  return result;
};

Related Documentation

• See Docs/02-DESIGN.md for architecture
• See Docs/05-DEV.md for query patterns
• See Docs/06-AI-ARCHITECTURE.md for AI optimization