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(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