sql - Check statistics targets in PostgreSQL

Question

I have searched but been unable to find any simple, straight forward answer to this. How do I check the current statistics targets used by ANALYZE?

Answer 1

The setting for the statistics target is stored per column in the catalog table pg_attribute. You can set it like this:

ALTER TABLE myschama.mytable ALTER mycolumn SET STATISTICS 127;

And check it like this:

SELECT attstattarget
FROM   pg_attribute
WHERE  attrelid = 'myschama.mytable'::regclass
AND    attname = 'mycolumn';

Or you just look at the creation script in the object browser of pgAdmin, where it is appended if the value is distinct from the default in default_statistics_target.

I quote the manual on attstattarget:

attstattarget controls the level of detail of statistics accumulated for this column by ANALYZE. A zero value indicates that no statistics should be collected. A negative value says to use the system default statistics target. The exact meaning of positive values is data type-dependent. For scalar data types, attstattarget is both the target number of "most common values" to collect, and the target number of histogram bins to create.

Bold emphasis mine.

Statistics for plain index columns are identical to column statistics and have no separate entries in statistics tables. But Postgres gathers separate statistics for index expressions. Those can be tweaked in a similar fashion:

ALTER INDEX myschema.myidx ALTER COLUMN 1 SET STATISTICS 128;

In absence of actual column names, ordinal numbers are used to address index columns, which correspond to pg_attribute.attnum:

SELECT attstattarget
FROM   pg_attribute
WHERE  attrelid = 'myschama.myidx'::regclass
AND    attnum = 1;

The setting only actually affects column statistics the next time ANALYZE is being run manually or by autovacuum.