Skip to main content

Knowledge > Runbooks > Technical Ops > Investigate and Resolve Slow Database Queries

Investigate and Resolve Slow Database Queries

Identify the root cause of slow Supabase queries and apply the correct fix without introducing new problems.

Prerequisites​

  • Access to Supabase SQL editor (https://supabase.com/dashboard/project/wrwkszmobuhvcfjipasi/sql)
  • Access to Vercel logs (vercel logs --tail or Vercel dashboard)
  • The slow query or route identified (from error reports, user complaint, or monitoring)

Steps​

  1. Identify the slow query

    Check each source in order:

    • ops_error_reports table for timeout or performance errors: 
      SELECT route, message, created_at FROM ops_error_reports
      WHERE message ILIKE '%timeout%' OR message ILIKE '%slow%'
      ORDER BY created_at DESC LIMIT 20;
    • Vercel function logs: vercel logs --tail (filter for the suspected route)
    • Supabase Dashboard → Database → Query Performance (shows slow queries)

  2. Run EXPLAIN ANALYZE on the query

    Wrap the suspect query with EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT):

    EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
    SELECT * FROM churches
    WHERE slug = 'grace-community-church'
    LIMIT 1;

    Look for: Seq Scan (bad on large tables), high actual time, large rows estimates.

  3. Check for the most common issues

    Missing directory_visible=true filter on churches:

    -- BAD — scans 261K rows
    SELECT * FROM churches WHERE slug = 'example-church';

    -- GOOD — scans 218K visible rows, can use partial index
    SELECT * FROM churches WHERE slug = 'example-church' AND directory_visible = true;

    Missing pagination on large tables:

    -- BAD — may return or process thousands of rows
    SELECT * FROM unified_rag_content WHERE church_id = '...';

    -- GOOD — paginate with .range()
    SELECT * FROM unified_rag_content WHERE church_id = '...'
    ORDER BY created_at DESC
    LIMIT 1000 OFFSET 0;

    Missing indexes on common lookup columns:

    -- Check existing indexes on a table
    SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'churches';

    -- Common columns that should have indexes:
    -- churches: slug, directory_visible, denomination_id
    -- unified_rag_content: church_id, content_type, embedding (vector index)
    -- premium_churches: church_id, stripe_subscription_id
    -- voice_call_logs: church_id, created_at

  4. Add a missing index

    Always use CONCURRENTLY to avoid locking the table during index creation:

    CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_churches_slug_visible
      ON churches (slug)
      WHERE directory_visible = true;

    For unified_rag_content full-text or vector queries, check if a vector index exists:

    SELECT indexname FROM pg_indexes
    WHERE tablename = 'unified_rag_content' AND indexname LIKE '%embed%';

  5. Verify query time improvement

    Re-run EXPLAIN ANALYZE after adding the index:

    EXPLAIN (ANALYZE, BUFFERS)
    SELECT * FROM churches
    WHERE slug = 'grace-community-church' AND directory_visible = true;

    Confirm: Index Scan replaces Seq Scan, actual time drops significantly.

  6. Fix the application query if needed

    If the slow query comes from application code, locate it in the codebase:

    C:\dev\churchwiseai-web\src\    (ChurchWiseAI routes)
    C:\dev\pewsearch\web\src\       (PewSearch routes)
    C:\dev\sermon-illustrations\src\ (ITW routes)

    Update the query to include the correct filter, pagination, or index hint.

  7. Commit and deploy the fix

    For index-only fixes: commit the migration file (see supabase-migration.md). For code fixes: commit to a feature branch and deploy via standard process.

Verification​

  • EXPLAIN ANALYZE shows Index Scan not Seq Scan on the target table
  • Route response time drops to under 500ms (check Vercel function duration)
  • No new entries in ops_error_reports for the affected route

See Also​