wjones
78c8d4256c
- Moved all documentation to docs/ and updated README with categorized links and new docs/INDEX.md - Added HOW_TO_SWITCH_DATABASE.md and several new analysis/action docs - Introduced db-config.ps1 for centralized DB config; all scripts now use it for easy DB switching - Added db-quick.ps1 for interactive diagnostics and index management - Updated Add-All-Indexes.bat to use db-config.ps1 - Added Fix-ItemValues-Performance.ps1 to create 3 critical indexes on ItemValues, addressing 1.3B row seq scan issue - Updated performance_indexes.sql with new ItemValues indexes and ANALYZE - Updated diagnostics.sql and database_report.txt for improved output and clarity - All scripts and docs now reference the new config and index optimization workflow
317 lines
8.4 KiB
Markdown
317 lines
8.4 KiB
Markdown
# 📊 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
|
||
```sql
|
||
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
|
||
|
||
```powershell
|
||
& "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:**
|
||
```sql
|
||
-- 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:**
|
||
```sql
|
||
-- 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**
|
||
```powershell
|
||
& "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:
|
||
|
||
```sql
|
||
-- 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! 🚀
|