Re: Speed issues

On 9 Jun 2004 at 13:44, Dru wrote:

> Ok so i've done some benchmarking to make certain that things are
> actually slow.
>
>
> Development machine:
> Pentium 4 3GHz, 1GB ram. Linux 2.6 with postgresql 7.4.1
> Production machine:
> Pentium 4 2.4GHz with 256MB of ram. winxp home
> Test machine:
> AMD XP 1600 Duron with 512MB of ram. win2k
>
> The linux machine is the only one with 7200RPM drives.
>
What are the drives on the Windows machines then? If they are only
5400 RPM drives then it will be slow, at least for the first few selects
until you have got most of the data in cache.

These machines have wildly varying specs! I dont think you can
compare performance directly between them.

Personally I would say that 256MB of ram for a Windows production
machine for running a database server is way too low. Windows XP/2K
will need a lot of that just for themselves. If there is little room for the
database in cache then you will be hitting the disks a lot, if they are
slow disks then it will run like a dog in syrup, even for such a small
database as yours.

Just running the client app on the same machine may be enough to tip
the balance of memory vs disk and make a big difference. I notice that
postgres is doing hash joins a lot here, again very little memory may
make a big difference.

What are your postgresql.conf setting on each machine?

Can you do an EXPLAIN ANALYZE (not just EXPLAIN) on the query for
the cases where its fast and slow? This may give some idea why there
is a difference. Also let us know the machine(s) that you were running
each statement on.

Regards,
Gary.