Skip site navigation (1) Skip section navigation (2)

Re: indexes on partitioned tables - on the base table, or the partitioned tables?

From: Hrishikesh Mehendale <hrishikesh(dot)mehendale(at)riverbed(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: indexes on partitioned tables - on the base table, or the partitioned tables?
Date: 2009-08-31 16:44:52
Message-ID: 200908310944.52528.hrishikesh.mehendale@riverbed.com (view raw or flat)
Thread:
Lists: pgsql-general
Hi Kevin,

From the PG documentation (section 5.8 - inheritance):
  http://www.postgresql.org/docs/current/static/ddl-inherit.html

"A serious limitation of the inheritance feature is that indexes (including 
unique constraints) and foreign key constraints only apply to single tables, 
not to their inheritance children. This is true on both the referencing and 
referenced sides of a foreign key constraint."

So there's no "best place", there's only one place for the indexes and 
constraints - which is the table(s) that contain the data, which would usually 
be the individual partition tables.

(This caveat seems to be absent from the Partitioning documentation (section 
5.9))

Cheers,
~Hrishi

It was Sunday 30 August 2009 07:17:28 pm that the wise Kevin Kempter thus 
wrote:
> Hi all;
>
> where's the best place for the indexes/constraints on a partitioned table.
>
> I assume it's best to place the FK constraints/triggers on the base/master
> table and the indexes on the individual partition tables.
>
> Thoughts?
>
> Thanks in advance.

In response to

pgsql-general by date

Next:From: bilal ghayyadDate: 2009-08-31 16:51:06
Subject: Re: Query and the number of row result
Previous:From: David KerrDate: 2009-08-31 15:48:24
Subject: Re: Audit Trigger puzzler

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group