Re: Partitions and indexes

On Sun, Feb 27, 2011 at 12:30 AM, John R Pierce <pierce(at)hogranch(dot)com> wrote:

> On 02/26/11 10:42 AM, Amitabh Kant wrote:
>
>> On Sat, Feb 26, 2011 at 11:52 PM, John R Pierce <pierce(at)hogranch(dot)com<mailto:
>> pierce(at)hogranch(dot)com>> wrote:
>>
>> On 02/26/11 10:01 AM, Alban Hertroys wrote:
>>
>> On 26 Feb 2011, at 18:04, Amitabh Kant wrote:
>>
>> 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)?
>>
>> No. Either would be rather pointless. In the child tables all
>> the values in that index would have the same exact value,
>> which you don't need as constraint exclusion already pointed
>> the planner to the right table. In the parent table there
>> wouldn't be any data to index.
>>
>>
>> the whole idea of one table per row sounds rather odd and
>> pointless to me.<http://www.postgresql.org/mailpref/pgsql-general>
>>
>>
>>
>> Not sure I am getting you correctly, but every table will have multiple
>> rows of data, but for every row in a given table, the value of the FK would
>> be the same.
>>
>
> ah, I misunderstood the original description.
>
> you're not likely to query T2 by T1id, are you? Doing so would return all
> of one of those inherited tables
>
>
Almost all queries will have T1id as one of its parameter, although not the
only parameter.

Amitabh