Can someone explain ALTER TABLE SET STATISTICS?
Mladen Gogala
does the integer argument taken by this command represent? My guess is
that it is determining the percentage of the table to inspect for
calculating the statistics? If that is so, why is it integer?
Matthew Woodcraft
> The documentation of this command is extremely unclear and vague? What
> does the integer argument taken by this command represent? My guess is
> that it is determining the percentage of the table to inspect for
> calculating the statistics? If that is so, why is it integer?
What it controls directly is the level of detail used to store summary
statistics after the table has been inspected.
It determines two things: the number of 'most common values' to store
statistics for, and the number of buckets to store statistics for in
the value frequency histogram.
Section 14.2 explains this, and there's a detailed walkthrough of the
algorithm that uses it in 55.1 (in the 8.4 docs).
I believe it does also indirectly determine the percentage of the table
inspected; that is, ANALYZE samples enough rows to get accurate
statistics at the level of detail requested. I don't think this
percentage is directly tunable.
-M-
Mladen Gogala
Ah, I got it:
"The amount of information stored in pg_statistic by ANALYZE, in
particular the maximum number of entries in the most_common_vals and
histogram_bounds arrays for each column, can be set on a column-by-column
basis using the ALTER TABLE SET STATISTICS command, or globally by
setting the default_statistics_target configuration variable."
It's the size of histogram for every column! Thanks a lot for your help.