At 08:45 PM 3/04/2008, you wrote:

>> This sounds like a compound PK where one (or more) segments have low
>selectivity and mass inserts and deletes are being performed without
>any follow-up housekeeping. If the PK is getting so out of balance,
>it would be expected that dependent FK indexes would suffer the same
>kind of deterioration.
>>
>> How often do you run SET STATISTICS on your indexes as a maintenance
>measure? This won't rebuild indexes but it should help to improve the
>selectivity of these unstable indexes.
>>
>
>Thanks for the reples, ./heLen and Maxim!
>
>This is a 2 column primary key - id and date - and for most of the
>id-s there's only one record and other id-s (about 1%) have 2-3
>records with different dates. It seems to me that both id and date
>fields have good selectivity.
>
>./heLen, you're right - the table was populated with mass inserts
>without any later housekeeping - i've never used SET STATISTICS
>actually :)

Mass inserts probably account for large blocks of records with the same date--> long chains.

>At the moment i understand it like this: FB optimizer thought that my
>PK index is no good, because index statistics was not up to date?

Probably a combination of selectiveity not up-to-date and index out-of-balance.

>And assuming my mass inserts were random enough (not sorted by PK
>fields) then index doesn't need rebuilding at all?

Inserts are never sorted: the database engine doesn't understand "sorted". In SQL engines "sorted" is an output concept, not a storage concept. Inserts are simply performed in arrival order and nodes are added to the index. Assuming the insert operation is writing CURRENT_DATE to that node through a trigger,
you will get a large batch of nodes having the same date.

./heLen