Unused indexes
Using the postgres_dba[1] tools, looking at Pleroma database on bikeshed -- we have a few obvious indexes we can probably shed. A few others are picked up incorrectly because of data/features not on bikeshed.
pleroma_prod=# :dba
Menu:
0 – Node & Current DB Information: master/replica, lag, DB size, tmp files, etc
1 – Databases: Size, Statistics
2 – Table Sizes
3 – Load Profile
a1 – Current Activity: count of current connections grouped by database, user name, state
b1 – Tables Bloat, rough estimation
b2 – B-tree Indexes Bloat, rough estimation
b3 – Tables Bloat, more precise (requires pgstattuple extension; expensive)
b4 – B-tree Indexes Bloat, more precise (requires pgstattuple extension; expensive)
b5 – Tables and Columns Without Stats (so bloat cannot be estimated)
e1 – List of extensions installed in the current DB
i1 – Unused/Rarely Used Indexes
i2 – Redundant Indexes
i3 – FKs with Missing/Bad Indexes
i4 – Invalid Indexes
i5 – Unused/Redundant Indexes Do & Undo Migration DDL
p1 – [EXPERIMENTAL] Alignment Padding. How many bytes can be saved if columns are ordered better?
s1 – Slowest Queries, by Total Time (requires pg_stat_statements extension)
s2 – Slowest Queries Report (requires pg_stat_statements)
t1 – Postgres parameters tuning
v1 – Vacuum: Current Activity
q – Quit
Type your choice and press <Enter>:
i1
reason | schema_name | table_name | index_name | index_scan_pct | scans_per_write | index_size | table_size | idx_scan | all_scans
----------------------------+-------------+-----------------------------+---------------------------------------------------+----------------+-----------------+------------+------------+----------+------------
Never Used Indexes | public | objects | objects_actor_type | 0.00 | 0.00 | 797 MB | 14 GB | 0 | 2347416057
Never Used Indexes | public | activities | activities_hosts | 0.00 | 0.00 | 241 MB | 5841 MB | 0 | 22719783
Never Used Indexes | public | activities | activities_in_reply_to | 0.00 | 0.00 | 170 MB | 5841 MB | 0 | 22719783
Never Used Indexes | public | users | users_following_address_index | 0.00 | 0.00 | 6976 kB | 78 MB | 0 | 40852176
Never Used Indexes | public | conversation_participations | conversation_participations_updated_at_desc_index | 0.00 | 0.00 | 1096 kB | 3280 kB | 0 | 1921
Never Used Indexes | public | instances | instances_unreachable_since_index | 0.00 | 0.00 | 56 kB | 32 kB | 0 | 989610
Never Used Indexes | public | lists | lists_user_id_index | 0.00 | 0.00 | 16 kB | 8192 bytes | 0 | 558899
Never Used Indexes | public | lists | lists_following_index | 0.00 | 0.00 | 16 kB | 8192 bytes | 0 | 558899
Never Used Indexes | public | filters | hided_phrases_index | 0.00 | 0.00 | 8192 bytes | 0 bytes | 0 | 1083
Never Used Indexes | public | scheduled_activities | scheduled_activities_scheduled_at_index | 0.00 | 0.00 | 8192 bytes | 0 bytes | 0 | 60230
Low Scans, High Writes | public | activities | activities_context_index | 0.07 | 0.02 | 838 MB | 5841 MB | 16551 | 22719783
Low Scans, High Writes | public | activities | activities_actor_id_DESC_NULLS_LAST_index | 2.90 | 0.90 | 611 MB | 5841 MB | 658341 | 22719783
Low Scans, High Writes | public | activities | activities_actor_index | 0.00 | 0.00 | 539 MB | 5841 MB | 157 | 22719783
Low Scans, High Writes | public | objects | objects_in_reply_to_index | 0.00 | 0.01 | 439 MB | 14 GB | 12672 | 2347416057
Low Scans, High Writes | public | activities | activities_id_desc_nulls_last_local_index | 1.65 | 0.51 | 308 MB | 5841 MB | 373744 | 22719783
Low Scans, High Writes | public | activities | activities_visibility_index | 0.00 | 0.00 | 250 MB | 5841 MB | 219 | 22719783
Low Scans, High Writes | public | activities | activities_local_index | 0.00 | 0.00 | 157 MB | 5841 MB | 105 | 22719783
High-Write Large Non-Btree | public | objects | objects_fts | 0.00 | 0.00 | 2020 MB | 14 GB | 240 | 2347416057
High-Write Large Non-Btree | public | objects | objects_tags | 0.00 | 0.00 | 134 MB | 14 GB | 544 | 2347416057
(19 rows)