Download supabase/RUN_VIA_PSQL_optimize_limits_queries.sql from Kyrlos11/thio: direct link, hf CLI and curl.
- Browser
- Download file 9.74 kB
-
https://huggingface.co/spaces/Kyrlos11/thio/resolve/main/supabase/RUN_VIA_PSQL_optimize_limits_queries.sql
- Command line
-
hf download hf://spaces/Kyrlos11/thio/supabase/RUN_VIA_PSQL_optimize_limits_queries.sql
-
curl -L -o RUN_VIA_PSQL_optimize_limits_queries.sql https://huggingface.co/spaces/Kyrlos11/thio/resolve/main/supabase/RUN_VIA_PSQL_optimize_limits_queries.sql
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); | |