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 3D97111F65A2 for ; Thu, 20 Apr 2006 12:03:38 -0300 (ADT) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Score: 1.386 X-Spam-Level: * X-Spam-Status: No, score=1.386 required=5 tests=[AWL=0.133, RCVD_NUMERIC_HELO=1.253] Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 1JB6nUR++iOS for ; Thu, 20 Apr 2006 12:03:34 -0300 (ADT) 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 B555411F65A3 for ; Thu, 20 Apr 2006 12:03:31 -0300 (ADT) Received: from 172.16.1.112 by mail.Mi8.com with ESMTP (- Welcome to Mi8 Corporation www.Mi8.com (D1)); Thu, 20 Apr 2006 11:03:11 -0400 X-Server-Uuid: 241911D6-425B-44B9-A073-E3FE0F8FC774 Received: from MI8NYCMAIL06.Mi8.com ([172.16.1.160]) by D01SMTP02.Mi8.com with Microsoft SMTPSVC(6.0.3790.1830); Thu, 20 Apr 2006 11:03:11 -0400 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 ; Thu, 20 Apr 2006 15:03:11 +0000 User-Agent: Microsoft-Entourage/11.2.3.060209 Date: Thu, 20 Apr 2006 08:03:10 -0700 Subject: Re: Quick Performance Poll From: "Luke Lonergan" To: jim@contactbda.com, "Simon Dale" , pgsql-performance@postgresql.org Message-ID: Thread-Topic: [PERFORM] Quick Performance Poll Thread-Index: AcZkiIhvZsN3K4XYSHOiuNMmMRaDoQAAwFAr In-Reply-To: <20060420143830.M5360@contactbda.com> MIME-Version: 1.0 X-OriginalArrivalTime: 20 Apr 2006 15:03:11.0712 (UTC) FILETIME=[8AB70600:01C6648B] X-WSS-ID: 68597D254N02556228-01-01 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200604/451 X-Sequence-Number: 18535 Jim, On 4/20/06 7:40 AM, "Jim Buttafuoco" wrote: > First of all this is NOT a single table and yes I am using partitioning and > the constaint exclusion stuff. the largest > set of tables is over 2T. I have not had to rebuild the biggest database yet, > but for a smaller one ~1T the restore > takes about 12 hours including many indexes on both large and small tables You would probably benefit greatly from the new on-disk bitmap index feature in Bizgres Open Source. It's 8.1 plus the sort speed improvement and on-disk bitmap index. Index creation and sizes for the binary version are in the table below (from a performance report on bizgres network. The version in CVS tip on pgfoundry is much faster on index creation as well. The current drawback to bitmap index is that it isn't very maintainable under insert/update, although it is safe for those operations. For now, you have to drop index, do inserts/updates, rebuild index. We'll have a version that is maintained for insert/update next. - Luke # Indexed Columns Create Time (seconds) Space Used (MBs) BITMAP BTREE BITMAP BTREE 1 L_SHIPMODE 454.8 2217.1 58 1804 2 L_QUANTITY 547.2 937.8 117 1804 3 L_LINENUMBER 374.5 412.4 59 1285 4 L_SHIPMODE, L_QUANTITY 948.7 2933.4 176 2845 5 O_ORDERSTATUS 83.5 241.3 5 321 6 O_ORDERPRIORITY 108.5 679.1 11 580 7 C_MKTSEGMENT 10.9 51.3 1 45 8 C_NATIONKEY 8.3 9.3 2 32