• Kevin Grittner's avatar
    Fix an O(N^2) problem in foreign key references. · 5ddc7288
    Kevin Grittner authored
    Commit 45ba424f improved foreign key lookups during bulk updates
    when the FK value does not change.  When restoring a schema dump
    from a database with many (say 100,000) foreign keys, this cache
    would grow very big and every ALTER TABLE command was causing an
    InvalidateConstraintCacheCallBack(), which uses a sequential hash
    table scan.  This could cause a severe performance regression in
    restoring a schema dump (including during pg_upgrade).
    
    The patch uses a heuristic method of detecting when the hash table
    should be destroyed and recreated.
    InvalidateConstraintCacheCallBack() adds the current size of the
    hash table to a counter.  When that sum reaches 1,000,000, the hash
    table is flushed.  This fixes the regression without noticeable
    harm to the bulk update use case.
    
    Jan Wieck
    Backpatch to 9.3 where the performance regression was introduced.
    5ddc7288
ri_triggers.c 103 KB