X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 04C7C53353 for ; Wed, 20 Apr 2005 21:45:45 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 96904-06 for ; Thu, 21 Apr 2005 00:45:36 +0000 (GMT) Received: from server07.icaen.uiowa.edu (server07.icaen.uiowa.edu [128.255.17.47]) by svr1.postgresql.org (Postfix) with ESMTP id E9B035296E for ; Wed, 20 Apr 2005 21:45:34 -0300 (ADT) Received: from server11.icaen.uiowa.edu (server11.icaen.uiowa.edu [128.255.17.51]) by server07.icaen.uiowa.edu (8.13.3/8.12.9) with ESMTP id j3L0jc97016434; (envelope-from ) Wed, 20 Apr 2005 19:45:38 -0500 (CDT) Received: from [192.168.1.11] (12-215-118-172.client.mchsi.com [12.215.118.172]) (authenticated user=jfmeinel) by server11.icaen.uiowa.edu (8.13.2/smtp-serv-1.7) with ESMTP id j3L0jbr9026232 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256); (envelope-from ) Wed, 20 Apr 2005 19:45:38 -0500 (CDT) Message-ID: <4266F7B3.7050004@arbash-meinel.com> Date: Wed, 20 Apr 2005 19:45:39 -0500 From: John A Meinel User-Agent: Mozilla Thunderbird 1.0.2 (Windows/20050317) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Joel Fradkin , Postgresql Performance Subject: Re: Joel's Performance Issues WAS : Opteron vs Xeon References: <001901c54608$b70ccfc0$797ba8c0@jfradkin> In-Reply-To: <001901c54608$b70ccfc0$797ba8c0@jfradkin> X-Enigmail-Version: 0.91.0.0 Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="------------enig908EB5CB19F1D4D6FF634A4C" X-Virus-Scanned: ClamAV version 0.83, clamav-milter version 0.83 on clamav.icaen.uiowa.edu X-Virus-Scanned: ClamAV version 0.83, clamav-milter version 0.83 on clamav.icaen.uiowa.edu X-Virus-Status: Clean X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200504/544 X-Sequence-Number: 11941 This is an OpenPGP/MIME signed message (RFC 2440 and 3156) --------------enig908EB5CB19F1D4D6FF634A4C Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Joel Fradkin wrote: >I did think of something similar just loading the data tables with junk >records and I may visit that idea with Josh. > >I did just do some comparisons on timing of a plain select * from tbl where >indexed column = x and it was considerably slower then both MSSQL and MYSQL, >so I am still a bit confused. This still might be configuration issue (I ran >on my 2gig desktop and the 8 gig Linux box comparisons were all ran on the >same machines as far MSSQL, MYSQL, and Postgres. >I turned off postgres when running MYSQL and turned off MYSQL when running >postgres, MSSQL had one of the two running while I tested it. > >For the 360,000 records returned MYSQL did it in 40 seconds first run and 17 >seconds second run. > >MSSQL did it in 56 seconds first run and 16 seconds second run. > >Postgres was on the second run >Total query runtime: 17109 ms. >Data retrieval runtime: 72188 ms. >331640 rows retrieved. > >So like 89 on the second run. >The first run was 147 secs all told. > >These are all on my 2 meg desktop running XP. >I can post the config. I noticed the postgres was using 70% of the cpu while >MSSQL was 100%. > >Joel Fradkin > > Why is MYSQL returning 360,000 rows, while Postgres is only returning 330,000? This may not be important at all, though. I also assume you are selecting from a plain table, not a view. I suppose knowing your work_mem, and shared_buffers settings would be useful. How were you measuring "data retrieval time"? And how does this compare to what you were measuring on the other machines? It might be possible that what you are really measuring is just the time it takes psql to load up all the data into memory, and then print it out. And since psql defaults to measuring entry lengths for each column, this may not be truly comparable. It *looks* like it only takes 18s for postgres to get the data, but then it is taking 72s to transfer the data to you. That would be network latency, or something like that, not database latency. And I would say that 18s is very close to 16 or 17 seconds. I don't know what commands you were issuing, or how you measured, though. You might be using some other interface (like ODBC), which I can't say too much about. John =:-> --------------enig908EB5CB19F1D4D6FF634A4C Content-Type: application/pgp-signature; name="signature.asc" Content-Description: OpenPGP digital signature Content-Disposition: attachment; filename="signature.asc" -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.0 (Cygwin) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCZvezJdeBCYSNAAMRAvMUAKDElIXWAYM4BXNTTKYrW/P7+52pWgCgg7M8 52rFCC5X8Ne8GDuKW4AXf1s= =yLxP -----END PGP SIGNATURE----- --------------enig908EB5CB19F1D4D6FF634A4C--