If I increase the stats target to 1000, I get results like
regression=# select f, count(*) from (select unnest(most_common_freqs) f from pg_stats where tablename = 'qqq' and attname = 'other') ss group by 1 order by 1; f | count -------------+------- 6.66667e-06 | 935 1e-05 | 65 (2 rows)
regression=# select f, count(*) from (select unnest(most_common_freqs) f from pg_stats where tablename = 'qqq' and attname = 'other') ss group by 1 order by 1; f | count -------------+------- 6.66667e-06 | 936 1e-05 | 63 1.33333e-05 | 1 (3 rows)
(the counts move around a bit, but not very much). That corresponds to a minimum of 2 and a maximum of either 3 or 4 duplicates in a 300000-row sample.
Given that we know that the duplicate values of "other" are adjacent, what this is looking like to me is that there's something fishy about ANALYZE's choice of sample rows. It seems like it should not be picking 3
or 4 rows from the same block very often, and for all of those to come from the same 10-row span (out of 225 rows per page, more or less, in this table) seems like it ought to be a really low-probability event. But maybe my math is off.
Yeah, first it samples 30,000 blocks, then it samples 30,000 rows from those blocks. This leads to too many blocks getting zero rows sampled, too many getting more than one sampled, and too few getting exactly one sampled.
Basically, the variance for the poisson distribution for selected rows per block for sampling 30,000 row from 30,000 blocks is much broader than it is for sampling 30,000 rows from >> 30,000 blocks, so the 2 stage sampling method doesn't work very well.
Tomas was working on a patch a while back for fixing this by resampling from blocks, but I think he dropped it to work on multivariate stuff instead.
