Re: [GENERAL] Partitions and indexes

[Amitabh Kant]

On Sun, Feb 27, 2011 at 12:30 AM, John R Pierce  wrote:

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

Re: [GENERAL] Partitions and indexes

[John R Pierce]

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


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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Partitions and indexes

[Amitabh Kant]

On Sat, Feb 26, 2011 at 11:52 PM, John R Pierce  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.
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> 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.

Amitabh

Re: [GENERAL] Partitions and indexes

[John R Pierce]

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Partitions and indexes

[Alban Hertroys]

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.

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:737,4d69400211732483184779!



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Partitions and indexes

[Amitabh Kant]

On Fri, Feb 25, 2011 at 1:17 PM, Chetan Suttraway <
chetan.suttra...@enterprisedb.com> wrote:

>
>
> On Wed, Feb 23, 2011 at 12:52 PM, Amitabh Kant 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

[GENERAL] Partitions and indexes

[Amitabh Kant]

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