BRIN indexes only make sense for big tables (thousands of data pages or much more). (You already know that, just to guide the general public.) But how to tell whether rows are physically sorted enough?
The manual offers a hint:
BRIN indexes (a shorthand for Block Range INdexes) store summaries
about the values stored in consecutive physical block ranges of a
table. Thus, they are most effective for columns whose values are
well-correlated with the physical order of the table rows.
Bold emphasis mine.
ANALYZE gathers a corresponding statistic stored in
pg_statistic. Best accessed via the column
correlation in the system view
pg_stats by humans:
Statistical correlation between physical row ordering and logical
ordering of the column values. This ranges from -1 to +1. When the
value is near -1 or +1, an index scan on the column will be estimated
to be cheaper than when it is near zero, due to reduction of random
access to the disk. (This column is null if the column data type does
not have a
All statistics are based on a sample of rows and as such just estimates, even when up to date. You can increase validity by increasing the sample size, i.e. setting a higher "statistics target" - thereby also increasing the cost of
ANALYZE, of course. The default
default_statistics_target is just 100, which is OK for most purposes, but typically too low for columns of big tables with non-trivial data distribution (that are used for sorting and filtering). See:
You might even increase the statistics target temporarily, run
ANALYZE, and reset the value, to get a one-time correlation with increased validity.
So, once the table is analyzed (manually, or by
SELECT schemaname, tablename, attname, n_distinct, correlation
WHERE schemaname = 'public'
AND tablename = 'tbl'
AND attname = 'my_index_col';
correlation close to 0 means a BRIN index will be useless.
correlation close to 1 (or -1) means a BRIN index will be excellent.
- With a low count in
n_distinct (relative to the total count) or a negative ratio close to 0, you might increase the setting for
pages_per_range (default 128) accordingly.
- With a high count of
n_distinct (relative to the total count) or a negative ratio close to -1, you might decrease the setting for
Everything in between is a grey area. Many factors at play. Also depends a lot on the avg row size and typical queries. In my experience, values close to 0 or to 1 / -1 are common, which makes the decision easy.
That said, if the table is not sorted enough, you might make it so with
CLUSTER or one of the less blocking community tools pg_squeeze or pg_repack. Rewriting the whole table in sort order is expensive for big tables, and deteriorates over time if there are
INSERT) operations. But can pay for certain use cases. See: