PostgreSQL – How to Set Statistics Level of a Table

postgresqlstatistics

I know how to set statistics level for a particular column i.e

alter table table_name alter column column_name set statistics 100;

However, if I wish to set statistics level for a particular table or for a particular schema, is there is one liner command?

Best Answer

There is nothing ready-made for a table or schema, because that is seldom useful.

But you can set it for a whole database with

ALTER DATABASE mydb SET default_statistics_target = 1000;

You can also use psql's \gexec to set the statistics target for all columns of a table:

SELECT format(
          'ALTER TABLE %s ALTER %I SET STATISTICS 1000',
          attrelid::regclass,
          attname
       )
FROM pg_attribute
WHERE attrelid = 'pgbench_tellers'::regclass
  AND attnum > 0 \gexec
Related Question