Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 7721E9FB207 for ; Mon, 8 Jan 2007 11:39:37 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-new, port 10024) with ESMTP id 40828-08 for ; Mon, 8 Jan 2007 11:39:30 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from exprod6og53.obsmtp.com (exprod6og53.obsmtp.com [64.18.1.187]) by postgresql.org (Postfix) with SMTP id 70BE49FB1B9 for ; Mon, 8 Jan 2007 11:39:36 -0400 (AST) Received: from source ([63.240.6.44]) (using TLSv1) by exprod6ob53.postini.com ([64.18.5.12]) with SMTP; Mon, 08 Jan 2007 07:39:33 PST Received: from 172.16.1.114 by mail.mi8.com with ESMTP (- Welcome to Mi8 Corporation www.Mi8.com (D4)); Mon, 08 Jan 2007 10:39:24 -0500 X-Server-Uuid: C8FB4D43-1108-484A-A898-3CBCC7906230 Received: from MI8NYCMAIL08.Mi8.com ([172.16.1.164]) by d01smtp07.Mi8.com with Microsoft SMTPSVC(6.0.3790.1830); Mon, 8 Jan 2007 10:39:22 -0500 Received: from 69.181.100.71 ([69.181.100.71]) by MI8NYCMAIL08.Mi8.com ( [172.16.1.190]) via Exchange Front-End Server mi8owa.mi8.com ( [172.16.1.106]) with Microsoft Exchange Server HTTP-DAV ; Mon, 8 Jan 2007 15:39:22 +0000 User-Agent: Microsoft-Entourage/11.3.2.061213 Date: Mon, 08 Jan 2007 07:39:21 -0800 Subject: Re: table partioning performance From: "Luke Lonergan" To: "Colin Taylor" , pgsql-performance@postgresql.org Message-ID: Thread-Topic: [PERFORM] table partioning performance Thread-Index: AcczOypaaSCyZp8uEduvrwAWy4o9DA== In-Reply-To: <84d3067d0701062037k3229c9ddjbde73b1fd79ee209@mail.gmail.com> MIME-Version: 1.0 X-OriginalArrivalTime: 08 Jan 2007 15:39:22.0340 (UTC) FILETIME=[2B26D240:01C7333B] X-WSS-ID: 69BCBA2127031334692-01-01 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200701/97 X-Sequence-Number: 22698 Colin, On 1/6/07 8:37 PM, "Colin Taylor" wrote: > Hi there, we've partioned a table (using 8.2) by day due to the 50TB of data > (500k row size, 100G rows) we expect to store it in a year. > Our performance on inserts and selects against the master table is > disappointing, 10x slower (with ony 1 partition constraint) than we get by > going to the partioned table directly. Browsing the list I get the impression > this just a case of too many partitions? would be better off doing partitions > of partitions ? Can you post an "explain analyze" of your query here so we can see what's going on? - Luke