Groups > comp.databases.postgresql > #408

Re: index on a partitioned table

From	Jasen Betts <jasen@xnet.co.nz>
Newsgroups	comp.databases.postgresql
Subject	Re: index on a partitioned table
Date	2012-08-18 00:30 +0000
Organization	Dis (not Dat) Organisation
Message-ID	<k0mnmp$2li$1@reversiblemaps.ath.cx> (permalink)
References	<k0jbq5$n53$2@dont-email.me>

Show all headers | View raw

On 2012-08-16, Greg Hennessy <greg.hennessy@cox.net> wrote:
> I am using postgresql 8.1.3 on redhat,

Upgrade! But seriously, upgrade. 8.1.23 at a minumum.

> and trying to use partitioned
> tables to manage a multi-terabyte table. I've created the parent table
> and the child tables.
>
> If I want to add an index, does it matter if I index the parent or the
> collection of children? I can't find a discussion of the pros or cons
> on this in the documentation.

you need to add it to each of the children that will benefit from
having it. usually this means all of them.

indexing the parent will bave no possitive effect, the database knows it's
really empty

you're going to want to turn constraint excluusion "on" if you haven't
alredy. (8.1 doesn't have a 'partition' setting for constraint exclusiin)
upgrade to someting newer like 9.1 for improved performance.

-- 
⚂⚃ 100% natural

--- Posted via news://freenews.netfront.net/ - Complaints to news@netfront.net ---

Back to comp.databases.postgresql | Previous | Next — Previous in thread | Find similar | Unroll thread

Thread

index on a partitioned table Greg Hennessy <greg.hennessy@cox.net> - 2012-08-16 17:48 +0000
  Re: index on a partitioned table Robert Klemme <shortcutter@googlemail.com> - 2012-08-17 11:11 +0200
  Re: index on a partitioned table Mladen Gogala <gogala.mladen@gmail.com> - 2012-08-17 19:31 +0000
  Re: index on a partitioned table Jasen Betts <jasen@xnet.co.nz> - 2012-08-18 00:30 +0000

csiph-web