Re: BUG #6275: Horrible performance regression

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" <finlay(at)dragonfly(dot)co(dot)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