X-Original-To: pgsql-hackers@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id D8757475F00 for ; Sat, 19 Apr 2003 15:03:06 -0400 (EDT) Received: from megazone.bigpanda.com (megazone.bigpanda.com [63.150.15.178]) by postgresql.org (Postfix) with ESMTP id D5F30475AA9 for ; Sat, 19 Apr 2003 15:03:01 -0400 (EDT) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id 4F5AFD60D; Sat, 19 Apr 2003 12:03:02 -0700 (PDT) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id 451555C03; Sat, 19 Apr 2003 12:03:02 -0700 (PDT) Date: Sat, 19 Apr 2003 12:03:02 -0700 (PDT) From: Stephan Szabo To: Tom Lane Cc: Kevin Brown , Subject: Re: [PERFORM] Foreign key performance In-Reply-To: <20030418080950.R2132-100000@megazone23.bigpanda.com> Message-ID: <20030419114146.E18449-200000@megazone23.bigpanda.com> MIME-Version: 1.0 Content-Type: MULTIPART/MIXED; BOUNDARY="0-194152151-1050778982=:18449" X-Spam-Status: No, hits=-13.0 required=5.0 tests=BAYES_01,IN_REP_TO,QUOTED_EMAIL_TEXT autolearn=ham version=2.50 X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp) X-Archive-Number: 200304/716 X-Sequence-Number: 38206 This message is in MIME format. The first part should be readable text, while the remaining parts are likely unreadable without MIME-aware tools. Send mail to mime@docserver.cac.washington.edu for more info. --0-194152151-1050778982=:18449 Content-Type: TEXT/PLAIN; charset=US-ASCII On Fri, 18 Apr 2003, Stephan Szabo wrote: > On Fri, 18 Apr 2003, Tom Lane wrote: > > > But the call counts do sound pretty damning. > > Yeah, but even with my hack last night it was still appreciably slower > than immediate constraints. Comparing the call counts in that function > for the immediate versus deferred(hacked) weren't giving me a good idea of > where that time was going. This last was due to assert checking I think. AllocSetCheck was the big time waster on the hacked deferred case. Turning off assert checking I get: Median over 3 100000 inserts in one transaction (excepting the original code which took a really long time so I ran it once) from time psql ... No Fk 24.14s Immediate FK 42.80s Original Deferred FK 1862.06s Hacked Deferred FK 35.30s The hack was just the keeping around the list pointer from the last run through (see attached - passed simple fk tests and regression, but there might be problems I don't see). Looking at the code, I also wonder if we would get some gain by not allocating the per_tuple_context at the beginning but only when a non-deferred constraint is found since otherwise we're creating and destroying the context and possibly never using it. The cost would presumably be some boolean tests inside the inner loop. --0-194152151-1050778982=:18449 Content-Type: TEXT/PLAIN; charset=US-ASCII; name="trigger.patch" Content-Transfer-Encoding: BASE64 Content-ID: <20030419120302.O18449@megazone23.bigpanda.com> Content-Description: Content-Disposition: attachment; filename="trigger.patch" KioqIHNyYy9iYWNrZW5kL2NvbW1hbmRzL3RyaWdnZXIuYwkyMDAzLTA0LTE5 IDEyOjQxOjI0LjAwMDAwMDAwMCAtMDcwMA0KLS0tIHNyYy9iYWNrZW5kL2Nv bW1hbmRzL3RyaWdnZXIuaGFjazEuYwkyMDAzLTA0LTE5IDEzOjQxOjE3LjAw MDAwMDAwMCAtMDcwMA0KKioqKioqKioqKioqKioqDQoqKiogMTYyNiwxNjM3 ICoqKioNCi0tLSAxNjI2LDE2NDMgLS0tLQ0KICAgKiBCZWNhdXNlIHRoaXMg Y2FuIGdyb3cgcHJldHR5IGxhcmdlLCB3ZSBkb24ndCB1c2Ugc2VwYXJhdGUg TGlzdCBub2RlcywNCiAgICogYnV0IGluc3RlYWQgdGhyZWFkIHRoZSBsaXN0 IHRocm91Z2ggdGhlIGR0ZV9uZXh0IGZpZWxkcyBvZiB0aGUgbWVtYmVyDQog ICAqIG5vZGVzLiAgU2F2ZXMganVzdCBhIGZldyBieXRlcyBwZXIgZW50cnks IGJ1dCB0aGF0IGFkZHMgdXAuDQorICAqIA0KKyAgKiBkZWZ0cmlnX2V2ZW50 c19pbW0gaG9sZHMgdGhlIHRhaWwgcG9pbnRlciBhcyBvZiB0aGUgbGFzdCAN CisgICogZGVmZXJyZWRUcmlnZ2VySW52b2tlRXZlbnRzIGNhbGwsIHNpbmNl IGdlbmVyYWxseSBpbW1lZGlhdGUgdHJpZ2dlcnMNCisgICogY2FuJ3QgZXhp c3QgYmVmb3JlIHRoaXMgcG9pbnQgKHNpbmNlIHRoZXkgc2hvdWxkIGFscmVh ZHkgaGF2ZSBiZWVuDQorICAqIGNhbGxlZCkgZXhjZXB0aW5nIFNFVCBDT05T VFJBSU5UUw0KICAgKg0KICAgKiBYWFggTmVlZCB0byBiZSBhYmxlIHRvIHNo b3ZlIHRoaXMgZGF0YSBvdXQgdG8gYSBmaWxlIGlmIGl0IGdyb3dzIHRvbw0K ICAgKgkgICBsYXJnZS4uLg0KICAgKiAtLS0tLS0tLS0tDQogICAqLw0KICBz dGF0aWMgRGVmZXJyZWRUcmlnZ2VyRXZlbnQgZGVmdHJpZ19ldmVudHM7DQor IHN0YXRpYyBEZWZlcnJlZFRyaWdnZXJFdmVudCBkZWZ0cmlnX2V2ZW50c19p bW07DQogIHN0YXRpYyBEZWZlcnJlZFRyaWdnZXJFdmVudCBkZWZ0cmlnX2V2 ZW50X3RhaWw7DQogIA0KICANCioqKioqKioqKioqKioqKg0KKioqIDE4NDQs MTg1MCAqKioqDQogIHN0YXRpYyB2b2lkDQogIGRlZmVycmVkVHJpZ2dlcklu dm9rZUV2ZW50cyhib29sIGltbWVkaWF0ZV9vbmx5KQ0KICB7DQohIAlEZWZl cnJlZFRyaWdnZXJFdmVudCBldmVudCwNCiAgCQkJCXByZXZfZXZlbnQgPSBO VUxMOw0KICAJTWVtb3J5Q29udGV4dCBwZXJfdHVwbGVfY29udGV4dDsNCiAg CVJlbGF0aW9uCXJlbCA9IE5VTEw7DQotLS0gMTg1MCwxODU2IC0tLS0NCiAg c3RhdGljIHZvaWQNCiAgZGVmZXJyZWRUcmlnZ2VySW52b2tlRXZlbnRzKGJv b2wgaW1tZWRpYXRlX29ubHkpDQogIHsNCiEgCURlZmVycmVkVHJpZ2dlckV2 ZW50IGV2ZW50ID0gTlVMTCwNCiAgCQkJCXByZXZfZXZlbnQgPSBOVUxMOw0K ICAJTWVtb3J5Q29udGV4dCBwZXJfdHVwbGVfY29udGV4dDsNCiAgCVJlbGF0 aW9uCXJlbCA9IE5VTEw7DQoqKioqKioqKioqKioqKioNCioqKiAxODY5LDE4 ODAgKioqKg0KICAJLyogTWFrZSBhIHBlci10dXBsZSBtZW1vcnkgY29udGV4 dCBmb3IgdHJpZ2dlciBmdW5jdGlvbiBjYWxscyAqLw0KICAJcGVyX3R1cGxl X2NvbnRleHQgPQ0KICAJCUFsbG9jU2V0Q29udGV4dENyZWF0ZShDdXJyZW50 TWVtb3J5Q29udGV4dCwNCiEgCQkJCQkJCSAgIkRlZmVycmVkVHJpZ2dlclR1 cGxlQ29udGV4dCIsDQohIAkJCQkJCQkgIEFMTE9DU0VUX0RFRkFVTFRfTUlO U0laRSwNCiEgCQkJCQkJCSAgQUxMT0NTRVRfREVGQVVMVF9JTklUU0laRSwN CiEgCQkJCQkJCSAgQUxMT0NTRVRfREVGQVVMVF9NQVhTSVpFKTsNCiAgDQot IAlldmVudCA9IGRlZnRyaWdfZXZlbnRzOw0KICAJd2hpbGUgKGV2ZW50ICE9 IE5VTEwpDQogIAl7DQogIAkJYm9vbAkJc3RpbGxfZGVmZXJyZWRfb25lcyA9 IGZhbHNlOw0KLS0tIDE4NzUsMTg5NyAtLS0tDQogIAkvKiBNYWtlIGEgcGVy LXR1cGxlIG1lbW9yeSBjb250ZXh0IGZvciB0cmlnZ2VyIGZ1bmN0aW9uIGNh bGxzICovDQogIAlwZXJfdHVwbGVfY29udGV4dCA9DQogIAkJQWxsb2NTZXRD b250ZXh0Q3JlYXRlKEN1cnJlbnRNZW1vcnlDb250ZXh0LA0KISAJCQkgICJE ZWZlcnJlZFRyaWdnZXJUdXBsZUNvbnRleHQiLA0KISAJCQkgIEFMTE9DU0VU X0RFRkFVTFRfTUlOU0laRSwNCiEgCQkJICBBTExPQ1NFVF9ERUZBVUxUX0lO SVRTSVpFLA0KISAJCQkgIEFMTE9DU0VUX0RFRkFVTFRfTUFYU0laRSk7DQoh IA0KISAJLyoNCiEgCSAqIElmIGltbWVkaWF0ZV9vbmx5IGlzIHRydWUsIHRo ZW4gdGhlIG9ubHkgZXZlbnRzIHRoYXQgY291bGQgaGF2ZSBmaXJlZA0KISAJ ICogYXJlIHRob3NlIHNpbmNlIGRlZnRyaWdfZXZlbnRzX2ltbS4gIA0KISAJ ICogVGhlIG9uZSBleGNlcHRpb24gdG8gb25seSBzaW5jZSBkZWZ0cmlnX2V2 ZW50c19pbW0gd291bGQgYmUgDQohIAkgKiBTRVQgQ09OU1RSQUlOVFMgLi4u IElNTUVESUFURSBzbyB3ZSBzZXQgaXQgdG8gTlVMTCBpbiB0aGVyZQ0KISAJ ICogYW5kIHRyZWF0IE5VTEwgYXMgYmVpbmcgZXF1aXZhbGVudCB0byB0aGUg ZW50aXJlIGxpc3QuDQohIAkgKi8NCiEgCWlmIChpbW1lZGlhdGVfb25seSkg DQohIAkJZXZlbnQgPSBkZWZ0cmlnX2V2ZW50c19pbW07DQohIAlpZiAoZXZl bnQgPT0gTlVMTCkNCiEgCQlldmVudCA9IGRlZnRyaWdfZXZlbnRzOw0KICAN CiAgCXdoaWxlIChldmVudCAhPSBOVUxMKQ0KICAJew0KICAJCWJvb2wJCXN0 aWxsX2RlZmVycmVkX29uZXMgPSBmYWxzZTsNCioqKioqKioqKioqKioqKg0K KioqIDE5OTMsMjAwNCAqKioqDQotLS0gMjAxMCwyMDI1IC0tLS0NCiAgCS8q IFVwZGF0ZSBsaXN0IHRhaWwgcG9pbnRlciBpbiBjYXNlIHdlIGp1c3QgZGVs ZXRlZCB0YWlsIGV2ZW50ICovDQogIAlkZWZ0cmlnX2V2ZW50X3RhaWwgPSBw cmV2X2V2ZW50Ow0KICANCisgCS8qIFNldCB0aGUgaW1tZWRpYXRlIGV2ZW50 IHBvaW50ZXIgdG8gdGhlIHRhaWwgKi8NCisgCWRlZnRyaWdfZXZlbnRzX2lt bSA9IHByZXZfZXZlbnQ7DQorIA0KICAJLyogUmVsZWFzZSB3b3JraW5nIHJl c291cmNlcyAqLw0KICAJaWYgKHJlbCkNCiAgCQloZWFwX2Nsb3NlKHJlbCwg Tm9Mb2NrKTsNCiAgCUZyZWVUcmlnZ2VyRGVzYyh0cmlnZGVzYyk7DQogIAlp ZiAoZmluZm8pDQogIAkJcGZyZWUoZmluZm8pOw0KKyANCiAgCU1lbW9yeUNv bnRleHREZWxldGUocGVyX3R1cGxlX2NvbnRleHQpOw0KICB9DQogIA0KKioq KioqKioqKioqKioqDQoqKiogMjI3NCwyMjc5ICoqKioNCi0tLSAyMjk1LDIz MDggLS0tLQ0KICAJfQ0KICANCiAgCS8qDQorIAkgKiBSZXNldCB0aGUgcG9p bnRlciBmb3IgdGhlIGltbWVkaWF0ZSBjb25zdHJhaW50cyB0byBOVUxMIChp ZQ0KKyAJICogdXNlIGJlZ2lubmluZyBvZiBsaXN0KS4gIFRoaXMgaXMgcmVh bGx5IG9ubHkgbmVjZXNzYXJ5IGZvcg0KKyAJICogZGVhbGluZyB3aGVuIHNl dHRpbmcgdG8gSU1NRURJQVRFLCBhbmQgY291bGQgcmVhbGx5IHBvaW50DQor IAkgKiB0aGUgZmlyc3QgY29uc3RyYWludCB3ZSBjaGFuZ2VkLCBidXQgZm9y IG5vdyB0aGlzIHdpbGwgZG8uDQorIAkgKi8NCisgCWRlZnRyaWdfZXZlbnRz X2ltbSA9IE5VTEw7DQorIA0KKyAJLyoNCiAgCSAqIFNRTDk5IHJlcXVpcmVz IHRoYXQgd2hlbiBhIGNvbnN0cmFpbnQgaXMgc2V0IHRvIElNTUVESUFURSwg YW55DQogIAkgKiBkZWZlcnJlZCBjaGVja3MgYWdhaW5zdCB0aGF0IGNvbnN0 cmFpbnQgbXVzdCBiZSBtYWRlIHdoZW4gdGhlIFNFVA0KICAJICogQ09OU1RS QUlOVFMgY29tbWFuZCBpcyBleGVjdXRlZCAtLSBpLmUuIHRoZSBlZmZlY3Rz IG9mIHRoZSBTRVQNCg== --0-194152151-1050778982=:18449--