-- Query Performance Monitoring for Jellyfin PostgreSQL Database
-- Use these queries to identify slow operations and monitor index usage
\pset pager off

-- 1. Show slow queries currently running (taking more than 5 seconds)
SELECT 
    pid,
    now() - query_start AS duration,
    state,
    wait_event_type,
    wait_event,
    LEFT(query, 100) AS query_preview
FROM pg_stat_activity
WHERE state != 'idle'
  AND query NOT LIKE '%pg_stat_activity%'
  AND (now() - query_start) > interval '5 seconds'
ORDER BY duration DESC;

-- 2. Show index usage statistics for BaseItems table
SELECT
    schemaname,
    reltablename,
    indexname,
    idx_scan AS index_scans,
    idx_tup_read AS tuples_read,
    idx_tup_fetch AS tuples_fetched,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
    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_level
FROM pg_stat_user_indexes
WHERE tablename = 'BaseItems'
  AND schemaname = 'library'
ORDER BY idx_scan DESC, indexname;

-- 3. Find missing indexes (sequential scans on BaseItems)
SELECT
    schemaname,
    reltablename,
    seq_scan AS sequential_scans,
    seq_tup_read AS rows_read_sequentially,
    idx_scan AS index_scans,
    n_tup_ins AS rows_inserted,
    n_tup_upd AS rows_updated,
    n_tup_del AS rows_deleted,
    CASE 
        WHEN seq_scan > 0 AND idx_scan = 0 THEN 'INDEX NEEDED'
        WHEN seq_scan > idx_scan THEN 'MORE INDEXES MAY HELP'
        ELSE 'INDEXES WORKING WELL'
    END AS recommendation
FROM pg_stat_user_tables
WHERE tablename = 'BaseItems'
  AND schemaname = 'library';

-- 4. Show table and index sizes
SELECT
    schemaname,
    reltablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS total_size,
    pg_size_pretty(pg_relation_size(schemaname||'.'||tablename)) AS table_size,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename) - pg_relation_size(schemaname||'.'||tablename)) AS indexes_size
FROM pg_stat_user_tables
WHERE tablename = 'BaseItems'
  AND schemaname = 'library';

-- 5. Show query statistics from pg_stat_statements (if extension is enabled)
-- Requires: CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SELECT
    LEFT(query, 80) AS query_preview,
    calls,
    ROUND(total_exec_time::numeric, 2) AS total_time_ms,
    ROUND(mean_exec_time::numeric, 2) AS avg_time_ms,
    ROUND(max_exec_time::numeric, 2) AS max_time_ms,
    rows
FROM pg_stat_statements
WHERE query LIKE '%BaseItems%'
  AND query NOT LIKE '%pg_stat%'
ORDER BY mean_exec_time DESC
LIMIT 20;

-- 6. Check for bloat in BaseItems table
SELECT
    schemaname,
    relname,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||relname)) AS total_size,
    n_dead_tup AS dead_tuples,
    n_live_tup AS live_tuples,
    ROUND(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_tuple_percent,
    last_vacuum,
    last_autovacuum,
    CASE
        WHEN ROUND(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) > 20 THEN 'VACUUM RECOMMENDED'
        WHEN ROUND(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) > 10 THEN 'VACUUM SOON'
        ELSE 'OK'
    END AS recommendation
FROM pg_stat_user_tables
WHERE relname = 'BaseItems'
  AND schemaname = 'library';

-- 7. Show cache hit ratio (should be > 99%)
SELECT
    schemaname,
    relname,
    heap_blks_read AS disk_reads,
    heap_blks_hit AS cache_hits,
    ROUND(100.0 * heap_blks_hit / NULLIF(heap_blks_hit + heap_blks_read, 0), 2) AS cache_hit_ratio
FROM pg_statio_user_tables
WHERE relname = 'BaseItems'
  AND schemaname = 'library';

-- 8. Reset statistics (useful after making changes to get fresh data)
-- UNCOMMENT TO USE:
-- SELECT pg_stat_reset();
-- SELECT pg_stat_statements_reset();  -- If pg_stat_statements is enabled