Re: simple query with radically different plan after 9.0 -> 9.2 upgrade

On Tue, Nov 12, 2013 at 2:47 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> > That's right, we store 90 days and roll up data older than that into a
> > different table.
>
> Ah-hah. The default statistics target is 100, so indeed ANALYZE is going
> to be able to fit every date entry in the table into the
> most-common-values list. In this situation, you'd rather that there were
> some uncertainty left. Given that the distribution of the date column is
> (I assume) pretty level, you don't really need full information about this
> column. I'd try backing off the stats target for the date column (and
> only the date column --- see ALTER TABLE SET STATISTICS) to 50 or even
> less.

That was it! I set it to 50 on all the 90-days tables and now we no longer
see that regular increase in disk reads between midnight of the new day and
the 1:37am re-analyze.

Thanks!