pgsql-jellyfin/docs/DATABASE_ANALYSIS_REPORT.md

📊 Database Performance Analysis - Your Results

Summary

Date: 2026-02-28
Database: jellyfin (PostgreSQL 18)
Analysis: Post-supplementary indexes installation

---

✅ Good News

1. No Active Issues

   • 0 blocked queries
   • 0 long-running queries
   • No locks or contention

2. High Index Usage (Overall)

   • BaseItems: 99.60%
   • MediaStreamInfos: 99.65%
   • BaseItemProviders: 99.68%
   • ItemValuesMap: 99.81%
   • PeopleBaseItemMap: 99.99%

3. Low Bloat

   • BaseItems: 5.46% dead tuples (acceptable)
   • BaseItemProviders: 2.50% (excellent)
   • BaseItemImageInfos: 3.11% (excellent)

---

⚠️ Critical Issues Found

1. ItemValues Table - Sequential Scan Problem

Symptoms:

• 226,121 sequential scans
• 1,313,356,213 rows read (1.3 billion!) 😱
• Only 52.03% index usage
• Average 5,808 rows per scan

Impact: This table is being scanned repeatedly instead of using indexes. Huge performance hit.

Current Indexes:

• IX_ItemValues_Type_CleanValue
• IX_ItemValues_Type_Value
• PK_ItemValues

Problem: Missing indexes for common query patterns.

---

2. Peoples Table - High Sequential Scans

Symptoms:

• 19,320 sequential scans
• 918,704,169 rows read (918 million!)
• 94.14% index usage (good, but scans still high)
• Average 47,551 rows per scan

Current Indexes:

• IX_Peoples_Name
• PK_Peoples

Problem: Queries are doing table scans even with name index.

---

3. UserData Table - Unusual Pattern

Symptoms:

• 15,837,908 sequential scans (15.8 million!)
• Only 411,177 total rows read
• Average 0 rows per scan

Analysis: This is actually OKAY! The table is very small (3 rows), so seq scans are faster than index scans. PostgreSQL is making the right choice.

---

🔍 Supplementary Index Usage

Your newly created supplementary indexes have very low usage:

Index	Times Used	Rows Read	Status
idx_itemvaluesmap_itemvalueid_itemid	10	108	⚠️ Almost unused
idx_baseitems_datecreated_filtered	0	0	❌ Never used
IX_BaseItems_Type_TopParentId_IsVirtualItem_PresentationUnique~	6	57,709	⚠️ Rarely used

Why Low Usage?

1. Database Has Been Idle

   • Report shows 0 active connections
   • Indexes only get used during queries
   • Need to use Jellyfin to generate workload

2. Statistics Not Updated

   • Query planner may not know about new indexes
   • Needs ANALYZE to update

3. Query Patterns Don't Match

   • Indexes were designed for specific WHERE clauses
   • If Jellyfin doesn't use those patterns, indexes won't help

---

🎯 Recommended Actions

Immediate Actions:

1. Update Database Statistics ✅ DONE

ANALYZE VERBOSE library."BaseItems", library."ItemValues", library."ItemValuesMap", library."Peoples";

Status: Executed via terminal

2. Use Jellyfin to Generate Workload

The supplementary indexes target specific user interactions:

To test idx_baseitems_datecreated_filtered:

• Open Jellyfin web interface
• Navigate to "Recently Added" view
• Browse libraries
• Sort by date added

To test idx_baseitems_type_isvirtualitem_topparentid:

• Browse different library types (Movies, TV Shows, Music)
• Navigate folders
• Filter by library

To test idx_itemvaluesmap_itemvalueid_itemid:

• Filter by Genre
• Filter by Tags
• Filter by Studios
• Search by actor/director

3. Run Diagnostics Again After Use

& "C:\Program Files\PostgreSQL\18\bin\psql.exe" -U jellyfin -d jellyfin -f sql\diagnostics.sql > diagnostics_after_use.txt

Compare the results to see if indexes are being used.

---

Long-Term Actions:

1. Address ItemValues Sequential Scans

The ItemValues table needs better indexing. Common query patterns likely include:

Possible missing indexes:

-- For filtering items by multiple values
CREATE INDEX idx_itemvalues_type_value_cleanvalue 
ON library."ItemValues" ("Type", "Value", "CleanValue");

-- For reverse lookups (value to items)
CREATE INDEX idx_itemvalues_cleanvalue_type 
ON library."ItemValues" ("CleanValue", "Type");

