Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1ovx9E-0001zP-Pt for pgsql-hackers@arkaria.postgresql.org; Fri, 18 Nov 2022 08:54:56 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1ovx9D-0001TD-DS for pgsql-hackers@arkaria.postgresql.org; Fri, 18 Nov 2022 08:54:55 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1ovx9C-0001T4-UC for pgsql-hackers@lists.postgresql.org; Fri, 18 Nov 2022 08:54:55 +0000 Received: from mail-ej1-x632.google.com ([2a00:1450:4864:20::632]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1ovx97-0008GO-Be for pgsql-hackers@lists.postgresql.org; Fri, 18 Nov 2022 08:54:53 +0000 Received: by mail-ej1-x632.google.com with SMTP id kt23so11375218ejc.7 for ; Fri, 18 Nov 2022 00:54:49 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=in-reply-to:from:references:cc:to:content-language:subject :user-agent:mime-version:date:message-id:from:to:cc:subject:date :message-id:reply-to; bh=FKtWb2CWEooPg5wntUjFgEeqPaEfbKASQyua5wj49h8=; b=anBZcc4GfpGwSsmGxAPDSeY4CgEJf8MveJw3o7r8v0pBtWPJwT+k2491YlQjqU3Fft xoXNJAzCBECFxdDv9V7bL3ZzS2Sid21y96sTCTWFnSFd/xpbMMf7vqLZn1jGmqXAWYdA cwoFjQwBT6mTVfsJ3yFCrNYOHO3NU5QEIgoy0r0L0NeeoB9PitmxFPJK0R/1yQ2Ds1x7 JWsTzWDXD1WT9B8qPcRKE+b9GCX6QTWjkXb4y1QvWDn0zQqFISOmN1yTfiAKrRhN5SRx X/28eTtiMz6sYVu1HQmeyY5JMedP/lzg/m2UWGdSVE7rI5jqerVF/pZGdQS1wveRyGWd tPJQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=in-reply-to:from:references:cc:to:content-language:subject :user-agent:mime-version:date:message-id:x-gm-message-state:from:to :cc:subject:date:message-id:reply-to; bh=FKtWb2CWEooPg5wntUjFgEeqPaEfbKASQyua5wj49h8=; b=e+DspfpJmw/AaIFCNF18XdbuZjjaOTblOaZK4cY3u8AjtF9/4QMVDJDuQ4/OqQO/2o h2UZnjUSKWBp+PhtPGCr7lgyqyUZxa51haim14nSpZSSg/2jbQLh3CNp7ewkZNL7jtHy agTRRLH9jV8ZH4mCB3O7d5drdDq7igYayvfveXJ8gCH7wk683uU2MpE4dcJLtLtFyF/I BO5x80ChFhQafzBfpSl7WNmWFeubNksJdPKKz7Hmv2hZJH8GV1pyr4WZo/iVPJN7FMRZ Gm8iCsqiUV6IKNO2aSJM17qVHWT50+C5z4ZUh8eQ0oA2qX6swouhtFpZ5U5jOYXJpk0V P4IA== X-Gm-Message-State: ANoB5pkFJIbU1AuiTbFimWjJWm+LxGQxOeOmni7Yq/OFGd0WhTKHSIMc iTi42bYsPvtsHCrlF5A4Udw= X-Google-Smtp-Source: AA0mqf4ThT1e8XU8//0n0ti72l2o9KDJ3ctQlA6C6+dUqv7ZbOYOINO1g5+BAIemivTxSB526HIIYA== X-Received: by 2002:a17:906:4e04:b0:78d:9b4f:44ee with SMTP id z4-20020a1709064e0400b0078d9b4f44eemr5162907eju.679.1668761687758; Fri, 18 Nov 2022 00:54:47 -0800 (PST) Received: from [172.16.209.129] ([147.161.235.7]) by smtp.gmail.com with ESMTPSA id kb12-20020a1709070f8c00b007a1d4944d45sm1425180ejc.142.2022.11.18.00.54.46 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Fri, 18 Nov 2022 00:54:47 -0800 (PST) Content-Type: multipart/mixed; boundary="------------2BCjm9nWNpm0Ps50CUAdm58E" Message-ID: <712310f5-a5c7-b833-4f71-b562c5f2de9b@gmail.com> Date: Fri, 18 Nov 2022 09:54:46 +0100 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.4.2 Subject: Re: Optimize join selectivity estimation by not reading MCV stats for unique join attributes Content-Language: en-US To: Tom Lane , Richard Guo Cc: Tomas Vondra , PostgreSQL Developers References: <522124.1668179795@sss.pgh.pa.us> <16acb727-24e8-6453-1711-aae339802966@gmail.com> <2f30ba9c-b707-55ae-0afd-68eb6a8a7708@enterprisedb.com> <2067066.1668731970@sss.pgh.pa.us> <2106238.1668735410@sss.pgh.pa.us> <2130228.1668748896@sss.pgh.pa.us> From: David Geier In-Reply-To: <2130228.1668748896@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------2BCjm9nWNpm0Ps50CUAdm58E Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit Thanks everyone for the great feedback and suggestions. > >> Yes, it is. Using zero flag would short-cut get_attstatsslot() to just >> return whether the slot type exists without loading it. Do you think we >> need to emphasize this use case in the comments for 'flags'? > Perhaps, it's not really obvious now. Comment added. > I wonder whether we need to also check statistic_proc_security_check() >> when determining if MCVs exists in both sides. > Yeah, I thought about hoisting the statistic_proc_security_check > tests up into get_mcv_stats. I don't think it's a great idea > though. Again, it'd complicate untangling this if we ever > generalize the use of MCVs in this function. Also, I don't > think we should be micro-optimizing the case where the security > check doesn't pass --- if it doesn't, you're going to be hurting > from bad plans a lot more than you are from some wasted cycles > here. Sounds reasonable. Attached is v2 of the patch. This is basically Tom's version plus a comment for the flags of get_attstatslot() as suggested by Richard. I couldn't come up with any reasonable way of writing an automated test for that. Any ideas? -- David Geier (ServiceNow) --------------2BCjm9nWNpm0Ps50CUAdm58E Content-Type: text/x-patch; charset=UTF-8; name="0001-Dont-read-MCV-stats-needlessly-in-join-selectivity-estimation-v2.patch" Content-Disposition: attachment; filename*0="0001-Dont-read-MCV-stats-needlessly-in-join-selectivity-esti"; filename*1="mation-v2.patch" Content-Transfer-Encoding: base64 RnJvbSA2NmIxNjc5MmU4ZTE2ZGFkMzNhZGI1NjE0YTA5MzZjYzQxMDAyZjRjIE1vbiBTZXAg MTcgMDA6MDA6MDAgMjAwMQpGcm9tOiBEYXZpZCBHZWllciA8Z2VpZGF2LnBnQGdtYWlsLmNv bT4KRGF0ZTogRnJpLCAxOCBOb3YgMjAyMiAwOTozNTowOCArMDEwMApTdWJqZWN0OiBbUEFU Q0hdIERvbid0IHJlYWQgTUNWIHN0YXRzIG5lZWRsZXNzbHkgaW4gam9pbiBzZWxlY3Rpdml0 eQogZXN0aW1hdGlvbgoKSm9pbiBzZWxlY3Rpdml0eSBlc3RpbWF0aW9uIG9ubHkgdXNlcyBN Q1Ygc3RhdHMgaWYgdGhleSdyZSBwcmVzZW50Cm9uIGJvdGggam9pbiBhdHRyaWJ1dGVzLiBU aGVyZWZvcmUsIGlmIE1DViBzdGF0cyBhcmUgbm90IGF2YWlsYWJsZQpvbiBvbmUgb2YgdGhl IHR3byBjb2x1bW5zLCBza2lwIHJlYWRpbmcgTUNWIHN0YXRzIGFsdG9nZXRoZXIuCgpGcmVx dWVudGx5IGVuY291bnRlcmVkIHNpdHVhdGlvbnMgaW4gd2hpY2ggTUNWIHN0YXRzIG5vdCBh dmFpbGFibGUKaXMgdW5pcXVlIGNvbHVtbnMgb3IgY29sdW1ucyBmb3Igd2hpY2ggYWxsIHZh bHVlcyBoYXZlIHJvdWdobHkKdGhlIHNhbWUgZnJlcXVlbmN5IGFuZCB0aGVyZWZvcmUgQU5B TFlaRSBkZWNpZGVkIHRvIG5vdCBzdG9yZSB0aGVtLgotLS0KIHNyYy9iYWNrZW5kL3V0aWxz L2FkdC9zZWxmdW5jcy5jICAgIHwgMTUgKysrKysrKysrKysrKy0tCiBzcmMvYmFja2VuZC91 dGlscy9jYWNoZS9sc3lzY2FjaGUuYyB8ICA0ICsrKysKIDIgZmlsZXMgY2hhbmdlZCwgMTcg aW5zZXJ0aW9ucygrKSwgMiBkZWxldGlvbnMoLSkKCmRpZmYgLS1naXQgYS9zcmMvYmFja2Vu ZC91dGlscy9hZHQvc2VsZnVuY3MuYyBiL3NyYy9iYWNrZW5kL3V0aWxzL2FkdC9zZWxmdW5j cy5jCmluZGV4IGQ1OTdiN2U4MWYuLjViYWFiNGUzYWMgMTAwNjQ0Ci0tLSBhL3NyYy9iYWNr ZW5kL3V0aWxzL2FkdC9zZWxmdW5jcy5jCisrKyBiL3NyYy9iYWNrZW5kL3V0aWxzL2FkdC9z ZWxmdW5jcy5jCkBAIC0yMjYzLDYgKzIyNjMsNyBAQCBlcWpvaW5zZWwoUEdfRlVOQ1RJT05f QVJHUykKIAlib29sCQloYXZlX21jdnMyID0gZmFsc2U7CiAJYm9vbAkJam9pbl9pc19yZXZl cnNlZDsKIAlSZWxPcHRJbmZvICppbm5lcl9yZWw7CisJYm9vbAkJZ2V0X21jdl9zdGF0czsK IAogCWdldF9qb2luX3ZhcmlhYmxlcyhyb290LCBhcmdzLCBzamluZm8sCiAJCQkJCSAgICZ2 YXJkYXRhMSwgJnZhcmRhdGEyLCAmam9pbl9pc19yZXZlcnNlZCk7CkBAIC0yMjc1LDExICsy Mjc2LDIxIEBAIGVxam9pbnNlbChQR19GVU5DVElPTl9BUkdTKQogCW1lbXNldCgmc3Nsb3Qx LCAwLCBzaXplb2Yoc3Nsb3QxKSk7CiAJbWVtc2V0KCZzc2xvdDIsIDAsIHNpemVvZihzc2xv dDIpKTsKIAorCS8qCisJICogVGhlcmUgaXMgbm8gdXNlIGluIGZldGNoaW5nIG9uZSBzaWRl J3MgTUNWcyBpZiB3ZSBsYWNrIE1DVnMgZm9yIHRoZQorCSAqIG90aGVyIHNpZGUsIHNvIGRv IGEgcXVpY2sgY2hlY2sgdG8gdmVyaWZ5IHRoYXQgYm90aCBzdGF0cyBleGlzdC4KKwkgKi8K KwlnZXRfbWN2X3N0YXRzID0gKEhlYXBUdXBsZUlzVmFsaWQodmFyZGF0YTEuc3RhdHNUdXBs ZSkgJiYKKwkJCSBIZWFwVHVwbGVJc1ZhbGlkKHZhcmRhdGEyLnN0YXRzVHVwbGUpICYmCisJ CQkgZ2V0X2F0dHN0YXRzc2xvdCgmc3Nsb3QxLCB2YXJkYXRhMS5zdGF0c1R1cGxlLCBTVEFU SVNUSUNfS0lORF9NQ1YsIEludmFsaWRPaWQsIDApICYmCisJCQkgZ2V0X2F0dHN0YXRzc2xv dCgmc3Nsb3QyLCB2YXJkYXRhMi5zdGF0c1R1cGxlLCBTVEFUSVNUSUNfS0lORF9NQ1YsIElu dmFsaWRPaWQsIDApKTsKKyAgICAKKyAgICAKIAlpZiAoSGVhcFR1cGxlSXNWYWxpZCh2YXJk YXRhMS5zdGF0c1R1cGxlKSkKIAl7CiAJCS8qIG5vdGUgd2UgYWxsb3cgdXNlIG9mIG51bGxm cmFjIHJlZ2FyZGxlc3Mgb2Ygc2VjdXJpdHkgY2hlY2sgKi8KIAkJc3RhdHMxID0gKEZvcm1f cGdfc3RhdGlzdGljKSBHRVRTVFJVQ1QodmFyZGF0YTEuc3RhdHNUdXBsZSk7Ci0JCWlmIChz dGF0aXN0aWNfcHJvY19zZWN1cml0eV9jaGVjaygmdmFyZGF0YTEsIG9wZnVuY29pZCkpCisJ CWlmIChnZXRfbWN2X3N0YXRzICYmIHN0YXRpc3RpY19wcm9jX3NlY3VyaXR5X2NoZWNrKCZ2 YXJkYXRhMSwgb3BmdW5jb2lkKSkKIAkJCWhhdmVfbWN2czEgPSBnZXRfYXR0c3RhdHNzbG90 KCZzc2xvdDEsIHZhcmRhdGExLnN0YXRzVHVwbGUsCiAJCQkJCQkJCQkJICBTVEFUSVNUSUNf S0lORF9NQ1YsIEludmFsaWRPaWQsCiAJCQkJCQkJCQkJICBBVFRTVEFUU1NMT1RfVkFMVUVT IHwgQVRUU1RBVFNTTE9UX05VTUJFUlMpOwpAQCAtMjI4OSw3ICsyMzAwLDcgQEAgZXFqb2lu c2VsKFBHX0ZVTkNUSU9OX0FSR1MpCiAJewogCQkvKiBub3RlIHdlIGFsbG93IHVzZSBvZiBu dWxsZnJhYyByZWdhcmRsZXNzIG9mIHNlY3VyaXR5IGNoZWNrICovCiAJCXN0YXRzMiA9IChG b3JtX3BnX3N0YXRpc3RpYykgR0VUU1RSVUNUKHZhcmRhdGEyLnN0YXRzVHVwbGUpOwotCQlp ZiAoc3RhdGlzdGljX3Byb2Nfc2VjdXJpdHlfY2hlY2soJnZhcmRhdGEyLCBvcGZ1bmNvaWQp KQorCQlpZiAoZ2V0X21jdl9zdGF0cyAmJiBzdGF0aXN0aWNfcHJvY19zZWN1cml0eV9jaGVj aygmdmFyZGF0YTIsIG9wZnVuY29pZCkpCiAJCQloYXZlX21jdnMyID0gZ2V0X2F0dHN0YXRz c2xvdCgmc3Nsb3QyLCB2YXJkYXRhMi5zdGF0c1R1cGxlLAogCQkJCQkJCQkJCSAgU1RBVElT VElDX0tJTkRfTUNWLCBJbnZhbGlkT2lkLAogCQkJCQkJCQkJCSAgQVRUU1RBVFNTTE9UX1ZB TFVFUyB8IEFUVFNUQVRTU0xPVF9OVU1CRVJTKTsKZGlmZiAtLWdpdCBhL3NyYy9iYWNrZW5k L3V0aWxzL2NhY2hlL2xzeXNjYWNoZS5jIGIvc3JjL2JhY2tlbmQvdXRpbHMvY2FjaGUvbHN5 c2NhY2hlLmMKaW5kZXggYTE2YTYzZjQ5NS4uMTkxZjY4ZjdiNCAxMDA2NDQKLS0tIGEvc3Jj L2JhY2tlbmQvdXRpbHMvY2FjaGUvbHN5c2NhY2hlLmMKKysrIGIvc3JjL2JhY2tlbmQvdXRp bHMvY2FjaGUvbHN5c2NhY2hlLmMKQEAgLTMxNTcsNiArMzE1NywxMCBAQCBnZXRfYXR0YXZn d2lkdGgoT2lkIHJlbGlkLCBBdHRyTnVtYmVyIGF0dG51bSkKICAqIHJlcWtpbmQ6IFNUQUtJ TkQgY29kZSBmb3IgZGVzaXJlZCBzdGF0aXN0aWNzIHNsb3Qga2luZC4KICAqIHJlcW9wOiBT VEFPUCB2YWx1ZSB3YW50ZWQsIG9yIEludmFsaWRPaWQgaWYgZG9uJ3QgY2FyZS4KICAqIGZs YWdzOiBiaXRtYXNrIG9mIEFUVFNUQVRTU0xPVF9WQUxVRVMgYW5kL29yIEFUVFNUQVRTU0xP VF9OVU1CRVJTLgorICogICAgICAgIFBhc3NpbmcgMCB3aWxsIG9ubHkgY2hlY2sgaWYgdGhl IHJlcXVlc3RlZCBzbG90IHR5cGUgZXhpc3RzIGJ1dCBub3QKKyAqICAgICAgICBleHRyYWN0 IGFueSBjb250ZW50LiBUaGlzIGlzIHVzZWZ1bCBpbiBjYXNlcyB3aGVyZSBNQ1Ygc3RhdHMg YXJlIG9ubHkKKyAqICAgICAgICB1c2VmdWwgaWYgdGhleSBleGlzdHMgb24gYWxsIGludm9s dmVkIGNvbHVtbnMgKGUuZy4gZHVyaW5nIGpvaW4KKyAqICAgICAgICBzZWxlY3Rpdml0eSBj b21wdXRhdGlvbikuCiAgKgogICogSWYgYSBtYXRjaGluZyBzbG90IGlzIGZvdW5kLCB0cnVl IGlzIHJldHVybmVkLCBhbmQgKnNzbG90IGlzIGZpbGxlZCB0aHVzOgogICogc3Rhb3A6IHJl Y2VpdmVzIHRoZSBhY3R1YWwgU1RBT1AgdmFsdWUuCi0tIAoyLjM0LjEKCg== --------------2BCjm9nWNpm0Ps50CUAdm58E--