GitLab

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)

[1] https://github.com/NikolayS/postgres_dba