X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 1D10C9DCE34 for ; Mon, 27 Mar 2006 12:32:19 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 01627-05 for ; Mon, 27 Mar 2006 12:32:17 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from mail.Mi8.com (d01gw01.mi8.com [63.240.6.47]) by postgresql.org (Postfix) with ESMTP id 425F39DCAEC for ; Mon, 27 Mar 2006 12:32:15 -0400 (AST) Received: from 172.16.1.148 by mail.Mi8.com with ESMTP (- Welcome to Mi8 Corporation www.Mi8.com (D1)); Mon, 27 Mar 2006 11:32:03 -0500 X-Server-Uuid: 241911D6-425B-44B9-A073-E3FE0F8FC774 Received: from MI8NYCMAIL06.Mi8.com ([172.16.1.160]) by D01HOST02.Mi8.com with Microsoft SMTPSVC(6.0.3790.1830); Mon, 27 Mar 2006 11:31:59 -0500 Received: from 69.181.100.71 ([69.181.100.71]) by MI8NYCMAIL06.Mi8.com ( [172.16.1.219]) via Exchange Front-End Server mi8owa.mi8.com ( [172.16.1.106]) with Microsoft Exchange Server HTTP-DAV ; Mon, 27 Mar 2006 16:31:58 +0000 User-Agent: Microsoft-Entourage/11.2.3.060209 Date: Mon, 27 Mar 2006 08:31:58 -0800 Subject: Re: count(*) performance From: "Luke Lonergan" To: "G=?ISO-8859-1?B?4Q==?=briel =?ISO-8859-1?B?wQ==?=kos" , pgsql-performance@postgresql.org Message-ID: Thread-Topic: [PERFORM] count(*) performance Thread-Index: AcZRo0beqqLMYUUKREuSagzJOKK1GgAGLCml In-Reply-To: <4427E9E8.9030806@i-logic.hu> MIME-Version: 1.0 X-OriginalArrivalTime: 27 Mar 2006 16:31:59.0076 (UTC) FILETIME=[F8284240:01C651BB] X-WSS-ID: 6836CCF62AW1445391-03-01 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=1.351 required=5 tests=[AWL=0.098, RCVD_NUMERIC_HELO=1.253] X-Spam-Score: 1.351 X-Spam-Level: * X-Archive-Number: 200603/522 X-Sequence-Number: 17959 Gabriel, On 3/27/06 5:34 AM, "G=E1briel =C1kos" wrote: > Question: I have a table with 2.5M rows. count(*) on this table is > running 4 minutes long. (dual opteron, 4gig ram, db on 4 disk raid10 > array (sata, not scsi)) Is this normal? How could I make it run faster? > Maybe make it run faster for the 2nd time? Which parameters should I > change in postgresql.conf and how? Before changing anything with your Postgres configuration, you should check your hard drive array performance. All select count(*) does is a sequentia= l scan of your data, and if the table is larger than memory, or if it's the first time you've scanned it, it is limited by your disk speed. To test your disk speed, use the following commands and report the times here: time bash -c "dd if=3D/dev/zero of=3Dbigfile bs=3D8k count=3D500000 && sync" time dd if=3Dbigfile of=3D/dev/null bs=3D8k If these are taking a long time, from another session watch the I/O rate with "vmstat 1" for a while and report that here. - Luke =20