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 1ow3ll-0001WA-Kc for pgsql-hackers@arkaria.postgresql.org; Fri, 18 Nov 2022 15:59:09 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1ow3lk-0000dE-Bk for pgsql-hackers@arkaria.postgresql.org; Fri, 18 Nov 2022 15:59:08 +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 1ow3lj-0000cP-SJ for pgsql-hackers@lists.postgresql.org; Fri, 18 Nov 2022 15:59:08 +0000 Received: from mail-ed1-x531.google.com ([2a00:1450:4864:20::531]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1ow3lh-0003V6-DO for pgsql-hackers@lists.postgresql.org; Fri, 18 Nov 2022 15:59:06 +0000 Received: by mail-ed1-x531.google.com with SMTP id y24so2721043edi.10 for ; Fri, 18 Nov 2022 07:59:05 -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=6awhVq52MBu5AhjH+fu/VKcR9upWSidRlC7EQE/x+jU=; b=RNA7Td1XLbJvtXu3RFqb15SYAV7kub01llyrR1sPhHXccx7cwbuQ92k28tiyAPcxKk Fl+zkpGuf4PuWsHFN0DMv6iTnkFZ7YdLCh2+6ZQFbIg9janfk/VAtuiVaenyF80bHMGz suuNd0KwZ8pp0lDsrpl5TRO0g9vsdVUgAz/vxP2ey6QyZ4Dm+cv7qZgQt3+RMF275dV6 ZYFi+EViOdzbNyfi2FHlcEGymES14WRaBXMK2+3Y9sYor2MX5dlRH72esOOzkZaaPEQA DH0+ackftihu/kBYyqfPvGHnZ4HDrXh+8cNIjdcwJ5t1y/dcdVeDvIUo3fvmZGEHJ1jm Sd8A== 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=6awhVq52MBu5AhjH+fu/VKcR9upWSidRlC7EQE/x+jU=; b=QI2OqguedV449HvYMl0bj2WX/Ro9k1PksU+XMYlTx8vVZeBqwX+CN8BXc0zUUU+NuB aQ/rzgoSOUr7R/MxsI9uhLQzQLwsbcsPcB/Xha4K9/92UNq2Xgntgy/KM5SlAjMTyDja 5Wb+oPF7qMWTjWG96W8lrcHy/dVV6mxHyrAj7l0kwXW0jftprFRhZPItkkmycEwPkQbS pskbKeVA+Xmb0BjP038n8DZPdVwHMN7HuEoIPjV9YU8/tutW+VQCSUEnUsfZ/49rjd5X 1leRRx8PzIpevQNGmTr+CtlqFrFK1nc2btIGkQV96SHcIBK1uj/tAsEKtsEA/TB6URw+ JrAQ== X-Gm-Message-State: ANoB5pnIWzP8pEQXCXEiHR+rLcUqUddmRNlAPBE9zrJvPg14Ilx7eAEJ t3cgY8gk8oS1lwNUpVuYLMXhCMZQ/Ik= X-Google-Smtp-Source: AA0mqf7gANaoQENNYuAy1CLfWFgp7AVRDEFcLLIaphz+hy0KtbGZQ5J4FGfol4zVcJ4F+fJVZKE/Ng== X-Received: by 2002:a05:6402:1f03:b0:468:7be6:55e7 with SMTP id b3-20020a0564021f0300b004687be655e7mr6665664edb.345.1668787143870; Fri, 18 Nov 2022 07:59:03 -0800 (PST) Received: from [172.16.209.129] ([147.161.235.7]) by smtp.gmail.com with ESMTPSA id jl24-20020a17090775d800b0073cf6ec3276sm1810874ejc.207.2022.11.18.07.59.03 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Fri, 18 Nov 2022 07:59:03 -0800 (PST) Content-Type: multipart/mixed; boundary="------------Qq3QnDE75V7zoYmGD38zGx4I" Message-ID: <2a4917f7-8a4d-0094-7189-3a6dcaebad44@gmail.com> Date: Fri, 18 Nov 2022 16:59:02 +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: Tomas Vondra , Tom Lane , Richard Guo Cc: 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> <712310f5-a5c7-b833-4f71-b562c5f2de9b@gmail.com> <53950f93-8cce-4440-52f1-5879653c6803@enterprisedb.com> From: David Geier In-Reply-To: <53950f93-8cce-4440-52f1-5879653c6803@enterprisedb.com> 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. --------------Qq3QnDE75V7zoYmGD38zGx4I Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit On 11/18/22 14:00, Tomas Vondra wrote: > Seems fine. I wonder if we could/could introduce a new constant for 0, > similar to ATTSTATSSLOT_NUMBERS/ATTSTATSSLOT_VALUES, instead of using a > magic constant. Say, ATTSTATSSLOT_NONE or ATTSTATSSLOT_CHECK. Good idea. I called it ATTSTATSSLOT_EXISTS. New patch attached. > I don't think you can write a test for this, because there is no change > to behavior that can be observed by the user. If one side has no MCV, > the only difference is whether we try to load the other MCV or not. Yeah. I thought along the lines of checking the number of pages read when the pg_stats entry is not in syscache yet. But that seems awfully implementation specific. So no test provided. -- David Geier (ServiceNow) --------------Qq3QnDE75V7zoYmGD38zGx4I Content-Type: text/x-patch; charset=UTF-8; name="0001-Dont-read-MCV-stats-needlessly-in-join-selectivity-estimation-v3.patch" Content-Disposition: attachment; filename*0="0001-Dont-read-MCV-stats-needlessly-in-join-selectivity-esti"; filename*1="mation-v3.patch" Content-Transfer-Encoding: base64 RnJvbSA1YzVjMGZiOWRkOTllNzlkYWFhMDE1OTg0YzlkZGEyMmU0Y2NkYTE3IE1vbiBTZXAg MTcgMDA6MDA6MDAgMjAwMQpGcm9tOiBEYXZpZCBHZWllciA8Z2VpZGF2LnBnQGdtYWlsLmNv bT4KRGF0ZTogRnJpLCAxOCBOb3YgMjAyMiAwOTozNTowOCArMDEwMApTdWJqZWN0OiBbUEFU Q0hdIERvbid0IHJlYWQgTUNWIHN0YXRzIG5lZWRsZXNzbHkgaW4gam9pbiBzZWxlY3Rpdml0 eQogZXN0aW1hdGlvbgoKSm9pbiBzZWxlY3Rpdml0eSBlc3RpbWF0aW9uIG9ubHkgdXNlcyBN Q1Ygc3RhdHMgaWYgdGhleSdyZSBwcmVzZW50Cm9uIGJvdGggam9pbiBhdHRyaWJ1dGVzLiBU aGVyZWZvcmUsIGlmIE1DViBzdGF0cyBhcmUgbm90IGF2YWlsYWJsZQpvbiBvbmUgb2YgdGhl IHR3byBjb2x1bW5zLCBza2lwIHJlYWRpbmcgTUNWIHN0YXRzIGFsdG9nZXRoZXIuCgpGcmVx dWVudGx5IGVuY291bnRlcmVkIHNpdHVhdGlvbnMgaW4gd2hpY2ggTUNWIHN0YXRzIG5vdCBh dmFpbGFibGUKaXMgdW5pcXVlIGNvbHVtbnMgb3IgY29sdW1ucyBmb3Igd2hpY2ggYWxsIHZh bHVlcyBoYXZlIHJvdWdobHkKdGhlIHNhbWUgZnJlcXVlbmN5IGFuZCB0aGVyZWZvcmUgQU5B TFlaRSBkZWNpZGVkIHRvIG5vdCBzdG9yZSB0aGVtLgotLS0KIHNyYy9iYWNrZW5kL3V0aWxz L2FkdC9zZWxmdW5jcy5jICAgIHwgMTcgKysrKysrKysrKysrKysrLS0KIHNyYy9iYWNrZW5k L3V0aWxzL2NhY2hlL2xzeXNjYWNoZS5jIHwgIDQgKysrKwogc3JjL2luY2x1ZGUvdXRpbHMv bHN5c2NhY2hlLmggICAgICAgfCAgMyArKy0KIDMgZmlsZXMgY2hhbmdlZCwgMjEgaW5zZXJ0 aW9ucygrKSwgMyBkZWxldGlvbnMoLSkKCmRpZmYgLS1naXQgYS9zcmMvYmFja2VuZC91dGls cy9hZHQvc2VsZnVuY3MuYyBiL3NyYy9iYWNrZW5kL3V0aWxzL2FkdC9zZWxmdW5jcy5jCmlu ZGV4IGQ1OTdiN2U4MWYuLjg1YTI5ZWFlYjkgMTAwNjQ0Ci0tLSBhL3NyYy9iYWNrZW5kL3V0 aWxzL2FkdC9zZWxmdW5jcy5jCisrKyBiL3NyYy9iYWNrZW5kL3V0aWxzL2FkdC9zZWxmdW5j cy5jCkBAIC0yMjYzLDYgKzIyNjMsNyBAQCBlcWpvaW5zZWwoUEdfRlVOQ1RJT05fQVJHUykK IAlib29sCQloYXZlX21jdnMyID0gZmFsc2U7CiAJYm9vbAkJam9pbl9pc19yZXZlcnNlZDsK IAlSZWxPcHRJbmZvICppbm5lcl9yZWw7CisJYm9vbAkJZ2V0X21jdl9zdGF0czsKIAogCWdl dF9qb2luX3ZhcmlhYmxlcyhyb290LCBhcmdzLCBzamluZm8sCiAJCQkJCSAgICZ2YXJkYXRh MSwgJnZhcmRhdGEyLCAmam9pbl9pc19yZXZlcnNlZCk7CkBAIC0yMjc1LDExICsyMjc2LDIz IEBAIGVxam9pbnNlbChQR19GVU5DVElPTl9BUkdTKQogCW1lbXNldCgmc3Nsb3QxLCAwLCBz aXplb2Yoc3Nsb3QxKSk7CiAJbWVtc2V0KCZzc2xvdDIsIDAsIHNpemVvZihzc2xvdDIpKTsK IAorCS8qCisJICogVGhlcmUgaXMgbm8gdXNlIGluIGZldGNoaW5nIG9uZSBzaWRlJ3MgTUNW cyBpZiB3ZSBsYWNrIE1DVnMgZm9yIHRoZQorCSAqIG90aGVyIHNpZGUsIHNvIGRvIGEgcXVp Y2sgY2hlY2sgdG8gdmVyaWZ5IHRoYXQgYm90aCBzdGF0cyBleGlzdC4KKwkgKi8KKwlnZXRf bWN2X3N0YXRzID0gKEhlYXBUdXBsZUlzVmFsaWQodmFyZGF0YTEuc3RhdHNUdXBsZSkgJiYK KwkJCSBIZWFwVHVwbGVJc1ZhbGlkKHZhcmRhdGEyLnN0YXRzVHVwbGUpICYmCisJCQkgZ2V0 X2F0dHN0YXRzc2xvdCgmc3Nsb3QxLCB2YXJkYXRhMS5zdGF0c1R1cGxlLAorCQkJCQkgIFNU QVRJU1RJQ19LSU5EX01DViwgSW52YWxpZE9pZCwgQVRUU1RBVFNTTE9UX0VYSVNUUykgJiYK KwkJCSBnZXRfYXR0c3RhdHNzbG90KCZzc2xvdDIsIHZhcmRhdGEyLnN0YXRzVHVwbGUsCisJ CQkJCSAgU1RBVElTVElDX0tJTkRfTUNWLCBJbnZhbGlkT2lkLCBBVFRTVEFUU1NMT1RfRVhJ U1RTKSk7CisgICAgCisgICAgCiAJaWYgKEhlYXBUdXBsZUlzVmFsaWQodmFyZGF0YTEuc3Rh dHNUdXBsZSkpCiAJewogCQkvKiBub3RlIHdlIGFsbG93IHVzZSBvZiBudWxsZnJhYyByZWdh cmRsZXNzIG9mIHNlY3VyaXR5IGNoZWNrICovCiAJCXN0YXRzMSA9IChGb3JtX3BnX3N0YXRp c3RpYykgR0VUU1RSVUNUKHZhcmRhdGExLnN0YXRzVHVwbGUpOwotCQlpZiAoc3RhdGlzdGlj X3Byb2Nfc2VjdXJpdHlfY2hlY2soJnZhcmRhdGExLCBvcGZ1bmNvaWQpKQorCQlpZiAoZ2V0 X21jdl9zdGF0cyAmJiBzdGF0aXN0aWNfcHJvY19zZWN1cml0eV9jaGVjaygmdmFyZGF0YTEs IG9wZnVuY29pZCkpCiAJCQloYXZlX21jdnMxID0gZ2V0X2F0dHN0YXRzc2xvdCgmc3Nsb3Qx LCB2YXJkYXRhMS5zdGF0c1R1cGxlLAogCQkJCQkJCQkJCSAgU1RBVElTVElDX0tJTkRfTUNW LCBJbnZhbGlkT2lkLAogCQkJCQkJCQkJCSAgQVRUU1RBVFNTTE9UX1ZBTFVFUyB8IEFUVFNU QVRTU0xPVF9OVU1CRVJTKTsKQEAgLTIyODksNyArMjMwMiw3IEBAIGVxam9pbnNlbChQR19G VU5DVElPTl9BUkdTKQogCXsKIAkJLyogbm90ZSB3ZSBhbGxvdyB1c2Ugb2YgbnVsbGZyYWMg cmVnYXJkbGVzcyBvZiBzZWN1cml0eSBjaGVjayAqLwogCQlzdGF0czIgPSAoRm9ybV9wZ19z dGF0aXN0aWMpIEdFVFNUUlVDVCh2YXJkYXRhMi5zdGF0c1R1cGxlKTsKLQkJaWYgKHN0YXRp c3RpY19wcm9jX3NlY3VyaXR5X2NoZWNrKCZ2YXJkYXRhMiwgb3BmdW5jb2lkKSkKKwkJaWYg KGdldF9tY3Zfc3RhdHMgJiYgc3RhdGlzdGljX3Byb2Nfc2VjdXJpdHlfY2hlY2soJnZhcmRh dGEyLCBvcGZ1bmNvaWQpKQogCQkJaGF2ZV9tY3ZzMiA9IGdldF9hdHRzdGF0c3Nsb3QoJnNz bG90MiwgdmFyZGF0YTIuc3RhdHNUdXBsZSwKIAkJCQkJCQkJCQkgIFNUQVRJU1RJQ19LSU5E X01DViwgSW52YWxpZE9pZCwKIAkJCQkJCQkJCQkgIEFUVFNUQVRTU0xPVF9WQUxVRVMgfCBB VFRTVEFUU1NMT1RfTlVNQkVSUyk7CmRpZmYgLS1naXQgYS9zcmMvYmFja2VuZC91dGlscy9j YWNoZS9sc3lzY2FjaGUuYyBiL3NyYy9iYWNrZW5kL3V0aWxzL2NhY2hlL2xzeXNjYWNoZS5j CmluZGV4IGExNmE2M2Y0OTUuLjE5MWY2OGY3YjQgMTAwNjQ0Ci0tLSBhL3NyYy9iYWNrZW5k L3V0aWxzL2NhY2hlL2xzeXNjYWNoZS5jCisrKyBiL3NyYy9iYWNrZW5kL3V0aWxzL2NhY2hl L2xzeXNjYWNoZS5jCkBAIC0zMTU3LDYgKzMxNTcsMTAgQEAgZ2V0X2F0dGF2Z3dpZHRoKE9p ZCByZWxpZCwgQXR0ck51bWJlciBhdHRudW0pCiAgKiByZXFraW5kOiBTVEFLSU5EIGNvZGUg Zm9yIGRlc2lyZWQgc3RhdGlzdGljcyBzbG90IGtpbmQuCiAgKiByZXFvcDogU1RBT1AgdmFs dWUgd2FudGVkLCBvciBJbnZhbGlkT2lkIGlmIGRvbid0IGNhcmUuCiAgKiBmbGFnczogYml0 bWFzayBvZiBBVFRTVEFUU1NMT1RfVkFMVUVTIGFuZC9vciBBVFRTVEFUU1NMT1RfTlVNQkVS Uy4KKyAqICAgICAgICBQYXNzaW5nIDAgd2lsbCBvbmx5IGNoZWNrIGlmIHRoZSByZXF1ZXN0 ZWQgc2xvdCB0eXBlIGV4aXN0cyBidXQgbm90CisgKiAgICAgICAgZXh0cmFjdCBhbnkgY29u dGVudC4gVGhpcyBpcyB1c2VmdWwgaW4gY2FzZXMgd2hlcmUgTUNWIHN0YXRzIGFyZSBvbmx5 CisgKiAgICAgICAgdXNlZnVsIGlmIHRoZXkgZXhpc3RzIG9uIGFsbCBpbnZvbHZlZCBjb2x1 bW5zIChlLmcuIGR1cmluZyBqb2luCisgKiAgICAgICAgc2VsZWN0aXZpdHkgY29tcHV0YXRp b24pLgogICoKICAqIElmIGEgbWF0Y2hpbmcgc2xvdCBpcyBmb3VuZCwgdHJ1ZSBpcyByZXR1 cm5lZCwgYW5kICpzc2xvdCBpcyBmaWxsZWQgdGh1czoKICAqIHN0YW9wOiByZWNlaXZlcyB0 aGUgYWN0dWFsIFNUQU9QIHZhbHVlLgpkaWZmIC0tZ2l0IGEvc3JjL2luY2x1ZGUvdXRpbHMv bHN5c2NhY2hlLmggYi9zcmMvaW5jbHVkZS91dGlscy9sc3lzY2FjaGUuaAppbmRleCA1MGYw Mjg4MzA1Li5kOWEzOTE2OTk3IDEwMDY0NAotLS0gYS9zcmMvaW5jbHVkZS91dGlscy9sc3lz Y2FjaGUuaAorKysgYi9zcmMvaW5jbHVkZS91dGlscy9sc3lzY2FjaGUuaApAQCAtMzksNyAr MzksOCBAQCB0eXBlZGVmIGVudW0gSU9GdW5jU2VsZWN0b3IKIH0gSU9GdW5jU2VsZWN0b3I7 CiAKIC8qIEZsYWcgYml0cyBmb3IgZ2V0X2F0dHN0YXRzc2xvdCAqLwotI2RlZmluZSBBVFRT VEFUU1NMT1RfVkFMVUVTCQkweDAxCisjZGVmaW5lIEFUVFNUQVRTU0xPVF9FWElTVFMJMHgw MAorI2RlZmluZSBBVFRTVEFUU1NMT1RfVkFMVUVTCTB4MDEKICNkZWZpbmUgQVRUU1RBVFNT TE9UX05VTUJFUlMJMHgwMgogCiAvKiBSZXN1bHQgc3RydWN0IGZvciBnZXRfYXR0c3RhdHNz bG90ICovCi0tIAoyLjM0LjEKCg== --------------Qq3QnDE75V7zoYmGD38zGx4I--