Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1j28AV-0005Z3-8a for pgsql-bugs@arkaria.postgresql.org; Thu, 13 Feb 2020 06:40:11 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1j28AT-0001Qp-Fs for pgsql-bugs@arkaria.postgresql.org; Thu, 13 Feb 2020 06:40:09 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1j28AT-0001Q0-80 for pgsql-bugs@lists.postgresql.org; Thu, 13 Feb 2020 06:40:09 +0000 Received: from wrigleys.postgresql.org ([2620:122:b000:7::246]) by magus.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1j28AQ-0001Df-2A for pgsql-bugs@lists.postgresql.org; Thu, 13 Feb 2020 06:40:08 +0000 Content-Type: multipart/mixed; boundary="===============4635624349243693645==" MIME-Version: 1.0 Subject: BUG #16256: Checking deferred FK constraints when updating PK is ordrs of magnitude slower than when updating FK To: pgsql-bugs@lists.postgresql.org From: PG Bug reporting form Cc: valentin.male.kovalenko@gmail.com Reply-To: valentin.male.kovalenko@gmail.com, pgsql-bugs@lists.postgresql.org Date: Thu, 13 Feb 2020 06:39:42 +0000 Message-ID: <16256-2cdabbe938e57aff@postgresql.org> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --===============4635624349243693645== Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: base64 VGhlIGZvbGxvd2luZyBidWcgaGFzIGJlZW4gbG9nZ2VkIG9uIHRoZSB3ZWJzaXRlOgoKQnVnIHJl ZmVyZW5jZTogICAgICAxNjI1NgpMb2dnZWQgYnk6ICAgICAgICAgIFZhbGVudGluIEtvdmFsZW5r bwpFbWFpbCBhZGRyZXNzOiAgICAgIHZhbGVudGluLm1hbGUua292YWxlbmtvQGdtYWlsLmNvbQpQ b3N0Z3JlU1FMIHZlcnNpb246IDEyLjEKT3BlcmF0aW5nIHN5c3RlbTogICBVYnVudHUgMTguMDQu MiBMVFMKRGVzY3JpcHRpb246ICAgICAgICAKCkVudmlyb25tZW50Og0KT1M6DQogIFVidW50dSAx OC4wNC4yIExUUw0KUG9zdGdyZVNRTDogDQogIHNlbGVjdCB2ZXJzaW9uKCk7IHJldHVybnMgJ1Bv c3RncmVTUUwgMTIuMSAoVWJ1bnR1IDEyLjEtMS5wZ2RnMTguMDQrMSkgb24KeDg2XzY0LXBjLWxp bnV4LWdudSwgY29tcGlsZWQgYnkgZ2NjIChVYnVudHUgNy40LjAtMXVidW50dTF+MTguMDQuMSkg Ny40LjAsCjY0LWJpdCcuDQogIFBHIGlzIHNldHVwIGFzIHNwZWNpZmllZCBhdApodHRwczovL3d3 dy5wb3N0Z3Jlc3FsLm9yZy9kb3dubG9hZC9saW51eC91YnVudHUvLg0KDQpTZXR1cDoNCjEpIElu c3RhbGwgUEcsIHN0YXJ0IFBHLCBzdGFydCBwc3FsICh0aGUgc2Vzc2lvbiBpcyBpbiBhdXRvLWNv bW1pdCBtb2RlKS4NCjIpIFJ1biB0aGUgZm9sbG93aW5nIGNvbW1hbmRzIHRvIGNyZWF0ZSB0aHJl ZSBmdW5jdGlvbnMgd2hpY2ggc2ltcGxpZnkKcmVwcm9kdWNpbmcgb2YgdGhlIHByb2JsZW06DQoN CmRyb3AgZnVuY3Rpb24gaWYgZXhpc3RzIGluaXRfdGVzdDsNCmNyZWF0ZSBmdW5jdGlvbiBpbml0 X3Rlc3QoaW4gcGtfaG9sZGVyX3NpemUgYmlnaW50LCBpbiBma19ob2xkZXJfc2l6ZQpiaWdpbnQp IHJldHVybnMgdm9pZA0KbGFuZ3VhZ2UgcGxwZ3NxbA0KYXMgJCQNCiAgYmVnaW4NCiAgICBwZXJm b3JtIHNldHNlZWQoMCk7DQogICAgPDxwa19ob2xkZXI+PmJlZ2luDQogICAgICBkcm9wIHRhYmxl IGlmIGV4aXN0cyBwa19ob2xkZXIgY2FzY2FkZTsNCiAgICAgIGNyZWF0ZSB1bmxvZ2dlZCB0YWJs ZSBwa19ob2xkZXIgKHBrIGJpZ2ludCBwcmltYXJ5IGtleSk7DQogICAgICBpbnNlcnQgaW50byBw a19ob2xkZXIgc2VsZWN0ICogZnJvbSBnZW5lcmF0ZV9zZXJpZXMoMCwgcGtfaG9sZGVyX3NpemUK LSAxKTsNCiAgICAgIGFuYWx5emUgcGtfaG9sZGVyOw0KICAgIGVuZDsNCiAgICA8PGZrX2hvbGRl cj4+YmVnaW4NCiAgICAgIGRyb3AgdGFibGUgaWYgZXhpc3RzIGZrX2hvbGRlcjsNCiAgICAgIGNy ZWF0ZSB1bmxvZ2dlZCB0YWJsZSBma19ob2xkZXIgKA0KICAgICAgICBmayBiaWdpbnQgbm90IG51 bGwsDQogICAgICAgIHNlcmlhbCBiaWdpbnQgdW5pcXVlIG5vdCBudWxsLA0KICAgICAgICBjb25z dHJhaW50IGZrX2hvbGRlcl9ma19ma2V5IGZvcmVpZ24ga2V5IChmaykgcmVmZXJlbmNlcyBwa19o b2xkZXIKKHBrKSBkZWZlcnJhYmxlIGluaXRpYWxseSBpbW1lZGlhdGUpOw0KICAgICAgaW5zZXJ0 IGludG8gZmtfaG9sZGVyIChmaywgc2VyaWFsKSBzZWxlY3QgZmxvb3IocmFuZG9tKCkgKgpwa19o b2xkZXJfc2l6ZSksIGdzLnYgZnJvbSBnZW5lcmF0ZV9zZXJpZXMoMCwgZmtfaG9sZGVyX3NpemUg LSAxKSBhcyBncwoodik7DQogICAgICBhbmFseXplIGZrX2hvbGRlcjsNCiAgICBlbmQ7DQogIGVu ZDsNCiQkOw0KDQpkcm9wIGZ1bmN0aW9uIGlmIGV4aXN0cyB1cGRhdGVfcGs7DQpjcmVhdGUgZnVu Y3Rpb24gdXBkYXRlX3BrKGluIHNpemUgYmlnaW50KSByZXR1cm5zIHZvaWQNCmxhbmd1YWdlIHBs cGdzcWwNCmFzICQkDQogIGRlY2xhcmUNCiAgICBtaW5fZnJlZV9wayBiaWdpbnQ7DQogIGJlZ2lu DQogICAgc2VsZWN0IG1heChwaykgKyAxIGludG8gbWluX2ZyZWVfcGsgZnJvbSBwa19ob2xkZXI7 DQogICAgc2V0IGNvbnN0cmFpbnRzIGZrX2hvbGRlcl9ma19ma2V5IGRlZmVycmVkOw0KICAgIC0t U2ltcGx5IGRvaW5nIHVwZGF0ZSBwa19ob2xkZXIgc2V0IHBrID0gcGsgd2hlcmUgcGsgPCBzaXpl OyBkb2VzIG5vdCBkbwp0aGUgdHJpY2sNCiAgICB1cGRhdGUgcGtfaG9sZGVyIHNldCBwayA9IHBr ICsgbWluX2ZyZWVfcGsgd2hlcmUgcGsgPCBzaXplOw0KICAgIHVwZGF0ZSBwa19ob2xkZXIgc2V0 IHBrID0gcGsgLSBtaW5fZnJlZV9wayB3aGVyZSBwayA+PSBtaW5fZnJlZV9wazsNCiAgZW5kOw0K JCQ7DQoNCmRyb3AgZnVuY3Rpb24gaWYgZXhpc3RzIHVwZGF0ZV9mazsNCmNyZWF0ZSBmdW5jdGlv biB1cGRhdGVfZmsoaW4gc2l6ZSBiaWdpbnQsIGluIGRlZmVycmVkIGJvb2xlYW4pIHJldHVybnMK dm9pZA0KbGFuZ3VhZ2UgcGxwZ3NxbA0KYXMgJCQNCiAgZGVjbGFyZQ0KICAgIG1heF9wayBiaWdp bnQ7DQogIGJlZ2luDQogICAgaWYgZGVmZXJyZWQgdGhlbiBzZXQgY29uc3RyYWludHMgZmtfaG9s ZGVyX2ZrX2ZrZXkgZGVmZXJyZWQ7IGVuZCBpZjsNCiAgICB1cGRhdGUgZmtfaG9sZGVyIHNldCBm ayA9IG1pbiArIGZsb29yKHJhbmRvbSgpICogKG1heCAtIG1pbiArIDEpKQ0KICAgIGZyb20gKHNl bGVjdCBtaW4ocGspIGFzIG1pbiwgbWF4KHBrKSBhcyBtYXggZnJvbSBwa19ob2xkZXIpIGFzCmJv dW5kYXJpZXMNCiAgICB3aGVyZSBzZXJpYWwgPCBzaXplOw0KICBlbmQ7DQokJDsNCg0KUmVwcm9k dWNlOg0KMSkgUnVuICJzZWxlY3QgaW5pdF90ZXN0KDEwMDAwMDAsIDEwMDAwMDApOyIgdG8gaW5p dCB0aGUgdGVzdCBzdGF0ZSAoaXQgYWxzbwphbmFseXplcyB0aGUgY3JlYXRlZCBhbmQgcG9wdWxh dGVkIHRhYmxlcykuIFRoaXMgc3RlcCBjcmVhdGVzIHR3byB1bmxvZ2dlZAp0YWJsZXMgcGtfaG9s ZGVyIGFuZCBma19ob2xkZXIgd2l0aCBhIHNpbmdsZSBGSyBjb25zdHJhaW50IGNoZWNraW5nIHRo YXQKZmtfaG9sZGVyLmZrIHZhbHVlcyBhcmUgYW1vbmcgcGtfaG9sZGVyLnBrIHZhbHVlcy4NCjIp IFJ1biAiXHRpbWluZyIgdG8gZW5hYmxlIHRpbWluZy4NCjMuMSkgUnVuICJiZWdpbjsgc2VsZWN0 IHVwZGF0ZV9waygyMDAwKTsiIHRvIGRlZmVyIHRoZSBGSyBjb25zdHJhaW50IGFuZAp1cGRhdGUg MjAwMCByb3dzIGluIHRoZSBwa19ob2xkZXIgYnkgY2hhbmdpbmcgcGtfaG9sZGVyLnBrIHZhbHVl cyBiYWNrIGFuZApmb3J0aCBzbyB0aGF0IGF0IHRoZSBlbmQgdGhlaXIgdmFsdWVzIHN0YXkgdGhl IHNhbWUsIGJ1dCB0aGUgcm93cyBhcmUKdXBkYXRlZCBhbmQgRksgY29uc3RyYWludHMgd2lsbCBi ZSBjaGVja2VkIG9uIGNvbW1pdCAoc2ltcGx5IGFzc2lnbmluZwpwa19ob2xkZXIucGsgPSBwa19o b2xkZXIucGsgaXMgbm90IGVub3VnaCBmb3IgdGhpcyB0ZXN0KS4NCjMuMikgUnVuICJjb21taXQ7 IiB0byBpbml0aWF0ZSB2YWxpZGF0aW9uIG9mIHRoZSBkZWZlcnJlZCBGSyBjb25zdHJhaW50IGZv cgp0aGUgcm93cyB1cGRhdGVkIGluIHRoZSBwcmV2aW91cyBzdGVwLiBUYWtlIGEgbm90ZSBvbiBo b3cgbG9uZyBpdCB0YWtlcyB0bwpiZSBleGVjdXRlZCBhcyByZXBvcnRlZCBieSBwc3FsIChhYm91 dCAyIG1pbnV0ZXMgZm9yIG1lKS4NCjQuMSkgUnVuICJiZWdpbjsgc2VsZWN0IHVwZGF0ZV9maygy MDAwLCB0cnVlKTsiIHRvIGRlZmVyIHRoZSBGSyBjb25zdHJhaW50CmFuZCB1cGRhdGUgMjAwMCBy b3dzIGluIHRoZSBma19ob2xkZXIgYnkgcmFuZG9tbHkgY2hhbmdpbmcgZmtfaG9sZGVyLmZrCnZh bHVlcy4NCjQuMikgUnVuICJjb21taXQ7IiB0byBpbml0aWF0ZSB2YWxpZGF0aW9uIG9mIHRoZSBk ZWZlcnJlZCBGSyBjb25zdHJhaW50IGZvcgp0aGUgcm93cyB1cGRhdGVkIGluIHRoZSBwcmV2aW91 cyBzdGVwLiBUYWtlIGEgbm90ZSBvbiBob3cgbG9uZyBpdCB0YWtlcyB0bwpiZSBleGVjdXRlZCBh cyByZXBvcnRlZCBieSBwc3FsIChhYm91dCAzMCBtaWxsaXNlY29uZHMgZm9yIG1lKS4NCg0KRmFj dHM6DQpJbiB0aGUgcHJvcG9zZWQgZXhwZXJpbWVudCBjb21taXR0aW5nIGEgdHJhbnNhY3Rpb24g dGhhdCB1cGRhdGVzIFBLIHZhbHVlcwpvbiAyMDAwIHJvd3MgaW4gdGhlIHBrX2hvbGRlciB0YWJs ZSB0YWtlcyAyIG1pbnV0ZXMgLyAzMCBtaWxsaXNlY29uZHMgPSA0MDAwCnRpbWVzIG1vcmUgdGlt ZSB0aGFuIGNvbW1pdHRpbmcgYSB0cmFuc2FjdGlvbiB0aGF0IHVwZGF0ZWQgRksgdmFsdWVzIGlu IDIwMDAKcm93cyBpbiB0aGUgZmtfaG9sZGVyIHRhYmxlLiBCb3RoIHRyYW5zYWN0aW9ucyB1c2Ug dGhlIHNhbWUgZGVmZXJyZWQgRksKY29uc3RyYWludCwgYm90aCB0YWJsZXMgYXJlIHVubG9nZ2Vk Lg0KDQpQcm9ibGVtOg0KTGF0ZW5jeSBvZiB0aGUgY29tbWl0IGNvbW1hbmQgdGhhdCB2YWxpZGF0 ZXMgZGVmZXJyZWQgRksgY29uc3RyYWludHMgYXMgYQpyZXN1bHQgb2YgcHJpbWFyeSBrZXkgdmFs dWVzIGJlaW5nIHVwZGF0ZWQgaXMgc28gbGFyZ2UgdGhhdCBpdCBtYWtlcyB0aGUKZnVuY3Rpb25h bGl0eSB2aXJ0dWFsbHkgdW51c2FibGUuIElmIHRoZSBwZXJmb3JtYW5jZSBpbiBzdWNoIHNjZW5h cmlvcwpjYW5ub3QgYmUgaW1wcm92ZWQsIHRoZW4gdGhlIGRvY3VtZW50YXRpb24gc2hvdWxkIGF0 IGxlYXN0IGV4cGxpY2l0bHkgc3RhdGUKdGhhdCBzdWNoIHNjZW5hcmlvcyBhcmUgZGlzY291cmFn ZWQgYXMgdGhlIGltcGxlbWVudGF0aW9uIGlzIG5vdCBzdXBwb3NlZCB0bwpjb3BlIHdpdGggdGhl bTsgb3RoZXJ3aXNlIGl0IHdvdWxkIGJlIGdyZWF0IHRvIGltcGxlbWVudCB0aGUgdmFsaWRhdGlv biBpbiBhCndheSB0aGF0IGFsbG93cyB1c2luZyBzdWNoIHNjZW5hcmlvcyBpbiBwcmFjdGljZS4K Cg== --===============4635624349243693645==--