DTTH / supabase /RUN_VIA_PSQL_optimize_limits_queries.sql
Kyrlos11's picture
Upload 281 files
93d4e5d verified
Raw History Blame Contribute Delete
9.74 kB
-- ============================================================================
-- COMPREHENSIVE ZERO DOWNTIME DATABASE OPTIMIZATION
-- ============================================================================
-- Run MANUALLY via psql or Supabase SQL Editor (NOT via migrations)
-- CONCURRENTLY indexes don't lock tables but can't run in transactions
--
-- Usage: psql $DATABASE_URL -f RUN_VIA_PSQL_optimize_limits_queries.sql
--
-- Based on Supabase slow query analysis (2.5M+ calls analyzed)
-- ============================================================================
-- ============================================================================
-- MESSAGES TABLE - CRITICAL (40ms avg, 8 SECOND max spikes!)
-- ============================================================================
-- Total impact: ~135K calls/period, 2.5 MILLION ms total execution time
-- Query: WHERE thread_id = $1 AND is_llm_message = $4 ORDER BY created_at ASC
-- 52K calls, 19.76ms avg, 7.9 SECOND max (!!!)
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_messages_thread_llm_created
ON messages(thread_id, is_llm_message, created_at);
-- Query: WHERE thread_id = $1 AND type = ANY($2) ORDER BY created_at ASC
-- 22K calls, 40.85ms avg, 8.8 SECOND max (!!!)
-- Existing idx_messages_thread_type_created_at has DESC, we need ASC too
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_messages_thread_type_created
ON messages(thread_id, type, created_at);
-- ============================================================================
-- AGENT_RUNS TABLE - Supabase Index Advisor Recommendation
-- ============================================================================
-- Query: agent_runs JOIN threads WHERE status = 'running' AND started_at >= $5
-- 73K calls, 3.63ms avg, 4.4 SECOND max
-- Supabase Index Advisor: "CREATE INDEX ON public.agent_runs USING btree (started_at)"
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_agent_runs_started_at
ON agent_runs(started_at DESC);
-- Partial index for running status checks (limits API)
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_agent_runs_status_thread
ON agent_runs(status, thread_id)
WHERE status = 'running';
-- ============================================================================
-- BASEJUMP ACCOUNT_USER - Supabase Index Advisor Recommendation
-- ============================================================================
-- Query: basejump.accounts JOIN account_user WHERE user_id = $1
-- 16K calls, 34.42ms avg (consistent latency, high volume)
-- Supabase Index Advisor: "CREATE INDEX ON basejump.account_user USING btree (user_id)"
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_basejump_account_user_user_id
ON basejump.account_user(user_id);
-- ============================================================================
-- GET_STALE_PROJECTS_FOR_CATEGORIZATION - EXTREMELY SLOW (3.2 SECOND avg!)
-- ============================================================================
-- Query: Subquery does MAX(updated_at), MAX(created_at) GROUP BY project_id
-- 278 calls, 3233ms avg (!!) - Full table scan on threads
-- Index for MAX(updated_at) per project - supports LATERAL ORDER BY updated_at DESC LIMIT 1
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_threads_project_updated
ON threads(project_id, updated_at DESC);
-- Index for MAX(created_at) per project - supports LATERAL ORDER BY created_at DESC LIMIT 1
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_threads_project_created
ON threads(project_id, created_at DESC);
-- Fast lookup for categorization filtering
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_projects_last_categorized
ON projects(last_categorized_at)
WHERE last_categorized_at IS NOT NULL;
-- ============================================================================
-- THREADS TABLE
-- ============================================================================
-- Query: UPDATE threads SET status = $1 ... WHERE thread_id = $5
-- 20K calls, 18.89ms avg
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_threads_status
ON threads(status);
-- Thread access authorization (optimized JOIN queries)
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_threads_account_project
ON threads(thread_id, account_id, project_id);
-- ============================================================================
-- AGENTS TABLE - Limits API Optimization
-- ============================================================================
-- Fast count of non-Suna-default agents per account
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_agents_account_non_suna
ON agents(account_id)
WHERE (metadata->>'is_suna_default')::boolean IS NOT TRUE;
-- ============================================================================
-- AGENT_TRIGGERS TABLE
-- ============================================================================
-- Trigger limits check grouped by type
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_agent_triggers_agent_type
ON agent_triggers(agent_id, trigger_type);
-- ============================================================================
-- RESOURCES & PROJECTS - Sandbox Access Optimization
-- ============================================================================
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_resources_external_id_type
ON resources(external_id, type);
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_projects_sandbox_resource
ON projects(sandbox_resource_id)
WHERE sandbox_resource_id IS NOT NULL;
-- ============================================================================
-- SYSTEM SCHEMA INDEXES (May require superuser or may already exist)
-- ============================================================================
-- These target Supabase-managed schemas. Run separately if needed.
-- CRON JOB_RUN_DETAILS - 188 SECOND avg (cleanup query)
-- Query: UPDATE cron.job_run_details SET status = $1 WHERE status IN ($3,$4)
-- 2 calls, 188856ms avg (!!) - This is internal cron cleanup
-- UNCOMMENT IF NEEDED (may require elevated permissions):
-- CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_cron_job_run_details_status
-- ON cron.job_run_details(status);
-- REALTIME SUBSCRIPTION - High volume insert
-- Query: INSERT INTO realtime.subscription with conflict handling
-- 220K calls, 1.59ms avg - Usually has appropriate indexes already
-- UNCOMMENT IF NEEDED (may require elevated permissions):
-- CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_realtime_subscription_lookup
-- ON realtime.subscription(subscription_id, entity);
-- ============================================================================
-- OPTIMIZED get_stale_projects_for_categorization FUNCTION
-- ============================================================================
-- Original: 3.2 SECOND avg due to full GROUP BY scan on threads
-- Optimized: Two LATERAL subqueries use index scans for MAX values
CREATE OR REPLACE FUNCTION get_stale_projects_for_categorization(
stale_threshold TIMESTAMP WITH TIME ZONE,
max_count INT DEFAULT 50
)
RETURNS TABLE (project_id UUID)
LANGUAGE sql
STABLE
SECURITY DEFINER
SET search_path = ''
AS $$
SELECT p.project_id
FROM public.projects p
-- Get MAX(updated_at) using index scan
INNER JOIN LATERAL (
SELECT t.updated_at as last_activity
FROM public.threads t
WHERE t.project_id = p.project_id
ORDER BY t.updated_at DESC
LIMIT 1
) t_updated ON true
-- Get MAX(created_at) using index scan
INNER JOIN LATERAL (
SELECT t.created_at as newest_thread_created
FROM public.threads t
WHERE t.project_id = p.project_id
ORDER BY t.created_at DESC
LIMIT 1
) t_created ON true
WHERE t_updated.last_activity < stale_threshold
AND (p.last_categorized_at IS NULL OR p.last_categorized_at < t_updated.last_activity)
AND t_created.newest_thread_created > NOW() - INTERVAL '7 days'
ORDER BY
t_created.newest_thread_created DESC,
(p.last_categorized_at IS NULL) DESC
LIMIT max_count;
$$;
-- ============================================================================
-- OPTIMIZED update_thread_message_counts TRIGGER
-- ============================================================================
-- Add search_path for security
CREATE OR REPLACE FUNCTION update_thread_message_counts()
RETURNS TRIGGER
LANGUAGE plpgsql
SET search_path = ''
AS $$
BEGIN
IF TG_OP = 'INSERT' THEN
UPDATE public.threads SET
total_message_count = total_message_count + 1,
user_message_count = user_message_count + CASE WHEN NEW.type = 'user' THEN 1 ELSE 0 END
WHERE thread_id = NEW.thread_id;
RETURN NEW;
ELSIF TG_OP = 'DELETE' THEN
UPDATE public.threads SET
total_message_count = GREATEST(total_message_count - 1, 0),
user_message_count = GREATEST(user_message_count - CASE WHEN OLD.type = 'user' THEN 1 ELSE 0 END, 0)
WHERE thread_id = OLD.thread_id;
RETURN OLD;
END IF;
RETURN NULL;
END;
$$;
-- ============================================================================
-- VERIFICATION QUERIES (run after indexes complete)
-- ============================================================================
-- Check new indexes were created:
-- SELECT indexname, tablename, indexdef
-- FROM pg_indexes
-- WHERE indexname LIKE 'idx_%'
-- ORDER BY tablename, indexname;
-- Check index usage after some traffic:
-- SELECT schemaname, relname, indexrelname, idx_scan, idx_tup_read
-- FROM pg_stat_user_indexes
-- WHERE indexrelname LIKE 'idx_%'
-- ORDER BY idx_scan DESC;
-- Test categorization function performance:
-- EXPLAIN ANALYZE SELECT * FROM get_stale_projects_for_categorization(NOW() - INTERVAL '30 minutes', 50);