X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 6C5C69FA9BD for ; Thu, 4 May 2006 15:49:02 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 05302-09 for ; Thu, 4 May 2006 15:48:44 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from noel.decibel.org (noel.decibel.org [67.100.216.10]) by postgresql.org (Postfix) with ESMTP id 5A03C9F9B76 for ; Thu, 4 May 2006 15:48:44 -0300 (ADT) Received: by noel.decibel.org (Postfix, from userid 1001) id 04A2156431; Thu, 4 May 2006 13:48:43 -0500 (CDT) Received: (hashcash-sendmail, from uid 1001); Thu, 4 May 2006 13:48:42 -0500 Date: Thu, 4 May 2006 13:48:42 -0500 From: "Jim C. Nasby" To: Sriram Dandapani Cc: pgsql-admin@postgresql.org Subject: Re: Tale partitioning Message-ID: <20060504184841.GS97354@pervasive.com> References: <6992E470F12A444BB787B5C937B9D4DF041C362F@ca-mail1.cis.local> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <6992E470F12A444BB787B5C937B9D4DF041C362F@ca-mail1.cis.local> X-Operating-System: FreeBSD 6.0-RELEASE-p4 amd64 X-Distributed: Join the Effort! http://www.distributed.net User-Agent: Mutt/1.5.11 X-Hashcash: 1:20:060504:sdandapani@counterpane.com::tg3zDnhbenxfIZZf:00000000000 0000000000000000000000003Wa3 X-Hashcash: 1:20:060504:pgsql-admin@postgresql.org::+sfUzrgUUR/wwGQM:00000000000 0000000000000000000000004jsv X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200605/46 X-Sequence-Number: 21578 BTW, I should have mentioned that partitioning is a very new feature and that folks probably would like to know about shortcommings you find while using it. On Thu, May 04, 2006 at 11:40:51AM -0700, Sriram Dandapani wrote: > Thanks...looks like partitioning will help. > > -----Original Message----- > From: Jim Nasby [mailto:jnasby@pervasive.com] > Sent: Thursday, May 04, 2006 11:37 AM > To: Sriram Dandapani > Cc: pgsql-admin@postgresql.org > Subject: RE: [ADMIN] Tale partitioning > > Please include the mailing list in your replies so others can provide > input. > > > From: Sriram Dandapani [mailto:sdandapani@counterpane.com] > > Most of our reports use a order by limit X...The rowcount in > > some tables > > are > 200 million. (and the table size is about 50-100gb) > > > > Does the fact that constraint_exclusion doesn't deal with order by > > /limit > > makes partitioning an unwise choice. > > Well, in a worst-case scenario, partitioning will perform no worse than > if you had one giant table. So it's not hurting you, it may just not be > helping you. > > > What if the main query does just an order by and an outer query wraps > > the inner query with a limit.. > > It all depends on if the order-by code is partitioning aware, and I'm > not sure that it is. But if you index on the appropriate column it > should hopefully make use of that... > > > I am trying to figure out if I should use partitioning or not (my goal > > is two-fold..purge lots of data in aged tables and make queries > > partition-aware) > > Well, reason #1 sounds like plenty of justification for using > partitioning to me. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461