Re: Partitions and indexes

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