Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.postgresql > #405 > unrolled thread

index on a partitioned table

Back to article view | Back to comp.databases.postgresql

Contents

  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

#405 — index on a partitioned table

I am using postgresql 8.1.3 on redhat, 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.

[toc] | [next] | [standalone]

#406

On 16.08.2012 19:48, Greg Hennessy wrote:
> I am using postgresql 8.1.3 on redhat, 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.

To me this seems pretty clear: indexes are not automatically inherited 
and the master table is usually empty => index child tables.  It's all 
in the docs:

http://www.postgresql.org/docs/8.1/static/ddl-inherit.html#DDL-INHERIT-CAVEATS
http://www.postgresql.org/docs/8.1/static/ddl-partitioning.html

Cheers

	robert

-- 
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/

[toc] | [prev] | [next] | [standalone]

#407

On Thu, 16 Aug 2012 17:48:53 +0000, Greg Hennessy wrote:

> I am using postgresql 8.1.3 on redhat, 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.

In Postgres, parent table is usually empty. You can only index child 
tables. And there are no global indexes, it isn't possible to have a 
unique index across all the tables.



-- 
Mladen Gogala
http://mgogala.freehostia.com

[toc] | [prev] | [next] | [standalone]

#408

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 ---

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.postgresql

csiph-web