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

Поиск
Список
Период
Сортировка
От Kevin Goess
Тема Re: simple query with radically different plan after 9.0 -> 9.2 upgrade
Дата
Msg-id CABZkbxhnzKOyCcB0xHXjEK76JaN0EORfXcthnRULRJ9RxNMVaw@mail.gmail.com
обсуждение исходный текст
Ответ на Re: simple query with radically different plan after 9.0 -> 9.2 upgrade  (Tom Lane <tgl@sss.pgh.pa.us>)
Ответы Re: simple query with radically different plan after 9.0 -> 9.2 upgrade
Список pgsql-general
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
kgoess@bepress.com

510-665-1200 x179
www.bepress.com

bepress: sustainable scholarly publishing  
Вложения

В списке pgsql-general по дате отправления:

Предыдущее
От: Peter Eisentraut
Дата:
Сообщение: Re: Clang 3.3 Analyzer Results
Следующее
От: Tom Lane
Дата:
Сообщение: Re: simple query with radically different plan after 9.0 -> 9.2 upgrade