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

Kevin Goess <kgoess(at)bepress(dot)com> writes:
>> Now, the only way to get to a zero selectivity estimate for var = const
>> is if the planner believes that the pg_stats most-common-values list
>> for the column is complete, and the constant is nowhere in the list.
>> So one plausible explanation for the change in behavior is that you
>> jacked up the statistics target for the date column enough so that
>> it includes all of the date values you keep in that column.
>> Am I right
>> in guessing that you drop old data from this table? How far back?

> 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.

Still bemused by the change from 9.0 to 9.2. But there were some small
changes in the cost estimation equations for indexscans, so maybe on your
real data instead of my toy example the pkey index still managed to look
cheaper to 9.0 but not so much to 9.2.

regards, tom lane