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 1otSit-0003zt-4N for pgsql-hackers@arkaria.postgresql.org; Fri, 11 Nov 2022 12:01:27 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1otSiq-0002Dd-9R for pgsql-hackers@arkaria.postgresql.org; Fri, 11 Nov 2022 12:01:24 +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 1otSip-0002DU-UZ for pgsql-hackers@lists.postgresql.org; Fri, 11 Nov 2022 12:01:23 +0000 Received: from mail-ed1-x52e.google.com ([2a00:1450:4864:20::52e]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1otSil-00058f-EI for pgsql-hackers@lists.postgresql.org; Fri, 11 Nov 2022 12:01:22 +0000 Received: by mail-ed1-x52e.google.com with SMTP id i21so7305600edj.10 for ; Fri, 11 Nov 2022 04:01:19 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=to:subject:from:content-language:user-agent:mime-version:date :message-id:from:to:cc:subject:date:message-id:reply-to; bh=i7XJtRJdHehND99Sj1gMkCDmrcVCpZqbVM/cd+3AJRs=; b=WkS3qKuBX2lNema11Y7oPpSW1l9bAZ8AGTN+lZnC+8++KKBJt0exg8FnBSVVRa5yZb HskGHeBwRq7ul9slD9+JIA0M9kkiNeF0q9jqm3HvoKPF/ofOkQBdI5rJyuFworlKT8gk dldsWxIQ6kwRDBQOb1U/Qo3UmeJqTOTXnayoh+5rjXNQXHJgCvdxrb4ekJS26H4cys5a 6j+NvUFW/g+ubOMP1BOqp2XuIBqWfGwUiArwc3lXGGnjG8Ul//dHxBu0MNB/5rzBqLA4 3jdkV9UHfklg0doAhgWYzYbujnMuDO6rHFdzL7ZZmxp8yN7oYsB/sTCyU2VcF/EJABEl 9tIw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=to:subject:from:content-language:user-agent:mime-version:date :message-id:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=i7XJtRJdHehND99Sj1gMkCDmrcVCpZqbVM/cd+3AJRs=; b=oqKQzwBl8j8+kqwvtL2qA++rtPJdhFJJCfeB245Yw27GHxqEHkquyXTqRsMHaldfU4 e8Pxfs+dVGHWkdFKx/NdhGAcR1w7c571VzoUQkqlbwvfOeVFvTtCN88A42kq9M6x4O+6 ey7U49q/EtSoLxEM3fWMeHBwJkUlEq2UqVJMvz+lkqqBrjA71Ybx/ZHkew/+CSwVZNGO 64Dmy/Lv/5X68oROa6C2P0D+tVGhHeUqLbd2pFe2+yKGpSdC6hlHiF9hbO9RlEjjHRFG 8/6c+4tvwJ4YWC1CdTA2CV0SFhibiGDHn/2aXuxsqe1g+X1T1Kr4oJ9QpKagbQBkVn2Z DfBQ== X-Gm-Message-State: ANoB5pm1lDGTiK1zCEc7hJqsVQztW9hbryQmuYHW4Z9dcVlqQRbfXfIR aQkGH1mgCNyUFxUJQyxf0n6f+9UE6+M= X-Google-Smtp-Source: AA0mqf599yKjQHFyG+VGJTjYm/Mf90bd+zTqxiWmKK4GPsRkzNp0FeK0bpEThWW7Ucyk0WMBiPLB6w== X-Received: by 2002:a50:fc1a:0:b0:461:8972:e3b8 with SMTP id i26-20020a50fc1a000000b004618972e3b8mr1150438edr.110.1668168076870; Fri, 11 Nov 2022 04:01:16 -0800 (PST) Received: from [172.16.209.129] ([147.161.235.21]) by smtp.gmail.com with ESMTPSA id ft29-20020a170907801d00b0077016f4c6d4sm817282ejc.55.2022.11.11.04.01.16 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Fri, 11 Nov 2022 04:01:16 -0800 (PST) Content-Type: multipart/mixed; boundary="------------a4bJrBgF9iw60OM0I1BbDPVa" Message-ID: Date: Fri, 11 Nov 2022 13:01:15 +0100 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.2.2 Content-Language: en-US From: David Geier Subject: Optimize join selectivity estimation by not reading MCV stats for unique join attributes To: PostgreSQL Developers 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. --------------a4bJrBgF9iw60OM0I1BbDPVa Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit Hi hackers, eqjoinsel() can be optimized by not reading MCV stats if at least one of the two join attributes is unique. As primary keys are implicitly unique this situation can occur frequently. For unique columns no MCV stats are stored and eqjoinsel_inner() and eqjoinsel_semi(), called from eqjoinsel(), only consider MCV stats in the join selectivity computation if they're present on both columns. Attached is a small patch that implements the skipping. With this change we saw some queries improve planning time by more than 2x, especially with larger values for default_statistics_target. That's because get_attstatsslot() deconstructs the array holding the MCV. The size of that array depends on default_statistics_target. Thanks for your consideration! -- David Geier (ServiceNow) --------------a4bJrBgF9iw60OM0I1BbDPVa Content-Type: text/x-patch; charset=UTF-8; name="0001-Skip-reading-MCV-stats-for-unique-join-attributes.patch" Content-Disposition: attachment; filename*0="0001-Skip-reading-MCV-stats-for-unique-join-attributes.patch" Content-Transfer-Encoding: base64 RnJvbSA3YTgxNzZiOWViOWRkOTk4MjY2MmQ4M2I4Njk5NmM1NDAyMzc4Njc0IE1vbiBTZXAg MTcgMDA6MDA6MDAgMjAwMQpGcm9tOiBEYXZpZCBHZWllciA8Z2VpZGF2LnBnQGdtYWlsLmNv bT4KRGF0ZTogRnJpLCAxMSBOb3YgMjAyMiAxMjozMzoxMSArMDEwMApTdWJqZWN0OiBbUEFU Q0hdIFNraXAgcmVhZGluZyBNQ1Ygc3RhdHMgZm9yIHVuaXF1ZSBqb2luIGF0dHJpYnV0ZXMK Ck1DViBzdGF0cyBhcmUgbm90IHN0b3JlZCBmb3IgdW5pcXVlIGNvbHVtbnMuIElmIG9uZSBv ZiB0aGUgdHdvIGpvaW4KYXR0cmlidXRlcyBpcyB1bmlxdWUsIHJlYWRpbmcgdGhlIE1DViBz dGF0cyBjYW4gYmUgc2tpcHBlZCBmb3IgYm90aApjb2x1bW5zIGJlY2F1c2UgdGhleSBhcmUg b25seSBldmVyIHVzZWQgZHVyaW5nIGpvaW4gc2VsZWN0aXZpdHkKZXN0aW1hdGlvbiBpZiB0 aGV5J3JlIHByZXNlbnQgb24gYm90aCBhdHRyaWJ1dGVzLgotLS0KIHNyYy9iYWNrZW5kL3V0 aWxzL2FkdC9zZWxmdW5jcy5jIHwgNiArKysrLS0KIDEgZmlsZSBjaGFuZ2VkLCA0IGluc2Vy dGlvbnMoKyksIDIgZGVsZXRpb25zKC0pCgpkaWZmIC0tZ2l0IGEvc3JjL2JhY2tlbmQvdXRp bHMvYWR0L3NlbGZ1bmNzLmMgYi9zcmMvYmFja2VuZC91dGlscy9hZHQvc2VsZnVuY3MuYwpp bmRleCBkNTk3YjdlODFmLi4zYzkwMDdjOGQxIDEwMDY0NAotLS0gYS9zcmMvYmFja2VuZC91 dGlscy9hZHQvc2VsZnVuY3MuYworKysgYi9zcmMvYmFja2VuZC91dGlscy9hZHQvc2VsZnVu Y3MuYwpAQCAtMjI2Myw2ICsyMjYzLDcgQEAgZXFqb2luc2VsKFBHX0ZVTkNUSU9OX0FSR1Mp CiAJYm9vbAkJaGF2ZV9tY3ZzMiA9IGZhbHNlOwogCWJvb2wJCWpvaW5faXNfcmV2ZXJzZWQ7 CiAJUmVsT3B0SW5mbyAqaW5uZXJfcmVsOworCWJvb2wJCWdldF9tY3Zfc3RhdHM7CiAKIAln ZXRfam9pbl92YXJpYWJsZXMocm9vdCwgYXJncywgc2ppbmZvLAogCQkJCQkgICAmdmFyZGF0 YTEsICZ2YXJkYXRhMiwgJmpvaW5faXNfcmV2ZXJzZWQpOwpAQCAtMjI3MCw2ICsyMjcxLDcg QEAgZXFqb2luc2VsKFBHX0ZVTkNUSU9OX0FSR1MpCiAJbmQxID0gZ2V0X3ZhcmlhYmxlX251 bWRpc3RpbmN0KCZ2YXJkYXRhMSwgJmlzZGVmYXVsdDEpOwogCW5kMiA9IGdldF92YXJpYWJs ZV9udW1kaXN0aW5jdCgmdmFyZGF0YTIsICZpc2RlZmF1bHQyKTsKIAorCWdldF9tY3Zfc3Rh dHMgPSAhdmFyZGF0YTEuaXN1bmlxdWUgJiYgIXZhcmRhdGEyLmlzdW5pcXVlOwogCW9wZnVu Y29pZCA9IGdldF9vcGNvZGUob3BlcmF0b3IpOwogCiAJbWVtc2V0KCZzc2xvdDEsIDAsIHNp emVvZihzc2xvdDEpKTsKQEAgLTIyNzksNyArMjI4MSw3IEBAIGVxam9pbnNlbChQR19GVU5D VElPTl9BUkdTKQogCXsKIAkJLyogbm90ZSB3ZSBhbGxvdyB1c2Ugb2YgbnVsbGZyYWMgcmVn YXJkbGVzcyBvZiBzZWN1cml0eSBjaGVjayAqLwogCQlzdGF0czEgPSAoRm9ybV9wZ19zdGF0 aXN0aWMpIEdFVFNUUlVDVCh2YXJkYXRhMS5zdGF0c1R1cGxlKTsKLQkJaWYgKHN0YXRpc3Rp Y19wcm9jX3NlY3VyaXR5X2NoZWNrKCZ2YXJkYXRhMSwgb3BmdW5jb2lkKSkKKwkJaWYgKGdl dF9tY3Zfc3RhdHMgJiYgc3RhdGlzdGljX3Byb2Nfc2VjdXJpdHlfY2hlY2soJnZhcmRhdGEx LCBvcGZ1bmNvaWQpKQogCQkJaGF2ZV9tY3ZzMSA9IGdldF9hdHRzdGF0c3Nsb3QoJnNzbG90 MSwgdmFyZGF0YTEuc3RhdHNUdXBsZSwKIAkJCQkJCQkJCQkgIFNUQVRJU1RJQ19LSU5EX01D ViwgSW52YWxpZE9pZCwKIAkJCQkJCQkJCQkgIEFUVFNUQVRTU0xPVF9WQUxVRVMgfCBBVFRT VEFUU1NMT1RfTlVNQkVSUyk7CkBAIC0yMjg5LDcgKzIyOTEsNyBAQCBlcWpvaW5zZWwoUEdf RlVOQ1RJT05fQVJHUykKIAl7CiAJCS8qIG5vdGUgd2UgYWxsb3cgdXNlIG9mIG51bGxmcmFj IHJlZ2FyZGxlc3Mgb2Ygc2VjdXJpdHkgY2hlY2sgKi8KIAkJc3RhdHMyID0gKEZvcm1fcGdf c3RhdGlzdGljKSBHRVRTVFJVQ1QodmFyZGF0YTIuc3RhdHNUdXBsZSk7Ci0JCWlmIChzdGF0 aXN0aWNfcHJvY19zZWN1cml0eV9jaGVjaygmdmFyZGF0YTIsIG9wZnVuY29pZCkpCisJCWlm IChnZXRfbWN2X3N0YXRzICYmIHN0YXRpc3RpY19wcm9jX3NlY3VyaXR5X2NoZWNrKCZ2YXJk YXRhMiwgb3BmdW5jb2lkKSkKIAkJCWhhdmVfbWN2czIgPSBnZXRfYXR0c3RhdHNzbG90KCZz c2xvdDIsIHZhcmRhdGEyLnN0YXRzVHVwbGUsCiAJCQkJCQkJCQkJICBTVEFUSVNUSUNfS0lO RF9NQ1YsIEludmFsaWRPaWQsCiAJCQkJCQkJCQkJICBBVFRTVEFUU1NMT1RfVkFMVUVTIHwg QVRUU1RBVFNTTE9UX05VTUJFUlMpOwotLSAKMi4zNC4xCgo= --------------a4bJrBgF9iw60OM0I1BbDPVa--