Michal Szymanski wrote: > There is another strange thing. We have two versions of our test > >>environment one with production DB copy and second genereated with > >>minimal data set and it is odd that update presented above on copy of > >>production is executing 170ms but on small DB it executing 6s !!!! > > > >How are you vacuuming the tables? > > > Using pgAdmin (DB is installed on my laptop) and I use this tool for > vaccuminh, I do not think that vaccuming can help because I've tested on > both database just after importing.
I think you are misunderstanding the importance of vacuuming the table. Try this: on a different terminal from the one running the test, run a VACUUM on the updated table with vacuum_cost_delay set to 20, on an infinite loop. Keep this running while you do your update test. Vary the vacuum_cost_delay and measure the average/min/max UPDATE times. Also try putting a short sleep on the infinite VACUUM loop and see how its length affects the UPDATE times. One thing not clear to me is if your table is in a clean state. Before running this test, do a TRUNCATE and import the data again. This will get rid of any dead space that may be hurting your measurements. -- Alvaro Herrera http://www.advogato.org/person/alvherre "The Postgresql hackers have what I call a "NASA space shot" mentality. Quite refreshing in a world of "weekend drag racer" developers." (Scott Marlowe) ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org
