F004, F005, H002, H003: Dynamic adjustments for databases with huge amount of objects

Goal

Work well (do not fail; provide reliable data) in databases with large number of objects (100+ thousand tables, indexes). At the same time, do not miss anything (such as redundant indexes when index size is small) in databases with smaller amount of objects.

TODO / How to implement

In F004, F005, H002, H003 we already use condition relpages > 100. It saves these reports from failure in DBs with lots of objects, but leads to inaccurate results in DBs with fewer number of objects of smaller size.

Apply this condition dynamically. The plan:

• start with CI tests (ensure that these new tests fail before new code changes):
  • small redundant indexes are detected successfully
  • small unused indexes are detected successfully
  • small tables with high int4 value (close to 2^31) in PK are detected in L003
  • in CI, can we afford testing cases when we have 100+ thousand objects? Can it be fast? — if yes, ensure that notification about “800 kiB” appears when needed.
• get the number of objects: select count(*) from pg_class. Do it only once for the whole checkup run.
• if it’s > 100000, apply the filter as before. If not, remove it from the queries.
• The notification that we don’t consider objects <800kiB has to be present only if this filter was applied
• test thoroughly. Does it work well? How much time do we need to analyze 90,000 objects?

SEE ALSO: https://postgres-ai.slack.com/archives/CE6NYN64W/p1563123238006900?thread_ts=1561924905.000600&cid=CE6NYN64W

Acceptance criteria

• for databases with up to a few thousand objects, we always have exact checks. All redundant indexes are found (verify with code snippet from postgres_dba)
• for databases with 100+ k objects, reports F004, F005, H002, H003:
  • are not failing (at least not failing if we adjust statement timeout setting it to a few minutes), and
  • in markdown reports we see the notification that only objects of size >800kiB we’re analyzed.