Thanks...looks like partitioning will help. -----Original Message----- From: Jim Nasby [mailto:jnasby@xxxxxxxxxxxxx] Sent: Thursday, May 04, 2006 11:37 AM To: Sriram Dandapani Cc: pgsql-admin@xxxxxxxxxxxxxx Subject: RE: [ADMIN] Tale partitioning Please include the mailing list in your replies so others can provide input. > From: Sriram Dandapani [mailto:sdandapani@xxxxxxxxxxxxxxx] > 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. > -----Original Message----- > From: pgsql-admin-owner@xxxxxxxxxxxxxx > [mailto:pgsql-admin-owner@xxxxxxxxxxxxxx] On Behalf Of Jim C. Nasby > Sent: Wednesday, April 26, 2006 3:51 PM > To: Chris Hoover > Cc: Benjamin Krajmalnik; pgsql-admin@xxxxxxxxxxxxxx > Subject: Re: [ADMIN] Tale partitioning > > 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 > (other than the > > "master") has an constraint on it that is unique. > > I don't believe constraint_exclusion is smart enough to deal > with ORDER > BY / LIMIT yet... :/ > -- > Jim C. Nasby, Sr. Engineering Consultant jnasby@xxxxxxxxxxxxx > Pervasive Software http://pervasive.com work: 512-231-6117 > vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 > > ---------------------------(end of > broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings >
