pgsql-jellyfin/Jellyfin.Server/sql/performance_indexes_v2.sql

-- PostgreSQL Performance Indexes for Jellyfin
-- Based on actual schema analysis
-- Run this script to add supplementary performance indexes
-- Safe to run multiple times

\timing on
\echo '========================================';
\echo 'Jellyfin PostgreSQL Supplementary Indexes';
\echo '========================================';
\echo '';
\echo 'Your schema already has comprehensive indexes from EF Core migrations.';
\echo 'This script adds only missing supplementary indexes.';
\echo '';

-- ============================================================================
-- Check Existing Indexes
-- ============================================================================

\echo 'Analyzing existing indexes...';

DO $$
DECLARE
    idx_count int;
BEGIN
    SELECT count(*) INTO idx_count
    FROM pg_indexes
    WHERE schemaname = 'library' AND tablename = 'BaseItems';
    
    RAISE NOTICE 'Found % existing indexes on BaseItems table', idx_count;
END $$;

-- ============================================================================
-- BaseItems - Add Only Missing Supplementary Indexes
-- ============================================================================

\echo 'Adding supplementary BaseItems indexes...';

-- Composite index for filtered library browsing (type + virtual + parent)
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_indexes 
        WHERE schemaname = 'library' 
        AND tablename = 'BaseItems'
        AND indexname = 'idx_baseitems_type_isvirtualitem_topparentid'
    ) THEN
        CREATE INDEX CONCURRENTLY idx_baseitems_type_isvirtualitem_topparentid
        ON library."BaseItems" ("Type", "IsVirtualItem", "TopParentId")
        WHERE "IsVirtualItem" = false;
        RAISE NOTICE 'Created idx_baseitems_type_isvirtualitem_topparentid';
    ELSE
        RAISE NOTICE 'Index idx_baseitems_type_isvirtualitem_topparentid already exists';
    END IF;
END $$;

-- Index for folder hierarchy queries
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_indexes 
        WHERE schemaname = 'library' 
        AND tablename = 'BaseItems'
        AND indexname = 'idx_baseitems_topparentid_isfolder'
    ) THEN
        CREATE INDEX CONCURRENTLY idx_baseitems_topparentid_isfolder
        ON library."BaseItems" ("TopParentId", "IsFolder", "IsVirtualItem")
        WHERE "TopParentId" IS NOT NULL;
        RAISE NOTICE 'Created idx_baseitems_topparentid_isfolder';
    ELSE
        RAISE NOTICE 'Index idx_baseitems_topparentid_isfolder already exists';
    END IF;
END $$;

-- Index for recently added content with filters
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_indexes 
        WHERE schemaname = 'library' 
        AND tablename = 'BaseItems'
        AND indexname = 'idx_baseitems_datecreated_filtered'
    ) THEN
        CREATE INDEX CONCURRENTLY idx_baseitems_datecreated_filtered
        ON library."BaseItems" ("DateCreated" DESC, "Type", "IsVirtualItem")
        WHERE "DateCreated" IS NOT NULL AND "IsVirtualItem" = false;
        RAISE NOTICE 'Created idx_baseitems_datecreated_filtered';
    ELSE
        RAISE NOTICE 'Index idx_baseitems_datecreated_filtered already exists';
    END IF;
END $$;

-- ============================================================================
-- ItemValuesMap - Add Reverse Lookup Index
-- ============================================================================

\echo 'Checking ItemValuesMap indexes...';

DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_indexes 
        WHERE schemaname = 'library' 
        AND tablename = 'ItemValuesMap'
        AND indexname = 'idx_itemvaluesmap_itemvalueid_itemid'
    ) THEN
        CREATE INDEX CONCURRENTLY idx_itemvaluesmap_itemvalueid_itemid
        ON library."ItemValuesMap" ("ItemValueId", "ItemId");
        RAISE NOTICE 'Created idx_itemvaluesmap_itemvalueid_itemid';
    ELSE
        RAISE NOTICE 'Index idx_itemvaluesmap_itemvalueid_itemid already exists';
    END IF;
