Re: Simple query takes a long time on win2K

Yes, it is quite a range of hardware, and even within the win2k pro machines
there is quite a range of hardware which is why I would expect all of them
to produce slightly different latency times to serve up the records.  Yet,
all are serving in 4 seconds!  If I saw a pattern that suggested that the
slowest, most inept machine produced the slowest results I'd be satisfied
that it was strictly hardware, but I'm not seeing that.  The slowest machine
serves the records up in the same time as the second fastest machine!

The hardware between all of these machines is as widely varied as you can
get.

The drive on the Celeron 400 was just defragmented and it made no difference
at all.

Fresh reboot on Celeron 2400:  1st query took 561ms, 2nd and thereafter
takes 70ms.

Fresh reboot on PII 233: 1st query took 4300ms, 2nd and thereafter took
4000ms.




-----Original Message-----
From: Lincoln Yeoh [mailto:lyeoh@pop.jaring.my]
Sent: March 23, 2005 7:51 AM
To: A. Mous; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Simple query takes a long time on win2K

They are quite different hardware.

How long does it take for the _first_ time you do the query on the Celeron
machine? The first time. Wait until everything has started up first and the
machine is quiescent.

How long does it take for the _second_ and _third_ times?

Do the same for all the machines.

Are the drives on the machine very different?

How about you analyze the disks on each machine and compare how fragmented
the database files are on the various machines?

128MB RAM is not very much for a Win2K machine. Not very far from swapping.

Win2K pro or Win2K server? Performance optimized for server or
desktop/applications?

Regards,
Link.

At 02:57 AM 3/23/2005 -0700, A. Mous wrote:

>Hi,
>
>I have a table with about 1500 records.  My query is very basic: SELECT *
>FROM foo;
>
>With postgres 8.0.1 on Win XP (Celeron 2400, 500MB RAM) it returns the
>results in about 80ms.  The same query on the same database, tested on
three
>different win2k machines all running 8.0.1, takes roughly 4 seconds.  Win2K
>machines are as follows:
>
>1) PIII 800, 256MB RAM
>2) Celeron 400, 128MB RAM
>3) PII 233, 128MB RAM
>
>All machines are currently using the default settings upon install.  I've
>tried adjusting shared_buffers and work_mem but nothing seems to make any
>difference.
>
>EXPLAIN ANALYZE on WinXP machine gives:
>
>Seq Scan on foo  (cost=0.00..65.71 rows=1471 width=95) (actual
>time=0.000..0.000 rows=1472 loops=1)
>
>Same on #3 Win2K machine gives:
>
>Seq Scan on foo  (cost=0.00..40.72 rows=1472 width=95) (actual
>time=0.000..80.000 rows=1472 loops=1)
>
>All queries are executed locally on the server.  Can anyone please explain
>the profound performance difference here (which appear to be related to the
>OS)?
>
>Much thanks in advance!
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 9: the planner will ignore your desire to choose an index scan if your
>       joining column's datatypes do not match