Improve H002 (unused and redundant indexes). Also adjust H001 (invalid indexes)

Goal

TODO / How to implement

For the 'Never Used Indexes' table (this is the main table in this report!):

  • Add a column to the 1st place: "Table"
  • Move table sizes to a separate column, "Table size", don't use "Index size" and "Table size" inside cells
  • Add "Total" (sum of index sizes)
  • Get rid of the last column, Usage
  • Add names of the tables

For 'Other unused indexes':

  • rename to 'Rarely Used Indexes"
  • add examples to CI test
  • make its format absolutely the same as 'Never Used Indexes' with one exception: leave column 'Reason', but rename it to 'Comment'. Besides this, it should be absolutely the same, with all adjustments mentioned above, incuding "Total", etc.
  • In "usage per each host" columns, present usage stats like: "scans: 123/hour, writes: 45678/hour"
  • take care about numbers, don't use things like "Usage: 1.085001e+06" (see a fresh Postila example)
  • Move column "Comment" to the most right position

For 'Redundant indexes':

  • Next the "Index" column, on the right side to it, must be the column "Redundant to", where we show one or multiple index names.
  • Where is DROP statements for redundant indexes? I cannot find it in "Recommendations". It should be there // Nik

Global / apply to all tables:

  • Add new column, # – same in H001 Invalid Indexes.
  • Rename report (both in code and in the checks list in the README): "H002 Unused and redundant indexes"
  • If stats were reset less than 30 days ago, add warning:

⚠ Statistics age is less than 30 days. Make decisions on index cleanup with caution!

  • Add a column "Supports FK". If index is defined on a set of columns which are used in a FK definition, we must signal about it everywhere, because a user might want to avoid dropping such indexes. This also applies to the Recommendations section.
  • Do not use "Schema name" column anywhere. Apply approach used in other reports: if schema name is public, do nothing. If schema name <> 'public', add it as a prefix to the table name (examples: "table1" means that it sits in the "public" schema, "audit"."log" means it's in the "audit" schema). Apply this to all tables in H section, including H001 Invalid Indexes.
  • This will not work: DROP INDEX CONCURRENTLY public.pnct_AnyBoard_lower_idx; – index name must be with double quotes here, because capital letters are used. Correct version: DROP INDEX CONCURRENTLY "public"."pnct_AnyBoard_lower_idx"; ==> Use double quotes everywhere, in all tables (including H001 report).

Acceptance criteria

As a DBA, I can use H002 to get a lot of details about my unused, rarely used and redundant indexes. Report allows me to quickly understand, how much disk space I can save, and what indexes are worth to remove.

Edited by Dmitry