Re: performance issue with a specific query

On Thu, 2006-07-27 at 09:23, Eliott wrote:
> Hi!
>
> I hope I'm sending my question to the right list, please don't flame
> if it's the wrong one.
>
> I have noticed that while a query runs in about 1.5seconds on a 8.xx
> version postgresql server on our 7.4.13 it takes around 15-20 minutes.
> Since we are using RHEL4 on our server we are stuck with 7.4.13. The
> enormous time difference between the different builds drives me crazy.
> Can you please help me identifying the bottleneck or suggest anything
> to improve the dismal performance.

You are absolutely on the right list. A couple of points.

1: Which 8.xx? 8.0.x or 8.1.x? 8.1.x is literally light years ahead
of 7.4 in terms of performance. 8.0 is somewhere between them. The
performance difference you're seeing is pretty common.

2: Looking at your query, there are places where you're joining on
things like date_trunc(...). In 7.4 the database will not, and cannot
use a normal index on the date field for those kinds of things. It can,
however, use a funtional index on some of them. Try creating an index
on date_trunc('day',yourfieldhere) and see if that helps.

3: You are NOT Stuck on 7.4.13. I have a RHEL server that will be
running 8.1.4 or so pretty soon as a dataware house. It may get updated
to RHEL4, may not. You can either compile from the .tar.[gz|bz2] files
or download the PGDG rpms for your distro.

4: You are fighting an uphill battle. There were a LOT of improvements
made all over in the march from 7.4 to 8.1. Not all of them were simple
planner tweaks and shortcuts, but honest to goodness changes to the way
things happen. No amount of tuning can make 7.4 run as fast as 8.1.