Re: slow server

Marc Wrubleski <mlwruble@sorexsoftware.com> writes:
> One thing to think about is the PIII was installed via RPM and the
> Celeron wass compiled on that machine. Could this be the limiting
> factor?

It would be worth trying to compile 7.0.3 from source on the PIII,
rather than using the RPM installation.  That would eliminate a whole
set of variables about configure options, compiler and compiler options,
etc.

Besides which, if you do compile from source, you'll be set up to try
my next suggestion ;-).  Which is to get a profile and find out where
the time is going.  The whole thing seems quite strange to me; I can
think of patches that might have changed 7.0.3's performance, but not
on that query, and not by that much.

If the source compilation doesn't show any change in performance,
then do this to get a profile:

1. Go into src/backend of the built tree and do
    make clean
    make PROFILE=-pg all
This will build a new postgres executable with profiling support.
Install the executable by shutting down the postmaster and doing
"make install-bin" in that same directory.  (You might want to save
the old executable so you don't have to rebuild when you're done
profiling.)

2. Run the problem query.  The way you were doing it (1000 iterations
on one connection) is good.  Then close the connection.

3. You should find a file "gmon.out" in the $PGDATA/base/YOURDB/
directory after the backend exits.  Run
    gprof /path/to/postgres/executable $PGDATA/base/YOURDB/gmon.out
to produce the profile (it's long, so send it into a file).  Be sure
to give the path to the profiling-enabled executable.

4. Then reinstall the non-profiling backend so you don't pay for
profiling all the time.

The profile output is probably too verbose to post here, but if you'll
send it to me I'll see what I can figure out.

            regards, tom lane