Before creating, let me analyze actual query patterns by enabling query logging.

2. Monitor Peoples Table

The Peoples table has good index usage (94%) but still high scan counts. This suggests:

• Queries that can't use the name index (e.g., wildcard searches)
• Full table aggregations
• Queries using columns other than Name

Potential optimization:

-- If queries often filter by both name and type
CREATE INDEX idx_peoples_name_type 
ON library."Peoples" ("Name", "Type") 
WHERE "Name" IS NOT NULL;

3. Consider Removing Unused Indexes

These indexes have 0 uses and take up space:

Index	Size	Recommendation
PK_PeopleBaseItemMap	21 MB	Keep (Primary Key - needed for constraints)
IX_BaseItems_Path	15 MB	Monitor - may be used for file operations
IX_BaseItems_Type_TopParentId_Id	13 MB	Consider removing if still 0 after 30 days
IX_PeopleBaseItemMap_ItemId_ListOrder	12 MB	Monitor for "Continue Watching" queries

Action: Wait 30 days, run diagnostics again, then drop indexes with 0 uses.

---

📈 Performance Optimization Priority

Priority 1: Fix ItemValues Table (Critical)

• 1.3 billion rows read via seq scans
• Causing massive I/O
• Impact: Slow genre/tag filtering, slow metadata queries

Priority 2: Monitor Supplementary Indexes

• Use Jellyfin normally for 1 week
• Run diagnostics weekly
• Keep indexes that show usage
• Remove indexes with 0 uses after 30 days

Priority 3: Peoples Table Optimization

• 918 million rows read
• Good index usage but high scan count
• Impact: Actor/director queries may be slow

---

🧪 Testing Plan

Week 1: Baseline Testing

Day 1-2: Use Jellyfin Normally

• Browse libraries
• Use "Recently Added"
• Filter by genre/tags
• Search for actors

Day 3: Run Diagnostics

& "C:\Program Files\PostgreSQL\18\bin\psql.exe" -U jellyfin -d jellyfin -f sql\diagnostics.sql > diagnostics_week1.txt

Compare:

• Are supplementary indexes being used now?
• Has ItemValues seq scan count increased?

Week 2-4: Monitor and Optimize

Weekly: Run diagnostics
Look for:

• Index usage patterns
• Indexes with 0 uses (candidates for removal)
• New slow query patterns

After 30 days:

• Remove unused indexes
• Create new indexes based on actual query patterns
• Document findings

---

🎓 What We Learned

1. Supplementary Indexes May Not All Be Useful

• Created 5 supplementary indexes
• 2 have very low/zero usage
• This is normal - not all optimizations apply to every workload

2. Real Bottleneck Is ItemValues Table

• Our supplementary indexes weren't targeting the real problem
• ItemValues needs analysis of actual query patterns
• Sometimes you need to let the database run to find real issues

3. Index Creation Strategy

• ✅ Create indexes based on schema analysis (what we did)
• ✅ Monitor and remove unused indexes (what we need to do)
• ✅ Create indexes based on actual query patterns (next step)

---

📝 Next Steps

1. ✅ Statistics Updated (done)
2. Use Jellyfin for 1 week (your task)
3. Run diagnostics after 1 week
4. Analyze which indexes are used
5. Create optimized indexes for ItemValues
6. Remove unused indexes after 30 days

---

🔬 Advanced: Enable Query Logging

To see exactly what queries hit ItemValues:

-- Enable slow query logging
ALTER DATABASE jellyfin SET log_min_duration_statement = 1000; -- Log queries >1 second

-- Or log all ItemValues queries
ALTER DATABASE jellyfin SET log_statement = 'all';
ALTER DATABASE jellyfin SET log_line_prefix = '%t [%p]: ';

-- Check logs at:
-- C:\Program Files\PostgreSQL\18\data\log\

Then analyze the logs to see what indexes would help.

---

Summary

Your database is healthy but has optimization opportunities:

• ✅ Supplementary indexes installed correctly
• ✅ No critical errors or blocking
• ⚠️ ItemValues table needs optimization (critical)
• ℹ️ Need actual workload to see if new indexes help
• 📊 Run diagnostics weekly to track improvements

Estimated Performance Gain After Fixes:

• ItemValues queries: 70-90% faster
• Genre/tag filtering: 50-80% faster
• Overall: 20-40% improvement in common operations

Keep using Jellyfin and check back in a week! 🚀