Обсуждение: problem concerning vacuum/statistics & query performance

Поиск
Список
Период
Сортировка

problem concerning vacuum/statistics & query performance

От
"Esger Abbink"
Дата:
Hi,

i have a performance problem with a db. This db consists of only a few tables and is continuously fed with new datasets
(3mininterval) and at night it is pruned (all but the last set are deleted) and vacuumed.  

the problem is that queries run (far) too slow because the planner seems to be using incorrect statistics (during the
daythe larger tables will grow to several hundred-thousand rows). 

if i manually VACUUM ANALYZE the db when it is pretty loaded (~300k rows) the planner starts using a different plan
whichruns acceptable performance wise. however after the daily cleanup it reverts back to the old (bad) plan. 

so it seems the nightly VACUUM does some analyzing as well?
in which case the planner is using totally off-base statistics for most of the day, which in itself isnt bad but i'd
ratherhave it use large table stats on small tables than vice versa. 


any thoughts? (other than upgrading to 7.2 ;) )


(postgres is 7.0.3 btw)

Esger

--
NeoMail - Webmail that doesn't suck... as much.
http://neomail.sourceforge.net