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 84296D1B19A for ; Mon, 2 Aug 2004 13:10:33 -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 90792-05 for ; Mon, 2 Aug 2004 16:10:28 +0000 (GMT) Received: from mpls-qmqp-01.inet.qwest.net (mpls-qmqp-01.inet.qwest.net [63.231.195.112]) by svr1.postgresql.org (Postfix) with SMTP id 55D86D1B18A for ; Mon, 2 Aug 2004 13:10:26 -0300 (ADT) Received: (qmail 5671 invoked by uid 0); 2 Aug 2004 16:10:26 -0000 Received: from unknown (63.231.195.5) by mpls-qmqp-01.inet.qwest.net with QMQP; 2 Aug 2004 16:10:26 -0000 Received: from 63-227-127-37.dnvr.qwest.net (HELO ?10.0.0.2?) (63.227.127.37) by mpls-pop-05.inet.qwest.net with SMTP; 2 Aug 2004 16:10:25 -0000 Date: Mon, 02 Aug 2004 10:11:46 -0600 Message-Id: <1091463106.27166.33.camel@localhost.localdomain> From: "Scott Marlowe" To: "Joost Kraaijeveld" Cc: pgsql-performance@postgresql.org Subject: Re: What kind of performace can I expect and how to In-Reply-To: References: Content-Type: text/plain Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.6 (1.4.6-2) Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.1 tagged_above=0.0 required=5.0 tests=RCVD_IN_SORBS X-Spam-Level: X-Archive-Number: 200408/15 X-Sequence-Number: 7651 On Mon, 2004-08-02 at 06:21, Joost Kraaijeveld wrote: > Hi all, > > My system is a PostgreSQL 7.4.1 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 20020903 (Red Hat Linux 8.0 3.2-7). It has a Pentium III-733 Mhz with 512 MB ram. It is connected to my workststation (dual XEON 1700 with 1 Gb RAM) with a 100 Mb switched network. > > I have a table with 31 columns, all fixed size datatypes. It contains 88393 rows. Doing a "select * from table" with PGAdmin III in it's SQL window, it takes a total of 9206 ms query runtime an a 40638 ms data retrievel runtime. This means it took the backend about 9 seconds to prepare the data, and 40 or so seconds total (including the 9 I believe) for the client to retrieve and then display it. > Is this a reasonable time to get 88393 rows from the database? Depends on your row size really. I'm certain you're not CPU bound if you've only got one hard drive. Put that data on a 20 way RAID5 array and I'm sure it would come back a little quicker. > If not, what can I do to find the bottleneck (and eventually make it faster)? The bottleneck is almost always IO to start with. First, as another drive and mirror it. Then go to RAID 1+0, then add more and more drives. Read this document about performance tuning: http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html