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);