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 |
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. |