Your analysis matches everything I see here, so what you say is probably the case. As to why it changed for us with the 9.0 => 9.2 upgrade, I also don't know--the change was pretty dramatic though. Since we've compensated for it, and since you say the current behavior is actually what's expected, I'm happy.
But since you went to the trouble to reply:
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.
I'm not following you there, but I'm not a full-time database guy. Attached is the pg_stats for that column in case you find that interesting or helpful.
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.
-- Kevin M. Goess Software Engineer Berkeley Electronic Press kgoess@bepress.com
