Thread: meaning of default_statistics_target
I'm trying to understand the implications of changing the value of the STATISTICS of a column with ALTER TABLE, and there are somethings I'm not understanding correctly. How much, and which extra statistics information will ANALYZE gather if I set a higher value for one specific column (maybe one with and index, and heavily used in SELECT clauses)? If instead of 100 I set a column to 250, which extra data will I see in pg_statistics after an ANALYZE? Thanks.
Martin Marques <martin@marquesminen.com.ar> writes:
> How much, and which extra statistics information will ANALYZE gather if
> I set a higher value for one specific column (maybe one with and index,
> and heavily used in SELECT clauses)? If instead of 100 I set a column to
> 250, which extra data will I see in pg_statistics after an ANALYZE?
The target determines the desired size of the histogram and
most-common-values arrays. Increasing it gives you better resolution of
those stats.
Increasing the target also increases the number of rows that ANALYZE
samples to prepare the stats, so you should theoretically get more
accurate stats for the other columns too, even though they'll still get
boiled down to the same array lengths as before.
regards, tom lane
Tom Lane escribió: > Martin Marques <martin@marquesminen.com.ar> writes: >> How much, and which extra statistics information will ANALYZE gather if >> I set a higher value for one specific column (maybe one with and index, >> and heavily used in SELECT clauses)? If instead of 100 I set a column to >> 250, which extra data will I see in pg_statistics after an ANALYZE? > > The target determines the desired size of the histogram and > most-common-values arrays. Increasing it gives you better resolution of > those stats. > > Increasing the target also increases the number of rows that ANALYZE > samples to prepare the stats, so you should theoretically get more > accurate stats for the other columns too, even though they'll still get > boiled down to the same array lengths as before. Is the value measured in some scale, or does it depend on something internal? What does 1000 mean?
Martin Marques escribió: > Is the value measured in some scale, or does it depend on something > internal? What does 1000 mean? 1000 histogram and MCV entries, and also a larger sample: >> Increasing the target also increases the number of rows that ANALYZE >> samples to prepare the stats, so you should theoretically get more >> accurate stats for the other columns too, even though they'll still get >> boiled down to the same array lengths as before. How larger is the sample, I don't know. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.