Index analysis (H***): reorder/rename reports, add documentation
Basics (to be included to the documentation)
Index analysis includes
Unused indexes
Find all indexes which are unused on the master and all observed replicas. The usage data is based on pg_stat_all_tables
. All observed nodes must be analyzed. Important: the usage data can be reset at any time (manually or after server restart), so it must be visible when / how long ago it was last reset.
Later (in "Observations" and "Recommendations" sections), we will recommend to DROP indexes, which are known to be unused on all observed nodes, if usage information was collected during a significant period of time (at last 1 week; or even more, depending on the project).
Redundant indexes
Detect indexes redundant. Examples:
- if index A and B are identical, we can conclude that B is redundant to A. And vice versa, A is redundant to B. It is not really important which index to choose for dropping during optimization. What is important is (and these principles are applied to all subsequent cases as well):
- compare columns sets, keeping in mind that order is important (changed order leads to changed semantics);
- compare index types / access methods (we must not suggest dropping
gin
index claiming that its redundant tobtree
and vice versa); - non of indexes are partial or they are both partial, with identical predicates;
- to ensure, that index access methods match, and both of them or none of them have the Unique option;
- if both indexes have Unique option, and one of them supports PK constraint, never suggest dropping it.
- if we have index
A(col1)
and indexB(col1, col2)
, index A can be considered as redundant to index B. But not vice versa. - if we find some redundant index, we also need to check, is it being used or not. In the "Observations" and "Recommendations" sections, we won't suggest dropping such an index, because otherwise, this might lead to performance degradation. Therefore, rule: only unused redundant indexes will be proposed for removal.
Invalid indexes
Detect INVALID indexes, based on indisvalid
column in pg_index
.
Later (in "Observations" and "Recommendations" sections), we will recommend to DROP such indexes after manual analysis proving that they are not needed to normal activity.
FK indexes
Problem: consider that we have a foreign key (FK) from table A
, column ref
to table B
, column id
. In most cases, B.id
is a primary key, so it's indexed. At the same time, A.ref
is often left unindexed. If delete from B where id = :id_val
is executed, it leads to a sequential scan on table A
, to check which its rows refer to the row being deleted. The same happens for queries like update B set id = :new_id_val where id = :old_id_val
. To avoid that, an index on A.ref
is needed.
Special report FK indexes
is to detect such situations, suggesting to create indexes, supporting FKs.
This is not applied to every project – in some cases, chances that delete
/ update
described here will be needed, are close to 0. It needs manual verification.
The list of missing "FK indexes" is to be compared with the reports of unused and redundant indexes, to ensure that we're not going to DROP unused/redundant indexes which support DELETE/UPDATE involving FKs if chances that it will happen is significant.
Acceptance criteria
In the list of postgres-checkup reports, section H looks balanced, includes anaysis of unused, redundant, invalid and FK-supporting indexes. As a DBA using postgres-ckeckup, I easily find the documentation (readin any of index-related reports), explaining all 4 reports and connections between them.