END $$;

-- ============================================================================
-- ActivityLogs - Add User-Specific Index
-- (Note: Table is ActivityLogs, not ActivityLog)
-- ============================================================================

\echo 'Checking ActivityLogs indexes...';

DO $$
BEGIN
    IF EXISTS (
        SELECT 1 FROM information_schema.tables 
        WHERE table_schema = 'activitylog' 
        AND table_name = 'ActivityLogs'
    ) THEN
        IF NOT EXISTS (
            SELECT 1 FROM pg_indexes 
            WHERE schemaname = 'activitylog' 
            AND tablename = 'ActivityLogs'
            AND indexname = 'idx_activitylogs_userid_datecreated'
        ) THEN
            CREATE INDEX CONCURRENTLY idx_activitylogs_userid_datecreated
            ON activitylog."ActivityLogs" ("UserId", "DateCreated" DESC)
            WHERE "UserId" IS NOT NULL;
            RAISE NOTICE 'Created idx_activitylogs_userid_datecreated';
        ELSE
            RAISE NOTICE 'Index idx_activitylogs_userid_datecreated already exists';
        END IF;
    ELSE
        RAISE NOTICE 'ActivityLogs table not found (expected: activitylog.ActivityLogs)';
    END IF;
END $$;

-- ============================================================================
-- Analyze Tables to Update Statistics
-- ============================================================================

\echo '';
\echo 'Updating table statistics...';

ANALYZE library."BaseItems";
ANALYZE library."BaseItemProviders";
ANALYZE library."UserData";
ANALYZE library."PeopleBaseItemMap";
ANALYZE library."ItemValuesMap";
ANALYZE library."ItemValues";
ANALYZE library."MediaStreamInfos";
ANALYZE library."Peoples";
ANALYZE library."Chapters";
ANALYZE library."KeyframeData";

DO $$
BEGIN
    IF EXISTS (
        SELECT 1 FROM information_schema.tables 
        WHERE table_schema = 'activitylog' 
        AND table_name = 'ActivityLogs'
    ) THEN
        ANALYZE activitylog."ActivityLogs";
        RAISE NOTICE 'ActivityLogs statistics updated';
    END IF;
END $$;

\echo 'Statistics updated.';

-- ============================================================================
-- Report Current Index Status
-- ============================================================================

\echo '';
\echo '========================================';
\echo 'Index Usage Report';
\echo '========================================';

-- Show all indexes and their usage
SELECT 
    schemaname,
    relname as tablename,
    indexrelname as indexname,
    pg_size_pretty(pg_relation_size(indexrelid)) as index_size,
    idx_scan as times_used,
    idx_tup_read as rows_read,
    idx_tup_fetch as rows_fetched,
    CASE 
        WHEN idx_scan = 0 THEN 'UNUSED'
        WHEN idx_scan < 10 THEN 'RARELY USED'
        WHEN idx_scan < 100 THEN 'OCCASIONALLY USED'
        ELSE 'FREQUENTLY USED'
    END as usage_category
FROM pg_stat_user_indexes
WHERE schemaname IN ('library', 'activitylog', 'users', 'authentication', 'displaypreferences')
ORDER BY schemaname, relname, idx_scan DESC;

\echo '';
\echo '========================================';
\echo 'Summary';
\echo '========================================';
\echo 'Your database already had comprehensive indexes from EF Core migrations.';
\echo 'Added supplementary composite indexes for specific query patterns.';
\echo '';
\echo 'Next steps:';
\echo '1. Monitor performance with: psql -U jellyfin -d jellyfin -f sql/diagnostics.sql';
\echo '2. Check query plans for slow queries';
\echo '3. Consider removing rarely-used indexes after 30 days';
\echo '4. Restart Jellyfin to clear connection pools';
\echo '';
\echo 'See SCHEMA_ANALYSIS.md for detailed index information.';
\echo '========================================';