Re: Tale partitioning

From: "Sriram Dandapani" <sdandapani(at)counterpane(dot)com>
To: "Jim Nasby" <jnasby(at)pervasive(dot)com>
Cc: <pgsql-admin(at)postgresql(dot)org>
Subject: Re: Tale partitioning
Date: 2006-05-04 18:40:51
Message-ID: 6992E470F12A444BB787B5C937B9D4DF041C362F@ca-mail1.cis.local
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Thanks...looks like partitioning will help.

-----Original Message-----
From: Jim Nasby [mailto:jnasby(at)pervasive(dot)com]
Sent: Thursday, May 04, 2006 11:37 AM
To: Sriram Dandapani
Cc: pgsql-admin(at)postgresql(dot)org
Subject: RE: [ADMIN] Tale partitioning

Please include the mailing list in your replies so others can provide
input.

> From: Sriram Dandapani [mailto:sdandapani(at)counterpane(dot)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.

> -----Original Message-----
> From: pgsql-admin-owner(at)postgresql(dot)org
> [mailto:pgsql-admin-owner(at)postgresql(dot)org] On Behalf Of Jim C. Nasby
> Sent: Wednesday, April 26, 2006 3:51 PM
> To: Chris Hoover
> Cc: Benjamin Krajmalnik; pgsql-admin(at)postgresql(dot)org
> 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(at)pervasive(dot)com
> 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
>

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Jim C. Nasby 2006-05-04 18:48:42 Re: Tale partitioning
Previous Message Jim Nasby 2006-05-04 18:37:17 Re: Tale partitioning