Timing is on.
Expanded display is used automatically.
=========================================
Jellyfin PostgreSQL Performance Report
=========================================

1. CONNECTION POOL STATUS
-----------------------------------------
 total_connections | active | idle | idle_in_transaction | aborted | waiting 
-------------------+--------+------+---------------------+---------+---------
                 0 |      0 |    0 |                   0 |       0 |       0
(1 row)

Time: 14.923 ms

2. LONG-RUNNING QUERIES (>1 second)
-----------------------------------------
 pid | usename | application_name | duration | state | wait_event_type | wait_event | query_preview 
-----+---------+------------------+----------+-------+-----------------+------------+---------------
(0 rows)

Time: 10.143 ms

3. TABLE SIZES (Top 10)
-----------------------------------------
Time: 17.735 ms

4. INDEX USAGE (Tables with low index usage)
-----------------------------------------
 schemaname |       tablename       | seq_scan | idx_scan |  rows  | index_usage_percent 
------------+-----------------------+----------+----------+--------+---------------------
 library    | ItemValues            |   239123 |   262958 |   8802 |               52.37
 library    | Peoples               |    20780 |   331963 |  84061 |               94.11
 library    | AttachmentStreamInfos |      533 |    19267 |   5085 |               97.31
 library    | Chapters              |      369 |    19220 |  18865 |               98.12
 library    | BaseItems             |    10683 |  2776087 | 112093 |               99.62
 library    | MediaStreamInfos      |      126 |    39129 |  60298 |               99.68
 library    | BaseItemProviders     |     2680 |   900431 | 155030 |               99.70
 library    | ItemValuesMap         |      431 |   237893 | 263683 |               99.82
 library    | PeopleBaseItemMap     |       15 |   127908 | 246523 |               99.99
 library    | BaseItemImageInfos    |       87 |  1025614 |  65607 |               99.99
(10 rows)

Time: 8.349 ms

5. TABLES WITH HIGH SEQUENTIAL SCANS
-----------------------------------------
 schemaname |       tablename       | seq_scan | seq_tup_read | idx_scan | n_live_tup | avg_rows_per_seq_scan 
------------+-----------------------+----------+--------------+----------+------------+-----------------------
 library    | ItemValues            |   239123 |   1426740382 |   262958 |       8802 |                  5966
 library    | Peoples               |    20780 |   1038286642 |   331963 |      84061 |                 49965
 library    | BaseItems             |    10683 |    379674076 |  2776087 |     112093 |                 35540
 library    | BaseItemProviders     |     2680 |      1370592 |   900431 |     155030 |                   511
 library    | UserData              | 15853658 |       458427 |        1 |          3 |                     0
 library    | ItemValuesMap         |      431 |        96582 |   237893 |     263683 |                   224
 library    | AttachmentStreamInfos |      533 |        77460 |    19267 |       5085 |                   145
 library    | Chapters              |      369 |        71596 |    19220 |      18865 |                   194
 library    | MediaStreamInfos      |      126 |        11295 |    39129 |      60298 |                    89
(9 rows)

Time: 9.443 ms

6. TABLE BLOAT (Dead Tuples)
-----------------------------------------
 schemaname |     tablename      | dead_tuples | live_tuples | dead_tuple_percent | last_vacuum |        last_autovacuum        |         last_analyze          |       last_autoanalyze        
------------+--------------------+-------------+-------------+--------------------+-------------+-------------------------------+-------------------------------+-------------------------------
 library    | BaseItems          |        9743 |      112093 |               8.69 |             | 2026-02-28 15:26:15.491132-05 | 2026-02-28 15:53:05.714737-05 | 2026-02-28 15:46:14.039634-05
 library    | BaseItemImageInfos |        4933 |       65607 |               7.52 |             | 2026-02-28 15:51:10.816013-05 | 2026-02-28 15:53:06.902228-05 | 2026-02-28 15:52:11.608171-05
 library    | BaseItemProviders  |        2468 |      155030 |               1.59 |             | 2026-02-28 15:46:15.528943-05 | 2026-02-28 15:53:07.082623-05 | 2026-02-28 15:50:10.879895-05
(3 rows)

Time: 3.804 ms

7. CACHE HIT RATIO (Should be >95%)
-----------------------------------------
         metric         | hit_ratio_percent 
------------------------+-------------------
 Buffer Cache Hit Ratio |                  
(1 row)

Time: 3.750 ms

8. UNUSED OR RARELY USED INDEXES
-----------------------------------------
 schemaname |     tablename     |                            indexname                            | index_size | times_used | idx_tup_read | idx_tup_fetch 
------------+-------------------+-----------------------------------------------------------------+------------+------------+--------------+---------------
 library    | PeopleBaseItemMap | PK_PeopleBaseItemMap                                            | 23 MB      |          0 |            0 |             0
 library    | ItemValuesMap     | idx_itemvaluesmap_itemvalueid_itemid                            | 17 MB      |         10 |          108 |            10
 library    | ItemValuesMap     | PK_ItemValuesMap                                                | 17 MB      |          0 |            0 |             0
 library    | BaseItems         | IX_BaseItems_Type_TopParentId_IsVirtualItem_PresentationUnique~ | 16 MB      |          6 |        57709 |          4974
 library    | BaseItems         | IX_BaseItems_Path                                               | 15 MB      |          0 |            0 |             0
 library    | BaseItems         | IX_BaseItems_Type_TopParentId_Id                                | 13 MB      |          0 |            0 |             0
 library    | PeopleBaseItemMap | IX_PeopleBaseItemMap_ItemId_ListOrder                           | 13 MB      |          0 |            0 |             0
 library    | BaseItems         | idx_baseitems_datecreated_filtered                              | 11 MB      |          0 |            0 |             0
 library    | BaseItemProviders | IX_BaseItemProviders_ProviderId_ProviderValue_ItemId            | 10 MB      |          0 |            0 |             0
 library    | BaseItemProviders | baseitemproviders_providerid_idx                                | 8216 kB    |          0 |            0 |             0
(10 rows)

Time: 16.869 ms

9. CURRENT LOCKS (Blocked queries)
-----------------------------------------
 blocked_pid | blocked_user | blocking_query | blocking_duration | blocked_query 
-------------+--------------+----------------+-------------------+---------------
(0 rows)

Time: 10.333 ms

10. SLOWEST QUERIES (Requires pg_stat_statements extension)
-----------------------------------------
DO
Time: 10.465 ms
DO
Time: 144.695 ms

11. KEY POSTGRESQL SETTINGS
-----------------------------------------
             name             | setting | unit |                                        short_desc                                        
------------------------------+---------+------+------------------------------------------------------------------------------------------
 autovacuum                   | on      |      | Starts the autovacuum subprocess.
 checkpoint_completion_target | 0.9     |      | Time spent flushing dirty buffers during checkpoint, as fraction of checkpoint interval.
 default_statistics_target    | 100     |      | Sets the default statistics target.
 effective_cache_size         | 524288  | 8kB  | Sets the planner's assumption about the total size of the data caches.
 effective_io_concurrency     | 16      |      | Number of simultaneous requests that can be handled efficiently by the disk subsystem.
 maintenance_work_mem         | 131072  | kB   | Sets the maximum memory to be used for maintenance operations.
 max_connections              | 100     |      | Sets the maximum number of concurrent connections.
 random_page_cost             | 4       |      | Sets the planner's estimate of the cost of a nonsequentially fetched disk page.
 shared_buffers               | 262144  | 8kB  | Sets the number of shared memory buffers used by the server.
 wal_buffers                  | 2048    | 8kB  | Sets the number of disk-page buffers in shared memory for WAL.
 work_mem                     | 1048576 | kB   | Sets the maximum memory to be used for query workspaces.
(11 rows)

Time: 6.588 ms

12. RECOMMENDATIONS
-----------------------------------------
Time: 8.404 ms

=========================================
Run sql/performance_indexes.sql to add missing indexes
See PERFORMANCE_TROUBLESHOOTING.md for detailed fixes
=========================================
