Re: Partitions and indexes

On 02/26/11 10:42 AM, Amitabh Kant wrote:
> On Sat, Feb 26, 2011 at 11:52 PM, John R Pierce <pierce@hogranch.com
> <mailto:pierce@hogranch.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