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 C31319F9C10 for ; Thu, 4 May 2006 15:37:46 -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 05340-03 for ; Thu, 4 May 2006 15:37:24 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from ausimss.pervasive.com (ausimss.pervasive.com [66.45.103.246]) by postgresql.org (Postfix) with ESMTP id B41799F9B76 for ; Thu, 4 May 2006 15:37:22 -0300 (ADT) Received: from ausbayes2.aus.pervasive.com ([172.16.8.6]) by ausimss.pervasive.com with InterScan Messaging Security Suite; Thu, 04 May 2006 13:37:18 -0500 Received: from AUSOWA.aus.pervasive.com ([172.16.4.8]) by ausbayes2.aus.pervasive.com with Microsoft SMTPSVC(5.0.2195.6713); Thu, 4 May 2006 13:37:17 -0500 Received: from ausmailid.aus.pervasive.com ([172.16.4.64]) by AUSOWA.aus.pervasive.com with Microsoft SMTPSVC(6.0.3790.1830); Thu, 4 May 2006 13:37:17 -0500 X-MimeOLE: Produced By Microsoft Exchange V6.5.7226.0 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Subject: Re: Tale partitioning Date: Thu, 4 May 2006 13:37:17 -0500 Message-ID: <4D27CB1096EF1C408F4BFAB0046EC7B6099F35@ausmailid.aus.pervasive.com> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: [ADMIN] Tale partitioning thread-index: AcZphJHZNQBwRI6WTeK40uEK4pLi0wGGH9vgAAMPCmA= From: "Jim Nasby" To: "Sriram Dandapani" Cc: X-OriginalArrivalTime: 04 May 2006 18:37:17.0161 (UTC) FILETIME=[C4FB7D90:01C66FA9] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200605/44 X-Sequence-Number: 21576 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=20 > some tables > are > 200 million. (and the table size is about 50-100gb) >=20 > 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. =20 > 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. > -----Original Message----- > From: pgsql-admin-owner@postgresql.org > [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Jim C. Nasby > Sent: Wednesday, April 26, 2006 3:51 PM > To: Chris Hoover > Cc: Benjamin Krajmalnik; pgsql-admin@postgresql.org > Subject: Re: [ADMIN] Tale partitioning >=20 > On Wed, Apr 26, 2006 at 04:33:04PM -0400, Chris Hoover wrote: > > Each of the partition tables needs it's own set of indexes. Build > them, and > > see if the does not fix your performance issues. Also, be sure you > turned > > on the constraint_exclusion parameter, and each table=20 > (other than the > > "master") has an constraint on it that is unique. >=20 > I don't believe constraint_exclusion is smart enough to deal=20 > with ORDER > BY / LIMIT yet... :/ > --=20 > 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 >=20 > ---------------------------(end of=20 > broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings >=20