Small patch to fix an O(N^2) problem in foreign keys

From: Jan Wieck <jan(at)wi3ck(dot)info>
To: PostgreSQL Development <pgsql-hackers(at)postgresql(dot)org>
Subject: Small patch to fix an O(N^2) problem in foreign keys
Date: 2015-09-03 22:42:49
Message-ID: 55E8CCE9.8020007@wi3ck.info
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Attached is a small patch and a script to reproduce the issue.

The problem is a cache introduced in commit 45ba4247 that improves
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 is growing very big and every ALTER
TABLE command is causing a InvalidateConstraintCacheCallBack(), which
does a sequential hash table scan.

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 improves the schema
restore of a database with 100,000 foreign keys by factor 3.

According to my tests the patch does not interfere with the bulk
updates, the original feature was supposed to improve.

Regards, Jan

--
Jan Wieck
Senior Software Engineer
http://slony.info

Attachment Content-Type Size
pg96_ri_constraint_cache_flush.patch text/x-patch 1.4 KB
t1.sh application/x-shellscript 677 bytes

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tatsuo Ishii 2015-09-03 23:28:19 Re: BRIN INDEX value
Previous Message Corey Huinker 2015-09-03 22:02:32 Re: [POC] FETCH limited by bytes.