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

Re: Partitions and indexes

From: Amitabh Kant <amitabhkant(at)gmail(dot)com>
To: Chetan Suttraway <chetan(dot)suttraway(at)enterprisedb(dot)com>
Cc: PGSQL Mailing List <pgsql-general(at)postgresql(dot)org>
Subject: Re: Partitions and indexes
Date: 2011-02-26 17:04:28
Message-ID: AANLkTim9nF0AHUpDdEjcjh43SgU_dV9foToyBYJz0SuN@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-general
On Fri, Feb 25, 2011 at 1:17 PM, Chetan Suttraway <
chetan(dot)suttraway(at)enterprisedb(dot)com> wrote:

>
>
> On Wed, Feb 23, 2011 at 12:52 PM, Amitabh Kant <amitabhkant(at)gmail(dot)com>wrote:
>
>> Hello
>>
>> If I have partitioned a table based on a foreign key in a manner where
>> every child table will only have data for single value of the foreign key,
>> do I need to create a index for the foreign key in the primary and/or child
>> tables? I am using version 8.4
>>
>> With regards
>>
>> Amitabh
>>
>>
>> Could you post a detailed set of queries?
>
>
> --
> Chetan Sutrave
> http://www.enterprisedb.com
>
>
A simplified representation would be:

Table T1
id int (PK)
name varchar
--
--

Table T2
id int (PK)
T1id int (FK to T1->id)
--
--

Now if I partition the table T2 based on field T1id, making sure that each
distinct T1id is provided its own child table

Table T2C1 (inherited from T2, T1id field only contains 1 for all rows)
Table T2C2 (inherited from T2, T1id field only contains 2 for all rows)
-- 
--

What I would like to know here is that do I need to add an index for T1id
field for either T2 or it's inherited tables (T2C1/T2C2 etc)?

Amitabh

In response to

Responses

pgsql-general by date

Next:From: John R PierceDate: 2011-02-26 17:46:39
Subject: Re: Please help -- Postgres stopped responding ....
Previous:From: Kalai RDate: 2011-02-26 16:46:01
Subject: Re: PostgreSQL database design for a large company

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