> Vlad Khorsun wrote:
>>> But at the moment I have a problem understanding why we're discussing
>>> how to apply constraints _during: the load, since the original idea was
>>> to evaluate them _after_ the load (though I do not have profiling
>>> numbers from Vlad, some maybe indeed CHECK constrainsts can be evaluated
>>> during the load).
>>>
>>
>> I told mostly about FK constraint - it is very expencive to check it during load
>> (or index creation) as such check produced many random reads from parent table(s)
>>
>>
>
> Indeed. But is it cheaper to run it later?

It depends on how many keys in parent relation. In most cases, i believe, parent relation
have much less keys than child one. And note - sequential read of big table almost always
much faster then many random reads even if we'll sort this big table.

Currently, when FK index is created, FB checks each child key against parent table during
scan of child table. This is very expensive compared with creating simple index on the same
fields. I have a code where this check made later - when all keys are sorted and each distinct
key checked in parent table only once (still random reads from parent tables but less). This is
must better in most cases but not always :(
All my test shows performance improvement but at lest one user which tested this feature saw
slowness on the one of big tables. Therefore this code was not passed into FB 2.1.

Regards,
Vlad