297 lines
10 KiB
SQL
297 lines
10 KiB
SQL
-- ============================================================================
|
|
-- Slow Query Analysis Script
|
|
-- Purpose: Identify and analyze slow-running queries (including 110-second ones)
|
|
-- Requirements: pg_stat_statements extension must be enabled
|
|
-- ============================================================================
|
|
\pset pager off
|
|
|
|
\timing on
|
|
\x auto
|
|
|
|
\echo '========================================='
|
|
\echo 'Jellyfin Slow Query Analysis'
|
|
\echo 'Generated: 2026-03-05'
|
|
\echo '========================================='
|
|
\echo ''
|
|
|
|
-- ============================================================================
|
|
-- 1. CHECK EXTENSION STATUS
|
|
-- ============================================================================
|
|
|
|
\echo '1. CHECKING pg_stat_statements EXTENSION'
|
|
\echo '-----------------------------------------'
|
|
|
|
SELECT
|
|
CASE
|
|
WHEN COUNT(*) > 0 THEN '✅ pg_stat_statements is installed'
|
|
ELSE '❌ pg_stat_statements is NOT installed - Run: CREATE EXTENSION pg_stat_statements;'
|
|
END AS status
|
|
FROM pg_extension
|
|
WHERE extname = 'pg_stat_statements';
|
|
|
|
\echo ''
|
|
|
|
-- ============================================================================
|
|
-- 2. TOP 20 SLOWEST QUERIES BY MAX EXECUTION TIME
|
|
-- ============================================================================
|
|
|
|
\echo '2. TOP 20 SLOWEST QUERIES (by max execution time)'
|
|
\echo '-----------------------------------------'
|
|
\echo 'These are the queries that have taken the longest time to execute'
|
|
\echo ''
|
|
|
|
SELECT
|
|
LEFT(query, 100) AS query_preview,
|
|
calls,
|
|
ROUND(mean_exec_time::numeric, 2) AS avg_ms,
|
|
ROUND(max_exec_time::numeric, 2) AS max_ms,
|
|
ROUND(min_exec_time::numeric, 2) AS min_ms,
|
|
ROUND(stddev_exec_time::numeric, 2) AS stddev_ms,
|
|
ROUND(total_exec_time::numeric, 2) AS total_ms,
|
|
ROUND((100.0 * total_exec_time / SUM(total_exec_time) OVER ())::numeric, 2) AS pct_total,
|
|
rows AS rows_returned
|
|
FROM pg_stat_statements
|
|
WHERE query NOT LIKE '%pg_stat_statements%'
|
|
AND query NOT LIKE '%pg_catalog%'
|
|
AND dbid = (SELECT oid FROM pg_database WHERE datname = current_database())
|
|
ORDER BY max_exec_time DESC
|
|
LIMIT 20;
|
|
|
|
\echo ''
|
|
|
|
-- ============================================================================
|
|
-- 3. TOP 20 SLOWEST QUERIES BY AVERAGE EXECUTION TIME
|
|
-- ============================================================================
|
|
|
|
\echo '3. TOP 20 SLOWEST QUERIES (by average execution time)'
|
|
\echo '-----------------------------------------'
|
|
\echo 'These queries consistently take a long time'
|
|
\echo ''
|
|
|
|
SELECT
|
|
LEFT(query, 100) AS query_preview,
|
|
calls,
|
|
ROUND(mean_exec_time::numeric, 2) AS avg_ms,
|
|
ROUND(max_exec_time::numeric, 2) AS max_ms,
|
|
ROUND(total_exec_time::numeric, 2) AS total_ms,
|
|
ROUND((100.0 * total_exec_time / SUM(total_exec_time) OVER ())::numeric, 2) AS pct_total,
|
|
rows AS rows_returned,
|
|
CASE
|
|
WHEN calls > 0 THEN ROUND((rows::numeric / calls), 2)
|
|
ELSE 0
|
|
END AS avg_rows_per_call
|
|
FROM pg_stat_statements
|
|
WHERE query NOT LIKE '%pg_stat_statements%'
|
|
AND query NOT LIKE '%pg_catalog%'
|
|
AND dbid = (SELECT oid FROM pg_database WHERE datname = current_database())
|
|
AND calls > 1 -- Only queries called multiple times
|
|
ORDER BY mean_exec_time DESC
|
|
LIMIT 20;
|
|
|
|
\echo ''
|
|
|
|
-- ============================================================================
|
|
-- 4. QUERIES WITH HIGHEST TOTAL TIME (Performance Impact)
|
|
-- ============================================================================
|
|
|
|
\echo '4. QUERIES WITH HIGHEST TOTAL TIME'
|
|
\echo '-----------------------------------------'
|
|
\echo 'These queries consume the most total database time'
|
|
\echo ''
|
|
|
|
SELECT
|
|
LEFT(query, 100) AS query_preview,
|
|
calls,
|
|
ROUND(mean_exec_time::numeric, 2) AS avg_ms,
|
|
ROUND(max_exec_time::numeric, 2) AS max_ms,
|
|
ROUND(total_exec_time::numeric, 2) AS total_ms,
|
|
ROUND((100.0 * total_exec_time / SUM(total_exec_time) OVER ())::numeric, 2) AS pct_total,
|
|
ROUND((total_exec_time / 1000 / 60)::numeric, 2) AS total_minutes
|
|
FROM pg_stat_statements
|
|
WHERE query NOT LIKE '%pg_stat_statements%'
|
|
AND query NOT LIKE '%pg_catalog%'
|
|
AND dbid = (SELECT oid FROM pg_database WHERE datname = current_database())
|
|
ORDER BY total_exec_time DESC
|
|
LIMIT 20;
|
|
|
|
\echo ''
|
|
|
|
-- ============================================================================
|
|
-- 5. BASEITEMS QUERIES SPECIFICALLY (Main Problem Area)
|
|
-- ============================================================================
|
|
|
|
\echo '5. BASEITEMS TABLE QUERIES ANALYSIS'
|
|
\echo '-----------------------------------------'
|
|
\echo 'Focusing on BaseItems table (where 110-second queries occur)'
|
|
\echo ''
|
|
|
|
SELECT
|
|
LEFT(query, 150) AS query_preview,
|
|
calls,
|
|
ROUND(mean_exec_time::numeric, 2) AS avg_ms,
|
|
ROUND(max_exec_time::numeric, 2) AS max_ms,
|
|
ROUND(min_exec_time::numeric, 2) AS min_ms,
|
|
ROUND(total_exec_time::numeric, 2) AS total_ms,
|
|
rows AS rows_returned,
|
|
CASE
|
|
WHEN calls > 0 THEN ROUND((rows::numeric / calls), 2)
|
|
ELSE 0
|
|
END AS avg_rows_per_call,
|
|
CASE
|
|
WHEN max_exec_time > 100000 THEN '🔥 CRITICAL (>100s)'
|
|
WHEN max_exec_time > 10000 THEN '⚠️ SLOW (>10s)'
|
|
WHEN max_exec_time > 1000 THEN '⚡ MODERATE (>1s)'
|
|
ELSE '✅ FAST'
|
|
END AS performance_rating
|
|
FROM pg_stat_statements
|
|
WHERE query ILIKE '%BaseItems%'
|
|
AND query NOT LIKE '%pg_stat_statements%'
|
|
AND query NOT LIKE '%pg_catalog%'
|
|
AND dbid = (SELECT oid FROM pg_database WHERE datname = current_database())
|
|
ORDER BY max_exec_time DESC;
|
|
|
|
\echo ''
|
|
|
|
-- ============================================================================
|
|
-- 6. PEOPLES AND ITEMVALUES QUERIES
|
|
-- ============================================================================
|
|
|
|
\echo '6. PEOPLES AND ITEMVALUES QUERIES'
|
|
\echo '-----------------------------------------'
|
|
\echo 'Queries on high-sequential-scan tables'
|
|
\echo ''
|
|
|
|
SELECT
|
|
CASE
|
|
WHEN query ILIKE '%Peoples%' THEN 'Peoples'
|
|
WHEN query ILIKE '%ItemValues%' THEN 'ItemValues'
|
|
ELSE 'Other'
|
|
END AS table_name,
|
|
LEFT(query, 100) AS query_preview,
|
|
calls,
|
|
ROUND(mean_exec_time::numeric, 2) AS avg_ms,
|
|
ROUND(max_exec_time::numeric, 2) AS max_ms,
|
|
ROUND(total_exec_time::numeric, 2) AS total_ms,
|
|
rows AS rows_returned
|
|
FROM pg_stat_statements
|
|
WHERE (query ILIKE '%Peoples%' OR query ILIKE '%ItemValues%')
|
|
AND query NOT LIKE '%pg_stat_statements%'
|
|
AND query NOT LIKE '%pg_catalog%'
|
|
AND dbid = (SELECT oid FROM pg_database WHERE datname = current_database())
|
|
ORDER BY max_exec_time DESC
|
|
LIMIT 20;
|
|
|
|
\echo ''
|
|
|
|
-- ============================================================================
|
|
-- 7. QUERIES WITH MANY ROWS BUT FAST (Good Index Usage)
|
|
-- ============================================================================
|
|
|
|
\echo '7. EFFICIENT QUERIES (Many rows, fast execution)'
|
|
\echo '-----------------------------------------'
|
|
\echo 'These queries return many rows but execute quickly (good indexes!)'
|
|
\echo ''
|
|
|
|
SELECT
|
|
LEFT(query, 100) AS query_preview,
|
|
calls,
|
|
ROUND(mean_exec_time::numeric, 2) AS avg_ms,
|
|
rows AS total_rows_returned,
|
|
ROUND((rows::numeric / NULLIF(calls, 0))::numeric, 2) AS avg_rows_per_call,
|
|
ROUND((mean_exec_time / NULLIF((rows::numeric / NULLIF(calls, 0)), 0))::numeric, 4) AS ms_per_row
|
|
FROM pg_stat_statements
|
|
WHERE rows > 100000 -- Many rows returned
|
|
AND mean_exec_time < 100 -- But fast execution
|
|
AND calls > 10 -- Called frequently
|
|
AND query NOT LIKE '%pg_stat_statements%'
|
|
AND query NOT LIKE '%pg_catalog%'
|
|
AND dbid = (SELECT oid FROM pg_database WHERE datname = current_database())
|
|
ORDER BY rows DESC
|
|
LIMIT 10;
|
|
|
|
\echo ''
|
|
|
|
-- ============================================================================
|
|
-- 8. CURRENTLY RUNNING QUERIES (Real-time)
|
|
-- ============================================================================
|
|
|
|
\echo '8. CURRENTLY RUNNING QUERIES'
|
|
\echo '-----------------------------------------'
|
|
|
|
SELECT
|
|
pid,
|
|
usename,
|
|
application_name,
|
|
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 pid != pg_backend_pid()
|
|
AND query NOT LIKE '%pg_stat_activity%'
|
|
AND datname = current_database()
|
|
ORDER BY query_start ASC;
|
|
|
|
\echo ''
|
|
|
|
-- ============================================================================
|
|
-- 9. QUERY STATISTICS SUMMARY
|
|
-- ============================================================================
|
|
|
|
\echo '9. OVERALL QUERY STATISTICS SUMMARY'
|
|
\echo '-----------------------------------------'
|
|
|
|
SELECT
|
|
COUNT(*) AS total_unique_queries,
|
|
SUM(calls) AS total_calls,
|
|
ROUND(SUM(total_exec_time)::numeric / 1000 / 60, 2) AS total_exec_minutes,
|
|
ROUND(AVG(mean_exec_time)::numeric, 2) AS avg_query_time_ms,
|
|
ROUND(MAX(max_exec_time)::numeric, 2) AS slowest_query_ever_ms,
|
|
ROUND(MAX(max_exec_time)::numeric / 1000, 2) AS slowest_query_ever_seconds,
|
|
SUM(rows) AS total_rows_returned,
|
|
ROUND(AVG(rows::numeric / NULLIF(calls, 0))::numeric, 2) AS avg_rows_per_query
|
|
FROM pg_stat_statements
|
|
WHERE query NOT LIKE '%pg_stat_statements%'
|
|
AND query NOT LIKE '%pg_catalog%'
|
|
AND dbid = (SELECT oid FROM pg_database WHERE datname = current_database());
|
|
|
|
\echo ''
|
|
|
|
-- ============================================================================
|
|
-- 10. RECOMMENDATIONS
|
|
-- ============================================================================
|
|
|
|
\echo ''
|
|
\echo '========================================='
|
|
\echo 'RECOMMENDATIONS'
|
|
\echo '========================================='
|
|
\echo ''
|
|
\echo 'Based on the analysis above:'
|
|
\echo ''
|
|
\echo '1. CRITICAL QUERIES (>100s):'
|
|
\echo ' - Review queries marked 🔥 CRITICAL'
|
|
\echo ' - Check EXPLAIN ANALYZE for these queries'
|
|
\echo ' - Consider query rewrite or additional indexes'
|
|
\echo ''
|
|
\echo '2. SLOW QUERIES (>10s):'
|
|
\echo ' - Review queries marked ⚠️ SLOW'
|
|
\echo ' - Check if indexes are being used'
|
|
\echo ' - Run: EXPLAIN (ANALYZE, BUFFERS) <query>'
|
|
\echo ''
|
|
\echo '3. HIGH TOTAL TIME QUERIES:'
|
|
\echo ' - Even fast queries with many calls add up'
|
|
\echo ' - Consider caching frequently called queries'
|
|
\echo ' - Optimize connection pooling'
|
|
\echo ''
|
|
\echo '4. TO RESET STATISTICS:'
|
|
\echo ' Run: SELECT pg_stat_statements_reset();'
|
|
\echo ' (Useful after making optimization changes)'
|
|
\echo ''
|
|
\echo '5. TO GET FULL QUERY TEXT:'
|
|
\echo ' This script shows only first 100-150 characters'
|
|
\echo ' Query full text from pg_stat_statements table directly'
|
|
\echo ''
|
|
\echo '========================================='
|