Carlos Moreno wrote: >> , but my guess is that the total >> data size about enough to fit in shared_buffers or kernel cache. On >> the new system (or dropped/recreated database), it would've all or >> mostly fit in memory which would make things like count(*) work >> quickly. > > I don't understand this argument --- the newer system has actually > less memory than the old one; how could it fit there and not on the > old one? Plus, how could dropping-recreating the database on the same > machine change the fact that the entire dataset entirely fit or not in > memory?? Because on the older server it is bloated, while on the new one it is fresh thus no dead tuples. > The other part that puzzled me is that after running "select count(*) > ... " several times (that particular table is *very* small --- just > 200 thousand records of no more than 100 or 200 bytes each), then the > entire table *should* have been in memory ... Yet, it would still > take a few seconds (notice that there was a *considerable* > improvement from the first run of that query to the second one on the > old server --- from more than a minute, to just above two seconds.... > But still, on the new server, and after recreating the DB on the old > one, it runs in *no time* the first time). Bloat can explain this as well. >> My guess is that a vacuum full would've brought the other database >> back up to speed. > > I'm furious now that it didn't occur to me the vacuum full until > *after* I had recreated the database to see th problem disappear... > > I wonder if I should then periodically run a vacuum full --- say, once > a week? Once a month? Never. What you need to do is make sure your FSM settings (fsm_max_pages in particular) are high enough, and that you VACUUM (not full) frequently enough. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
