Re: BUG #6275: Horrible performance regression - Mailing list pgsql-bugs

From Tom Lane
Subject Re: BUG #6275: Horrible performance regression
Date
Msg-id 13998.1319777593@sss.pgh.pa.us
Whole thread Raw
In response to BUG #6275: Horrible performance regression  ("Finlay Thompson" <finlay@dragonfly.co.nz>)
Responses Re: BUG #6275: Horrible performance regression
List pgsql-bugs
"Finlay Thompson" <finlay@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

pgsql-bugs by date:

Previous
From: "Finlay Thompson"
Date:
Subject: BUG #6275: Horrible performance regression
Next
From: Robert Young
Date:
Subject: Re: Add statistics_collector_listen_addresses to fix hard-coding of "localhost"