File size: 4,865 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
-- ============================================================================
-- COMPLETE INDEX OPTIMIZATIONS - RUN VIA PSQL
-- ============================================================================
-- This file contains ALL indexes including messages table (requires CONCURRENTLY)
-- 
-- HOW TO RUN:
-- 1. Connect: psql "your-connection-string"
-- 2. Paste this entire file and press Enter
-- ============================================================================

-- Disable statement timeout for large index creation
SET statement_timeout = '0';

-- ============================================================================
-- PRIORITY 1: Critical High-Frequency Queries
-- ============================================================================

CREATE INDEX IF NOT EXISTS idx_agent_runs_thread_status 
ON agent_runs(thread_id, status);

CREATE INDEX IF NOT EXISTS idx_agent_runs_thread_created_desc 
ON agent_runs(thread_id, created_at DESC);

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_messages_thread_created_desc 
ON messages(thread_id, created_at DESC);

CREATE INDEX IF NOT EXISTS idx_threads_account_created_desc 
ON threads(account_id, created_at DESC);

CREATE INDEX IF NOT EXISTS idx_credit_ledger_account_type_created_desc 
ON credit_ledger(account_id, type, created_at DESC);

-- ============================================================================
-- PRIORITY 2: High-Impact Queries
-- ============================================================================

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_messages_thread_llm_created 
ON messages(thread_id, created_at) 
WHERE is_llm_message = TRUE;

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_messages_thread_type_created_desc 
ON messages(thread_id, type, created_at DESC);

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_messages_thread_optimized_types_created_desc 
ON messages(thread_id, created_at DESC) 
WHERE type IN ('user', 'tool', 'assistant');

CREATE INDEX IF NOT EXISTS idx_credit_ledger_account_created_debit 
ON credit_ledger(account_id, created_at DESC) 
WHERE amount < 0;

CREATE INDEX IF NOT EXISTS idx_agent_triggers_composio_active 
ON agent_triggers((config->>'composio_trigger_id'), is_active) 
WHERE trigger_type = 'webhook';

CREATE INDEX IF NOT EXISTS idx_agents_account_created_desc 
ON agents(account_id, created_at DESC);

CREATE INDEX IF NOT EXISTS idx_agents_account_updated_desc 
ON agents(account_id, updated_at DESC);

CREATE INDEX IF NOT EXISTS idx_agent_versions_agent_version_desc 
ON agent_versions(agent_id, version_number DESC);

CREATE INDEX IF NOT EXISTS idx_agent_templates_public_download_published_desc 
ON agent_templates(is_public, download_count DESC, marketplace_published_at DESC);

CREATE INDEX IF NOT EXISTS idx_agent_templates_creator_created_desc 
ON agent_templates(creator_id, created_at DESC);

-- ============================================================================
-- PRIORITY 3: Medium-Impact Optimizations
-- ============================================================================

CREATE INDEX IF NOT EXISTS idx_agent_runs_thread_agent_created_desc 
ON agent_runs(thread_id, agent_id, created_at DESC);

CREATE INDEX IF NOT EXISTS idx_agent_runs_status_created_desc 
ON agent_runs(status, created_at DESC);

CREATE INDEX IF NOT EXISTS idx_threads_account_updated_desc 
ON threads(account_id, updated_at DESC);

CREATE INDEX IF NOT EXISTS idx_threads_project_account 
ON threads(project_id, account_id);

CREATE INDEX IF NOT EXISTS idx_agent_templates_public_kortix_created_desc 
ON agent_templates(is_public, is_kortix_team, created_at DESC);

CREATE INDEX IF NOT EXISTS idx_agent_templates_name_trgm 
ON agent_templates USING gin (name gin_trgm_ops);

-- ============================================================================
-- PRIORITY 4: Low-Impact but Beneficial
-- ============================================================================

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_messages_thread_type_llm_summary_created_desc 
ON messages(thread_id, type, is_llm_message, created_at DESC) 
WHERE type = 'summary';

-- ============================================================================
-- Analyze tables to update statistics for query planner
-- ============================================================================

ANALYZE agent_runs;
ANALYZE messages;
ANALYZE threads;
ANALYZE credit_ledger;
ANALYZE agent_triggers;
ANALYZE agents;
ANALYZE agent_versions;
ANALYZE agent_templates;

-- ============================================================================
-- Mark migration as applied (so supabase db push skips it)
-- ============================================================================

INSERT INTO supabase_migrations.schema_migrations (version, name) 
VALUES ('20251123153256', 'index_optimisations')
ON CONFLICT (version) DO NOTHING;