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.
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
kgo...@bepress.com
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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
