Re: BUG #6275: Horrible performance regression

"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