Re: Simple query takes a long time on win2K - Mailing list pgsql-general

From Joshua D. Drake
Subject Re: Simple query takes a long time on win2K
Date
Msg-id 42419750.3010200@commandprompt.com
Whole thread Raw
In response to Re: Simple query takes a long time on win2K  (Lincoln Yeoh <lyeoh@pop.jaring.my>)
List pgsql-general
>
> How long does it take for the _second_ and _third_ times?

Just for reference. The reason we want to know about subsequent runs
is that things will be cached.

> Are the drives on the machine very different?

This is where I am leaning without any further information because
the older machine (in theory) are going to have slower drives. If the
celeron has a 7200 rpm machine and the others have 5400 rpm drives...

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

This is also good when was the last time you ran defrag?

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

Depending on what you are doing, you may already be swapping.

It would be good to also see an explain anaylze

Sincerely,

Joshua D. Drake


>
> 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
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
>      subscribe-nomail command to majordomo@postgresql.org so that your
>      message can get through to the mailing list cleanly



--
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com
PostgreSQL Replicator -- production quality replication for PostgreSQL


Attachment

pgsql-general by date:

Previous
From: "A. Mous"
Date:
Subject: Re: Simple query takes a long time on win2K
Next
From: Tom Lane
Date:
Subject: Re: checkpoint_timeout