Hi Tom Sorry for the delay.
The version of postgis didn't change, and when I rolled back to postgresql 8.4.9 the expected performance returned. I am very confident the regression was caused by the 8.4.9 upgrade. Any suggestions on how to diagnose a long run script like this ? I can easily turn on more logging, in both cases and compare ? Can I log the query plans ? Finlay On Fri, 2011-10-28 at 00:53 -0400, Tom Lane wrote: > "Finlay Thompson" <fin...@dragonfly.co.nz> writes: > > After an upgrade from 8.4.8 to 8.4.9 performance of load script went from ~ > > 3 hours to not finishing after 24 hours. > > > Context: We have a continuous build script, that loads, grooms, matches data > > every few hours. The script has been run over 100 times in the last few > > weeks, and has steadily been taking around 3 hours. It is really a group of > > scripts, something like 15000 lines of sql, and some python scripts to run > > it together. > > > After upgrading the postgresql*-8.4 packages on ubuntu, to version 8.4.9, > > the script suddenly stopped working, and consuming all the ram (16GB) on the > > computer (i7). > > Well, the most likely theory seems to be that one of the planner changes > in 8.4.9 is resulting in a much less optimal plan choice for one or more > queries, but it's impossible to diagnose it on the basis of this much > information. > > > btw: The database uses plpython and postgis. > > Hmm, did you change postgis versions at the same time? If so, which > upgrade caused the problem, postgres or postgis? > > regards, tom lane -- Finlay Thompson www.dragonfly.co.nz Dragonfly, PO Box 27535, Wellington 6141, New Zealand Level 5, 158 Victoria Street, Te Aro, Wellington M: +64 21 426 878 T: +64 4 385 9285 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
