bucardo
Add a check for correlation dropping below a certain value.
ID: 72 Version: unspecified Date: 2011-03-29 03:34 EDT Author: Andy Lester ([email protected])
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.
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
