Re: Optimising Foreign Key checks

On 6/2/13 4:45 AM, Simon Riggs wrote:
>> >Will this add too much cost where it doesn't help? I don't know what to
>> >predict there. There's the obvious case of trivial transactions with no more
>> >than one referential integrity check per FK, but there's also the case of a
>> >transaction with many FK checks all searching different keys. If the hash hit
>> >rate (key duplication rate) is low, the hash can consume considerably more
>> >memory than the trigger queue without preventing many RI queries. What sort
>> >of heuristic could we use to avoid pessimizing such cases?
> I've struggled with that for a while now. Probably all we can say is
> that there might be one, and if there is not, then manual decoration
> of the transaction will be the way to go.

Just an idea... each backend could keep a store that indicates what FKs this would help with. For example, any time we hit a transaction that exercises the same FK more than once, we stick the OID of the FK constraint (or maybe of the two tables) into a hash that's in that backend's top memory context. (Or if we want to be real fancy, shared mem).
--
Jim C. Nasby, Data Architect jim(at)nasby(dot)net
512.569.9461 (cell) http://jim.nasby.net