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