Received: from localhost (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 77D6B64FD93
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Wed, 12 Nov 2008 12:27:54 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 51598-05
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Wed, 12 Nov 2008 12:27:49 -0400 (AST)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from web34202.mail.mud.yahoo.com (web34202.mail.mud.yahoo.com
	[66.163.178.117])
	by mail.postgresql.org (Postfix) with SMTP id E604264FD42
	for <pgsql-performance@postgresql.org>;
	Wed, 12 Nov 2008 12:27:48 -0400 (AST)
Received: (qmail 42618 invoked by uid 60001); 12 Nov 2008 16:27:46 -0000
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=X-YMail-OSG:Received:X-Mailer:Date:From:Subject:To:MIME-Version:Content-Type:Message-ID;
	b=PQaEzGqhOntZGaiAJCOxyQiQ/uwaje/rZxO070YVKvEksvKuC0kPQPNr/QJk2J1hNKk+t9vfq8TbkzGD/+7GENkhtkv9+adidsWHU+Gj8mDbxZ1LWevNhFAt/mUslBY7eWAcVPItuFyKRu15mDeNqSyrls5UjEzqZxXMt5F8EG4=;
X-YMail-OSG: 
 qvNY4lIVM1mXc1tSiZv.enTuFNLJwh07_fC_Soa3Ep8zGkt6MZre_nLujQaNcevDQJdUt3BLRwf8O9flZ3QMofVe.bN.08xt__xG1XUIhrYF3yybLATWMZNoIPDkY6kJbshD9ifoaS4I54Q7lZ8MmCNWWkS2mHoPH4xamdQo
Received: from [71.41.44.139] by web34202.mail.mud.yahoo.com via HTTP;
	Wed, 12 Nov 2008 08:27:46 PST
X-Mailer: YahooMailRC/1155.29 YahooMailWebService/0.7.260.1
Date: Wed, 12 Nov 2008 08:27:46 -0800 (PST)
From: - - <themanatuf@yahoo.com>
Subject: Performance Question
To: pgsql-performance@postgresql.org
MIME-Version: 1.0
Content-Type: multipart/alternative; boundary="0-1445783529-1226507266=:42593"
Message-ID: <838490.42593.qm@web34202.mail.mud.yahoo.com>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.001 tagged_above=0 required=5
	tests=HTML_MESSAGE=0.001
X-Spam-Level: 
X-Archive-Number: 200811/91
X-Sequence-Number: 31822

--0-1445783529-1226507266=:42593
Content-Type: text/plain; charset=us-ascii

I've been searching for performance metrics and tweaks for a few weeks now. I'm trying to determine if the length of time to process my queries is accurate or not and I'm having a difficult time determining that. I know postgres performance is very dependent on hardware and settings and I understand how difficult it is to tackle. However, I was wondering if I could get some feedback based on my results please.

The database is running on a dual-core 2GHz Opteron processor with 8GB of RAM. The drives are 10K RPM 146GB drives in RAID 5 (I've read RAID 5 is bad for Postgres, but moving the database to another server didn't change performance at all). Some of the key parameters from postgresql.conf are:

max_connections = 100
shared_buffers = 16MB
work_mem = 64MB
everything else is set to the default

One of my tables has 660,000 records and doing a SELECT * from that table (without any joins or sorts) takes 72 seconds. Ordering the table based on 3 columns almost doubles that time to an average of 123 seconds. To me, those numbers are crazy slow and I don't understand why the queries are taking so long. The tables are UTF-8 encode and contain a mix of languages (English, Spanish, etc). I'm running the query from pgadmin3 on a remote host. The server has nothing else running on it except the database.

As a test I tried splitting up the data across a number of other tables. I ran 10 queries (to correspond with the 10 tables) with a UNION ALL to join the results together. This was even slower, taking an average of 103 seconds to complete the generic select all query.

I'm convinced something is wrong, I just can't pinpoint where it is. I can provide any other information necessary. If anyone has any suggestions it would be greatly appreciated. 



      
--0-1445783529-1226507266=:42593
Content-Type: text/html; charset=us-ascii

<html><head><style type="text/css"><!-- DIV {margin:0px;} --></style></head><body><div style="font-family:arial,helvetica,sans-serif;font-size:10pt"><div>I've been searching for performance metrics and tweaks for a few weeks now. I'm trying to determine if the length of time to process my queries is accurate or not and I'm having a difficult time determining that. I know postgres performance is very dependent on hardware and settings and I understand how difficult it is to tackle. However, I was wondering if I could get some feedback based on my results please.<br><br>The database is running on a dual-core 2GHz Opteron processor with 8GB of RAM. The drives are 10K RPM 146GB drives in RAID 5 (I've read RAID 5 is bad for Postgres, but moving the database to another server didn't change performance at all). Some of the key parameters from postgresql.conf are:<br><br>max_connections = 100<br>shared_buffers = 16MB<br>work_mem = 64MB<br>everything else is set
 to the default<br><br>One of my tables has 660,000 records and doing a SELECT * from that table (without any joins or sorts) takes 72 seconds. Ordering the table based on 3 columns almost doubles that time to an average of 123 seconds. To me, those numbers are crazy slow and I don't understand why the queries are taking so long. The tables are UTF-8 encode and contain a mix of languages (English, Spanish, etc). I'm running the query from pgadmin3 on a remote host. The server has nothing else running on it except the database.<br><br>As a test I tried splitting up the data across a number of other tables. I ran 10 queries (to correspond with the 10 tables) with a UNION ALL to join the results together. This was even slower, taking an average of 103 seconds to complete the generic select all query.<br><br>I'm convinced something is wrong, I just can't pinpoint where it is. I can provide any other information necessary. If anyone has any suggestions it
 would be greatly appreciated. <br></div></div><br>

      </body></html>
--0-1445783529-1226507266=:42593--