113 lines
3.6 KiB
SQL
113 lines
3.6 KiB
SQL
-- Database Optimization Script
|
|
-- Date: January 4, 2026
|
|
-- Purpose: Remove redundant indexes, optimize queries, improve performance
|
|
|
|
-- ============================================
|
|
-- STEP 1: Remove Redundant Indexes
|
|
-- ============================================
|
|
|
|
-- Remove duplicate index on profile_song_keys
|
|
-- Keep: uq_profile_song_key (unique constraint)
|
|
-- Keep: idx_profile_song_keys (for lookups)
|
|
-- Remove: idx_profile_keys (exact duplicate)
|
|
DROP INDEX IF EXISTS idx_profile_keys;
|
|
|
|
-- Remove redundant single-column index on plan_songs
|
|
-- Keep: idx_plan_songs_order (composite index: plan_id, order_index)
|
|
-- Remove: idx_plan_songs_plan (redundant - composite index handles this)
|
|
DROP INDEX IF EXISTS idx_plan_songs_plan;
|
|
|
|
-- ============================================
|
|
-- STEP 2: Optimize Low-Cardinality Indexes
|
|
-- ============================================
|
|
|
|
-- Replace full index on users.active with partial index
|
|
-- Most users are active, so only index the exceptions
|
|
DROP INDEX IF EXISTS idx_user_active;
|
|
CREATE INDEX IF NOT EXISTS idx_user_inactive ON users (id) WHERE active = false;
|
|
|
|
-- ============================================
|
|
-- STEP 3: Add Composite Indexes for Common Queries
|
|
-- ============================================
|
|
|
|
-- Optimize query: "find plans by profile within date range"
|
|
CREATE INDEX IF NOT EXISTS idx_plan_profile_date ON plans (profile_id, date)
|
|
WHERE profile_id IS NOT NULL;
|
|
|
|
-- Add index for plan song lookups (if not exists)
|
|
CREATE INDEX IF NOT EXISTS idx_plan_songs_song ON plan_songs (song_id);
|
|
|
|
-- Add index for profile song reverse lookups (if not exists)
|
|
CREATE INDEX IF NOT EXISTS idx_profile_songs_song ON profile_songs (song_id);
|
|
|
|
-- ============================================
|
|
-- STEP 4: Verify Index Effectiveness
|
|
-- ============================================
|
|
|
|
-- Show all indexes after cleanup
|
|
SELECT
|
|
schemaname,
|
|
tablename,
|
|
indexname,
|
|
indexdef
|
|
FROM pg_indexes
|
|
WHERE schemaname = 'public'
|
|
ORDER BY tablename, indexname;
|
|
|
|
-- Show index sizes
|
|
SELECT
|
|
schemaname,
|
|
tablename,
|
|
indexname,
|
|
pg_size_pretty(pg_relation_size(indexrelid)) as index_size
|
|
FROM pg_stat_user_indexes
|
|
WHERE schemaname = 'public'
|
|
ORDER BY pg_relation_size(indexrelid) DESC;
|
|
|
|
-- ============================================
|
|
-- STEP 5: Analyze Tables for Query Planner
|
|
-- ============================================
|
|
|
|
-- Update statistics for query planner optimization
|
|
ANALYZE users;
|
|
ANALYZE profiles;
|
|
ANALYZE songs;
|
|
ANALYZE plans;
|
|
ANALYZE plan_songs;
|
|
ANALYZE profile_songs;
|
|
ANALYZE profile_song_keys;
|
|
ANALYZE biometric_credentials;
|
|
|
|
-- ============================================
|
|
-- VERIFICATION QUERIES
|
|
-- ============================================
|
|
|
|
-- Check for unused indexes (run after a week of production use)
|
|
-- SELECT
|
|
-- schemaname,
|
|
-- tablename,
|
|
-- indexname,
|
|
-- idx_scan as scans,
|
|
-- pg_size_pretty(pg_relation_size(indexrelid)) as size
|
|
-- FROM pg_stat_user_indexes
|
|
-- WHERE schemaname = 'public'
|
|
-- AND idx_scan = 0
|
|
-- AND indexrelid IS NOT NULL
|
|
-- ORDER BY pg_relation_size(indexrelid) DESC;
|
|
|
|
-- Check for missing indexes on foreign keys
|
|
-- SELECT
|
|
-- c.conname AS constraint_name,
|
|
-- t.relname AS table_name,
|
|
-- ARRAY_AGG(a.attname ORDER BY u.attposition) AS columns
|
|
-- FROM pg_constraint c
|
|
-- JOIN pg_class t ON c.conrelid = t.oid
|
|
-- JOIN pg_namespace n ON t.relnamespace = n.oid
|
|
-- JOIN LATERAL UNNEST(c.conkey) WITH ORDINALITY AS u(attnum, attposition) ON TRUE
|
|
-- JOIN pg_attribute a ON a.attrelid = t.oid AND a.attnum = u.attnum
|
|
-- WHERE c.contype = 'f'
|
|
-- AND n.nspname = 'public'
|
|
-- GROUP BY c.conname, t.relname;
|
|
|
|
COMMIT;
|