Postgresql performance: unused indices

[hoerup]

Hi

As you know a unused index is bad for performance in the way that it doesn't improve your read queries, but adds additional work on INSERT/UPDATE/DELETE statements

I was curious on what the situation was in this area for VBO365 so I ran this query:

Code: Select all

SELECT
    t.schemaname,
    t.tablename,
    c.reltuples::bigint                            AS num_rows,
    pg_size_pretty(pg_relation_size(c.oid))        AS table_size,
    psai.indexrelname                              AS index_name,
    pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size,
    CASE WHEN i.indisunique THEN 'Y' ELSE 'N' END  AS "unique",
    psai.idx_scan                                  AS number_of_scans,
    psai.idx_tup_read                              AS tuples_read,
    psai.idx_tup_fetch                             AS tuples_fetched
    
FROM
    pg_tables t
    LEFT JOIN pg_class c ON t.tablename = c.relname
    LEFT JOIN pg_index i ON c.oid = i.indrelid
    LEFT JOIN pg_stat_all_indexes psai ON i.indexrelid = psai.indexrelid
WHERE
    t.schemaname NOT IN ('pg_catalog', 'information_schema')
    AND c.reltuples::bigint > 10000 # dont look at small tables
    AND psai.idx_scan <100 #only zero or little usage
ORDER BY 1, 2;

Which gave this result

Code: Select all

 schemaname |           tablename            | num_rows | table_size |                           index_name                           | index_size | unique | number_of_scans | tuples_read | tuples_fetched
------------+--------------------------------+----------+------------+----------------------------------------------------------------+------------+--------+-----------------+-------------+----------------
 public     | backup_journals                |  5756355 | 338 MB     | ix_backup_journals_repository_id                               | 69 MB      | N      |               6 |     1591301 |              0
 public     | job_session_messages           |  7504095 | 1502 MB    | ix_job_session_messages_job_session_id_order                   | 451 MB     | N      |               2 |           2 |              2
 public     | job_session_messages           |  7504095 | 1502 MB    | ix_job_session_messages_job_session_id_version                 | 254 MB     | N      |              40 |        1447 |           1328
 public     | organization_internal_accounts |    12769 | 1488 kB    | ix_organization_internal_accounts_organization_id_on_premises_ | 8192 bytes | Y      |               0 |           0 |              0
 public     | restore_point_configs          |  4280378 | 1044 MB    | ix_restore_point_configs_exchange_id                           | 48 MB      | N      |               0 |           0 |              0
 public     | restore_point_configs          |  4280378 | 1044 MB    | ix_restore_point_configs_share_point_id                        | 48 MB      | N      |               0 |           0 |              0
 public     | restore_point_journals         |  5715468 | 951 MB     | pk_restore_point_journals                                      | 146 MB     | Y      |               0 |           0 |              0
(7 rows)

From which it is clear that there are some indices that probably could be removed - at least it is worth for your engineering team to look further into