File size: 9,740 Bytes
22087d0 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 | -- ============================================================================
-- 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);
|