Add a check for correlation dropping below a certain value.

[phinjensen]

ID: 72
Version: unspecified
Date: 2011-03-29 03:34 EDT
Author: Andy Lester (andy@petdance.com)

---

We had an app that turned out to be very dependent on tuples being clustered in
a certain order. We had an 80M-row table with two columns, keyword and id.
The table happened to be predominantly in keyword order, physically.
Correlation from pg_stats on this table was around 0.90. Throughout the day,
searches would read thousands or tens of thousands of tuples in keyword order.
Life was good.

This weekend, we rebuilt this table, but rebuilt it in ID order. When we
rolled the table out, our performance tanked. Reading thousands of tuples in
keyword order required thousands of seeks throughout the table rows. It
crushed performance. Turns out correlation on the keyword column went down to
about 0.03. Re-clustering the table fixed our performance problem.

And, it's not just this one table. We have about 15 of these tables. As they
get updated, we want to make sure that the correlation on the keyword column in
all these tables never gets below, say, 0.90, and check_postgres seems like the
ideal tool to monitor this.

[justinpryzby]

7 years ago, something like this might have worked ?
check_postgres.pl --action custom_query -c 90 -w 95 --query "SELECT abs(100*correlation)::int AS result FROM pg_stats WHERE tablename='pg_attribute' AND attname='attname'" --valtype integer --reverse