Re: Is it necessary to have index for child table in following case? - Mailing list pgsql-general

On 3 Feb 2010, at 3:58, Yan Cheng Cheok wrote:

> Due to the fact
>
> "A serious limitation of the inheritance feature is that indexes (including unique constraints) and foreign key
constraintsonly apply to single tables, not to their inheritance children. This is true on both the referencing and
referencedsides of a foreign key constraint. Thus, in the terms of the above example: " 
>
> When I use table inheritance to implement table partition :
>
> measurement {
>    primary_key
>    foreign_key1
>    foreign_key2
>
>    create foreign_key1_index
>    create foreign_key2_index
> }
>
> measurement_1 inherit from measurement {
>    primary_key
>    foreign_key1
>    foreign_key2
>
>    create foreign_key1_index
>    create foreign_key2_index
> }
>
> However, I realize having foreign_key1_index and foreign_key2_index for child table measurement_1, make up my data
insertspeed slow down by factor of 2~3 

You probably want to check the output of EXPLAIN ANALYZE for your INSERT queries. That should point to what's slowing
thingsdown and can often tell why it does so. If it's not obvious, post the output here ;) 

Are you using INSERT or COPY to insert your data? COPY tends to be a lot faster than separate INSERTs, especially if
youdon't wrap the INSERTs in a transaction block and COMMIT them in batches. 

> I was wondering whether is it necessary for me to create index foreign_key1_index for child table measurement_1?
>
> (1) All my SELECT query is performed on parent table measurement.
> (2) All my data INSERT code is performed based on child table measurement_1.


That depends on whether your SELECT query actually uses those indexes. Again, you can see that by using EXPLAIN on your
SELECTquery. I think you'll see that the indexes on the child tables do get used, but the ones on the parent table
probablydon't. 

If you never insert any data into your parent table (or if it never contains more than a few rows if you do) then
there'sno point in having an index on it. As you already quoted, indexes can't span multiple tables. The only data in
theindex on the parent table is about the records that live there, which probably are none at all. 


As an aside, I hope you do realise that your primary key isn't guaranteed to be unique across your child tables? The
reasonis the same one that you already quoted for indexes spanning multiple tables - a primary key is implemented using
aunique index after all. 

If that matters, what you can do is make your primary key a foreign key to a new table with just the primary key column
init. Make sure you always insert a record in the primary key table along with the one referencing it, so that you will
geta unique violation when you try to insert a record for which the primary key already exists. This will of course
slowthings down some, but if it's necessary that's the price to pay. 

If your primary key is a serial (meaning it's generated by a sequence) you probably don't need to worry about it,
serialsgenerate unique numbers (unless they wrap around when they run out of numbers, but you control whether they're
allowedto and you can design them large enough that it won't happen). 

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,4b6966ba10441687344184!