Thanks for the reply!
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.
--
Kevin M. Goess
Software Engineer
Berkeley Electronic Press
kgoess@xxxxxxxxxxx
510-665-1200 x179
www.bepress.com
bepress: sustainable scholarly publishing
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@xxxxxxxxxxx
510-665-1200 x179
www.bepress.com
bepress: sustainable scholarly publishing
# select * from pg_stats where tablename = 'hits_user_daily_count' and attname = 'date';
-[ RECORD 1 ]----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
schemaname | public
tablename | hits_user_daily_count
attname | date
inherited | f
null_frac | 0
avg_width | 4
n_distinct | 91
most_common_vals | {2013-11-11,2013-10-21,2013-10-07,2013-10-09,2013-10-03,2013-10-23,2013-11-06,2013-09-16,2013-10-02,2013-10-08,2013-09-23,2013-10-29,2013-10-15,2013-09-30,2013-10-22,2013-11-07,2013-10-28,2013-09-11,2013-11-05,2013-10-16,2013-10-30,2013-10-10,2013-11-04,2013-09-24,2013-09-17,2013-10-14,2013-10-01,2013-10-17,2013-11-08,2013-10-24,2013-09-09,2013-09-19,2013-09-10,2013-09-25,2013-10-04,2013-09-18,2013-10-31,2013-09-04,2013-09-26,2013-10-20,2013-08-29,2013-10-18,2013-08-27,2013-10-13,2013-09-12,2013-08-14,2013-09-13,2013-09-02,2013-10-25,2013-11-03,2013-08-19,2013-09-05,2013-09-27,2013-10-06,2013-10-11,2013-09-15,2013-09-03,2013-09-22,2013-10-27,2013-11-10,2013-08-28,2013-11-01,2013-08-26,2013-09-20,2013-10-19,2013-11-09,2013-10-12,2013-08-15,2013-08-30,2013-08-16,2013-08-25,2013-09-21,2013-09-28,2013-11-02,2013-10-05,2013-08-23,2013-09-08,2013-09-06,2013-09-29,2013-10-26,2013-09-07,2013-09-14,2013-09-01,2013-08-31,2013-08-20,2013-08-17,2013-08-24,2013-08-18,2013-08-22,2013-08-21,2013-11-12}
most_common_freqs | {0.0144667,0.0137556,0.0136889,0.0135333,0.0134,0.0134,0.0133333,0.0133111,0.0132667,0.0132,0.0131556,0.0131333,0.0130667,0.013,0.0129778,0.0129333,0.0128889,0.0128,0.0127333,0.0126444,0.0126,0.0125778,0.0125111,0.0124889,0.0123778,0.0123778,0.0122667,0.0122667,0.0122,0.0121556,0.0120889,0.0119778,0.0119111,0.0118444,0.0118,0.0117333,0.0116,0.0114222,0.0114,0.0113556,0.0113111,0.0112889,0.0112222,0.0112222,0.0109556,0.0109333,0.0109111,0.0108889,0.0108444,0.0108444,0.0108222,0.0107333,0.0107333,0.0107333,0.0107333,0.0106889,0.0106,0.0105778,0.0105556,0.0105111,0.0104889,0.0104889,0.0104,0.0103778,0.0103333,0.0102667,0.0102,0.00995556,0.00977778,0.00975556,0.00971111,0.00966667,0.00966667,0.00964444,0.00962222,0.00948889,0.00944444,0.0094,0.00924444,0.0092,0.00855556,0.00848889,0.00846667,0.00831111,0.00828889,0.00826667,0.00811111,0.00766667,0.00717778,0.00497778,0.0018}
histogram_bounds |
correlation | 0.276451
most_common_elems |
most_common_elem_freqs |
elem_count_histogram |
-- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
