Re: Partitions and indexes - Mailing list pgsql-general

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