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