| 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: | Whole Thread | Raw Message | 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 |
| 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. |