pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Jim Nasby <jnasby@pervasive.com>
To: Sriram Dandapani <sdandapani@counterpane.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: Tale partitioning
Date: Thu, 4 May 2006 13:37:17 -0500
Message-ID: <4D27CB1096EF1C408F4BFAB0046EC7B6099F35@ausmailid.aus.pervasive.com> (raw)

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.

> -----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
> 
> 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@pervasive.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
> 



view thread (7+ messages)  latest in thread

Message-ID: <4D27CB1096EF1C408F4BFAB0046EC7B6099F35@ausmailid.aus.pervasive.com>
Permalink:  ../4D27CB1096EF1C408F4BFAB0046EC7B6099F35@ausmailid.aus.pervasive.com/
Also on:    postgresql.org/message-id/4D27CB1096EF1C408F4BFAB0046EC7B6099F35@ausmailid.aus.pervasive.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: jnasby@pervasive.com, sdandapani@counterpane.com
  Subject: Re: Tale partitioning
  In-Reply-To: <4D27CB1096EF1C408F4BFAB0046EC7B6099F35@ausmailid.aus.pervasive.com>
  References: <4D27CB1096EF1C408F4BFAB0046EC7B6099F35@ausmailid.aus.pervasive.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox