Re: Is it necessary to have index for child table in following case?

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 constraints only apply to single tables, not to their inheritance children. This is true on both the referencing and referenced sides 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 insert speed 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 things down 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 you don'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 SELECT query. I think you'll see that the indexes on the child tables do get used, but the ones on the parent table probably don'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's no point in having an index on it. As you already quoted, indexes can't span multiple tables. The only data in the index 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 reason is the same one that you already quoted for indexes spanning multiple tables - a primary key is implemented using a unique 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 in it. Make sure you always insert a record in the primary key table along with the one referencing it, so that you will get a unique violation when you try to insert a record for which the primary key already exists. This will of course slow things 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, serials generate unique numbers (unless they wrap around when they run out of numbers, but you control whether they're allowed to 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!