Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tvZLr-005vwd-6Y for pgsql-hackers@arkaria.postgresql.org; Fri, 21 Mar 2025 10:11:44 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tvZLp-003TW7-Tz for pgsql-hackers@arkaria.postgresql.org; Fri, 21 Mar 2025 10:11:41 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tvZIc-003Mi2-QY for pgsql-hackers@lists.postgresql.org; Fri, 21 Mar 2025 10:08:22 +0000 Received: from forward502b.mail.yandex.net ([2a02:6b8:c02:900:1:45:d181:d502]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1tvZIa-000Ik6-0J for pgsql-hackers@lists.postgresql.org; Fri, 21 Mar 2025 10:08:22 +0000 Received: from mail-nwsmtp-smtp-production-main-89.sas.yp-c.yandex.net (mail-nwsmtp-smtp-production-main-89.sas.yp-c.yandex.net [IPv6:2a02:6b8:c23:3b11:0:640:80c7:0]) by forward502b.mail.yandex.net (Yandex) with ESMTPS id 1E8E261043; Fri, 21 Mar 2025 13:08:17 +0300 (MSK) Received: by mail-nwsmtp-smtp-production-main-89.sas.yp-c.yandex.net (smtp/Yandex) with ESMTPSA id E8ISN1FLhqM0-3Cqetnxz; Fri, 21 Mar 2025 13:08:16 +0300 X-Yandex-Fwd: 1 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=tantorlabs.com; s=mail; t=1742551696; bh=Kcfx3Yoe+zGR7daPX3/U2v2UVJhcBFpF3NnlLtXCN78=; h=In-Reply-To:Cc:Date:References:To:Subject:Message-ID:From; b=ZrfOdSNF1eF9rhYvVONQvXvr6YJE2xCjLTtxJNp0XKltcXU67wOi80YmVF6BCixAb ql9YNrM32vvLrvI7cmD9UmNxb+yhXBaBX4My/fdOhRte+V/V0xYX9nOj/hYA+eEnJg IWV5xi5SMiGXq/3Qo3UsS7Yu3sSbHX/tM4Hggwlg= Authentication-Results: mail-nwsmtp-smtp-production-main-89.sas.yp-c.yandex.net; dkim=pass header.i=@tantorlabs.com Content-Type: multipart/mixed; boundary="------------sVUBP7tGzzXNo06YF4da0tVm" Message-ID: <0f32a222-c255-4a3b-bb98-255adb6d91e0@tantorlabs.com> Date: Fri, 21 Mar 2025 13:08:14 +0300 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: Add estimated hit ratio to Memoize in EXPLAIN to explain cost adjustment To: Andrei Lepikhov , David Rowley Cc: Lukas Fittl , Daniel Gustafsson , PostgreSQL Hackers References: <51A15CD7-E31B-483D-B911-D0EB9F5FF952@yesql.se> <44229379-3901-4cb0-8812-b354ef70d5e2@gmail.com> <8e12aa55-39eb-4e03-a8f7-077fe02309c3@gmail.com> Content-Language: en-US From: Ilia Evdokimov In-Reply-To: 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. --------------sVUBP7tGzzXNo06YF4da0tVm Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit After considering the opinions expressed in this discussion, I tend to agree more with David. If we add info about estimated unique keys and estimated capacity, then any additional information - such as evict_ratio and hit_ratio - can also be calculated, as EXPLAIN ANALYZE provides all the necessary details to compute these values. For now, I’m attaching a rebased v4 patch, which includes the fix for ExplainPropertyText. -- Best regards, Ilia Evdokimov, Tantor Labs LLC. --------------sVUBP7tGzzXNo06YF4da0tVm Content-Type: text/x-patch; charset=UTF-8; name="v4-0001-Show-ndistinct-and-est_entries-in-EXPLAIN-for-Memoize.patch" Content-Disposition: attachment; filename*0="v4-0001-Show-ndistinct-and-est_entries-in-EXPLAIN-for-Memoiz"; filename*1="e.patch" Content-Transfer-Encoding: base64 RnJvbSBiNjBlNTA4NTgzYjFjYjdmMzA4MTRjZWY2ZjM5YzRiNTcwNjkzMzUwIE1vbiBTZXAg MTcgMDA6MDA6MDAgMjAwMQpGcm9tOiBJbGlhIEV2ZG9raW1vdiA8aWx5YS5ldmRva2ltb3ZA dGFudG9ybGFicy5ydT4KRGF0ZTogRnJpLCAyMSBNYXIgMjAyNSAxMTo1MjoyOSArMDMwMApT dWJqZWN0OiBbUEFUQ0ggdjRdIFNob3cgbmRpc3RpbmN0IGFuZCBlc3RfZW50cmllcyBpbiBF WFBMQUlOIGZvciBNZW1vaXplCgotLS0KIHNyYy9iYWNrZW5kL2NvbW1hbmRzL2V4cGxhaW4u YyAgICAgICAgICB8IDE2ICsrKysrKysrKysrKysrKysKIHNyYy9iYWNrZW5kL29wdGltaXpl ci9wYXRoL2Nvc3RzaXplLmMgICB8ICAzICsrKwogc3JjL2JhY2tlbmQvb3B0aW1pemVyL3Bs YW4vY3JlYXRlcGxhbi5jIHwgMTAgKysrKysrKy0tLQogc3JjL2JhY2tlbmQvb3B0aW1pemVy L3V0aWwvcGF0aG5vZGUuYyAgIHwgIDMgKysrCiBzcmMvaW5jbHVkZS9ub2Rlcy9wYXRobm9k ZXMuaCAgICAgICAgICAgfCAgMiArKwogc3JjL2luY2x1ZGUvbm9kZXMvcGxhbm5vZGVzLmgg ICAgICAgICAgIHwgIDYgKysrKysrCiA2IGZpbGVzIGNoYW5nZWQsIDM3IGluc2VydGlvbnMo KyksIDMgZGVsZXRpb25zKC0pCgpkaWZmIC0tZ2l0IGEvc3JjL2JhY2tlbmQvY29tbWFuZHMv ZXhwbGFpbi5jIGIvc3JjL2JhY2tlbmQvY29tbWFuZHMvZXhwbGFpbi5jCmluZGV4IDM5MWIz NGEyYWYyLi5jYjdlMjk1Y2E4YSAxMDA2NDQKLS0tIGEvc3JjL2JhY2tlbmQvY29tbWFuZHMv ZXhwbGFpbi5jCisrKyBiL3NyYy9iYWNrZW5kL2NvbW1hbmRzL2V4cGxhaW4uYwpAQCAtMzYy OCw2ICszNjI4LDIyIEBAIHNob3dfbWVtb2l6ZV9pbmZvKE1lbW9pemVTdGF0ZSAqbXN0YXRl LCBMaXN0ICphbmNlc3RvcnMsIEV4cGxhaW5TdGF0ZSAqZXMpCiAJRXhwbGFpblByb3BlcnR5 VGV4dCgiQ2FjaGUgS2V5Iiwga2V5c3RyLmRhdGEsIGVzKTsKIAlFeHBsYWluUHJvcGVydHlU ZXh0KCJDYWNoZSBNb2RlIiwgbXN0YXRlLT5iaW5hcnlfbW9kZSA/ICJiaW5hcnkiIDogImxv Z2ljYWwiLCBlcyk7CiAKKwlpZiAoZXMtPmNvc3RzKQorCXsKKwkJaWYgKGVzLT5mb3JtYXQg PT0gRVhQTEFJTl9GT1JNQVRfVEVYVCkKKwkJeworCQkJRXhwbGFpbkluZGVudFRleHQoZXMp OworCQkJYXBwZW5kU3RyaW5nSW5mbyhlcy0+c3RyLCAiRXN0aW1hdGVkIENhcGFjaXR5OiAl dSAgRXN0aW1hdGVkIERpc3RpbmN0IExvb2t1cCBLZXlzOiAlMC4wZlxuIiwKKwkJCQkJCQko KE1lbW9pemUgKikgcGxhbiktPmVzdF9lbnRyaWVzLAorCQkJCQkJCSgoTWVtb2l6ZSAqKSBw bGFuKS0+ZXN0X3VuaXF1ZV9rZXlzKTsKKwkJfQorCQllbHNlCisJCXsKKwkJCUV4cGxhaW5Q cm9wZXJ0eVVJbnRlZ2VyKCJFc3RpbWF0ZWQgQ2FwYWNpdHkiLCAiIiwgKChNZW1vaXplICop IHBsYW4pLT5lc3RfZW50cmllcywgZXMpOworCQkJRXhwbGFpblByb3BlcnR5RmxvYXQoIkVz dGltYXRlZCBEaXN0aW5jdCBMb29rdXAgS2V5cyIsICIiLCAoKE1lbW9pemUgKikgcGxhbikt PmVzdF91bmlxdWVfa2V5cywgMCwgZXMpOworCQl9CisJfQorCiAJcGZyZWUoa2V5c3RyLmRh dGEpOwogCiAJaWYgKCFlcy0+YW5hbHl6ZSkKZGlmZiAtLWdpdCBhL3NyYy9iYWNrZW5kL29w dGltaXplci9wYXRoL2Nvc3RzaXplLmMgYi9zcmMvYmFja2VuZC9vcHRpbWl6ZXIvcGF0aC9j b3N0c2l6ZS5jCmluZGV4IGY2Zjc3YjhmZTE5Li5lM2FiZjljY2MyNiAxMDA2NDQKLS0tIGEv c3JjL2JhY2tlbmQvb3B0aW1pemVyL3BhdGgvY29zdHNpemUuYworKysgYi9zcmMvYmFja2Vu ZC9vcHRpbWl6ZXIvcGF0aC9jb3N0c2l6ZS5jCkBAIC0yNjA0LDYgKzI2MDQsOSBAQCBjb3N0 X21lbW9pemVfcmVzY2FuKFBsYW5uZXJJbmZvICpyb290LCBNZW1vaXplUGF0aCAqbXBhdGgs CiAJbXBhdGgtPmVzdF9lbnRyaWVzID0gTWluKE1pbihuZGlzdGluY3QsIGVzdF9jYWNoZV9l bnRyaWVzKSwKIAkJCQkJCQkgUEdfVUlOVDMyX01BWCk7CiAKKwkvKiBSZW1lbWJlciBuZGlz dGluY3QgZm9yIGEgcG90ZW50aWFsIEVYUExBSU4gbGF0ZXIgKi8KKwltcGF0aC0+ZXN0X3Vu aXF1ZV9rZXlzID0gbmRpc3RpbmN0OworCiAJLyoKIAkgKiBXaGVuIHRoZSBudW1iZXIgb2Yg ZGlzdGluY3QgcGFyYW1ldGVyIHZhbHVlcyBpcyBhYm92ZSB0aGUgYW1vdW50IHdlIGNhbgog CSAqIHN0b3JlIGluIHRoZSBjYWNoZSwgdGhlbiB3ZSdsbCBoYXZlIHRvIGV2aWN0IHNvbWUg ZW50cmllcyBmcm9tIHRoZQpkaWZmIC0tZ2l0IGEvc3JjL2JhY2tlbmQvb3B0aW1pemVyL3Bs YW4vY3JlYXRlcGxhbi5jIGIvc3JjL2JhY2tlbmQvb3B0aW1pemVyL3BsYW4vY3JlYXRlcGxh bi5jCmluZGV4IDc1ZTJiMGI5MDM2Li41OTZiODJjNWRjOSAxMDA2NDQKLS0tIGEvc3JjL2Jh Y2tlbmQvb3B0aW1pemVyL3BsYW4vY3JlYXRlcGxhbi5jCisrKyBiL3NyYy9iYWNrZW5kL29w dGltaXplci9wbGFuL2NyZWF0ZXBsYW4uYwpAQCAtMjg0LDcgKzI4NCw4IEBAIHN0YXRpYyBN YXRlcmlhbCAqbWFrZV9tYXRlcmlhbChQbGFuICpsZWZ0dHJlZSk7CiBzdGF0aWMgTWVtb2l6 ZSAqbWFrZV9tZW1vaXplKFBsYW4gKmxlZnR0cmVlLCBPaWQgKmhhc2hvcGVyYXRvcnMsCiAJ CQkJCQkJIE9pZCAqY29sbGF0aW9ucywgTGlzdCAqcGFyYW1fZXhwcnMsCiAJCQkJCQkJIGJv b2wgc2luZ2xlcm93LCBib29sIGJpbmFyeV9tb2RlLAotCQkJCQkJCSB1aW50MzIgZXN0X2Vu dHJpZXMsIEJpdG1hcHNldCAqa2V5cGFyYW1pZHMpOworCQkJCQkJCXVpbnQzMiBlc3RfZW50 cmllcywgQml0bWFwc2V0ICprZXlwYXJhbWlkcywKKwkJCQkJCQlkb3VibGUgZXN0X3VuaXF1 ZV9rZXlzKTsKIHN0YXRpYyBXaW5kb3dBZ2cgKm1ha2Vfd2luZG93YWdnKExpc3QgKnRsaXN0 LCBXaW5kb3dDbGF1c2UgKndjLAogCQkJCQkJCQkgaW50IHBhcnROdW1Db2xzLCBBdHRyTnVt YmVyICpwYXJ0Q29sSWR4LCBPaWQgKnBhcnRPcGVyYXRvcnMsIE9pZCAqcGFydENvbGxhdGlv bnMsCiAJCQkJCQkJCSBpbnQgb3JkTnVtQ29scywgQXR0ck51bWJlciAqb3JkQ29sSWR4LCBP aWQgKm9yZE9wZXJhdG9ycywgT2lkICpvcmRDb2xsYXRpb25zLApAQCAtMTcwMyw3ICsxNzA0 LDggQEAgY3JlYXRlX21lbW9pemVfcGxhbihQbGFubmVySW5mbyAqcm9vdCwgTWVtb2l6ZVBh dGggKmJlc3RfcGF0aCwgaW50IGZsYWdzKQogCiAJcGxhbiA9IG1ha2VfbWVtb2l6ZShzdWJw bGFuLCBvcGVyYXRvcnMsIGNvbGxhdGlvbnMsIHBhcmFtX2V4cHJzLAogCQkJCQkJYmVzdF9w YXRoLT5zaW5nbGVyb3csIGJlc3RfcGF0aC0+YmluYXJ5X21vZGUsCi0JCQkJCQliZXN0X3Bh dGgtPmVzdF9lbnRyaWVzLCBrZXlwYXJhbWlkcyk7CisJCQkJCQliZXN0X3BhdGgtPmVzdF9l bnRyaWVzLCBrZXlwYXJhbWlkcywKKwkJCQkJCWJlc3RfcGF0aC0+ZXN0X3VuaXF1ZV9rZXlz KTsKIAogCWNvcHlfZ2VuZXJpY19wYXRoX2luZm8oJnBsYW4tPnBsYW4sIChQYXRoICopIGJl c3RfcGF0aCk7CiAKQEAgLTY2MzYsNyArNjYzOCw4IEBAIG1hdGVyaWFsaXplX2ZpbmlzaGVk X3BsYW4oUGxhbiAqc3VicGxhbikKIHN0YXRpYyBNZW1vaXplICoKIG1ha2VfbWVtb2l6ZShQ bGFuICpsZWZ0dHJlZSwgT2lkICpoYXNob3BlcmF0b3JzLCBPaWQgKmNvbGxhdGlvbnMsCiAJ CQkgTGlzdCAqcGFyYW1fZXhwcnMsIGJvb2wgc2luZ2xlcm93LCBib29sIGJpbmFyeV9tb2Rl LAotCQkJIHVpbnQzMiBlc3RfZW50cmllcywgQml0bWFwc2V0ICprZXlwYXJhbWlkcykKKwkJ CXVpbnQzMiBlc3RfZW50cmllcywgQml0bWFwc2V0ICprZXlwYXJhbWlkcywKKwkJCWRvdWJs ZSBlc3RfdW5pcXVlX2tleXMpCiB7CiAJTWVtb2l6ZSAgICAqbm9kZSA9IG1ha2VOb2RlKE1l bW9pemUpOwogCVBsYW4JICAgKnBsYW4gPSAmbm9kZS0+cGxhbjsKQEAgLTY2NTQsNiArNjY1 Nyw3IEBAIG1ha2VfbWVtb2l6ZShQbGFuICpsZWZ0dHJlZSwgT2lkICpoYXNob3BlcmF0b3Jz LCBPaWQgKmNvbGxhdGlvbnMsCiAJbm9kZS0+YmluYXJ5X21vZGUgPSBiaW5hcnlfbW9kZTsK IAlub2RlLT5lc3RfZW50cmllcyA9IGVzdF9lbnRyaWVzOwogCW5vZGUtPmtleXBhcmFtaWRz ID0ga2V5cGFyYW1pZHM7CisJbm9kZS0+ZXN0X3VuaXF1ZV9rZXlzID0gZXN0X3VuaXF1ZV9r ZXlzOwogCiAJcmV0dXJuIG5vZGU7CiB9CmRpZmYgLS1naXQgYS9zcmMvYmFja2VuZC9vcHRp bWl6ZXIvdXRpbC9wYXRobm9kZS5jIGIvc3JjL2JhY2tlbmQvb3B0aW1pemVyL3V0aWwvcGF0 aG5vZGUuYwppbmRleCA5M2U3M2NiNDRkYi4uMWZiY2RhOTkwNjcgMTAwNjQ0Ci0tLSBhL3Ny Yy9iYWNrZW5kL29wdGltaXplci91dGlsL3BhdGhub2RlLmMKKysrIGIvc3JjL2JhY2tlbmQv b3B0aW1pemVyL3V0aWwvcGF0aG5vZGUuYwpAQCAtMTcwMSw2ICsxNzAxLDkgQEAgY3JlYXRl X21lbW9pemVfcGF0aChQbGFubmVySW5mbyAqcm9vdCwgUmVsT3B0SW5mbyAqcmVsLCBQYXRo ICpzdWJwYXRoLAogCUFzc2VydChlbmFibGVfbWVtb2l6ZSk7CiAJcGF0aG5vZGUtPnBhdGgu ZGlzYWJsZWRfbm9kZXMgPSBzdWJwYXRoLT5kaXNhYmxlZF9ub2RlczsKIAorCS8qIEVzdGlt YXRlZCBudW1iZXIgb2YgZGlzdGluY3QgbWVtb2l6YXRpb24ga2V5cywgY29tcHV0ZWQgdXNp bmcgZXN0aW1hdGVfbnVtX2dyb3VwcygpICovCisJcGF0aG5vZGUtPmVzdF91bmlxdWVfa2V5 cyA9IDA7CisKIAkvKgogCSAqIEFkZCBhIHNtYWxsIGFkZGl0aW9uYWwgY2hhcmdlIGZvciBj YWNoaW5nIHRoZSBmaXJzdCBlbnRyeS4gIEFsbCB0aGUKIAkgKiBoYXJkZXIgY2FsY3VsYXRp b25zIGZvciByZXNjYW5zIGFyZSBwZXJmb3JtZWQgaW4gY29zdF9tZW1vaXplX3Jlc2Nhbigp LgpkaWZmIC0tZ2l0IGEvc3JjL2luY2x1ZGUvbm9kZXMvcGF0aG5vZGVzLmggYi9zcmMvaW5j bHVkZS9ub2Rlcy9wYXRobm9kZXMuaAppbmRleCBjMjRhMWZjODUxNC4uMDk0NmNjZWE5OTQg MTAwNjQ0Ci0tLSBhL3NyYy9pbmNsdWRlL25vZGVzL3BhdGhub2Rlcy5oCisrKyBiL3NyYy9p bmNsdWRlL25vZGVzL3BhdGhub2Rlcy5oCkBAIC0yMDQwLDYgKzIwNDAsOCBAQCB0eXBlZGVm IHN0cnVjdCBNZW1vaXplUGF0aAogCXVpbnQzMgkJZXN0X2VudHJpZXM7CS8qIFRoZSBtYXhp bXVtIG51bWJlciBvZiBlbnRyaWVzIHRoYXQgdGhlCiAJCQkJCQkJCSAqIHBsYW5uZXIgZXhw ZWN0cyB3aWxsIGZpdCBpbiB0aGUgY2FjaGUsIG9yIDAKIAkJCQkJCQkJICogaWYgdW5rbm93 biAqLworCWRvdWJsZQkJZXN0X3VuaXF1ZV9rZXlzOwkJLyogRXN0aW1hdGVkIG51bWJlciBv ZiBkaXN0aW5jdCBtZW1vaXphdGlvbiBrZXlzLAorCQkJCQkJCQkgKiB1c2VkIGZvciBjYWNo ZSBzaXplIGV2YWx1YXRpb24uIEtlcHQgZm9yIEVYUExBSU4gKi8KIH0gTWVtb2l6ZVBhdGg7 CiAKIC8qCmRpZmYgLS1naXQgYS9zcmMvaW5jbHVkZS9ub2Rlcy9wbGFubm9kZXMuaCBiL3Ny Yy9pbmNsdWRlL25vZGVzL3BsYW5ub2Rlcy5oCmluZGV4IGY3OGJmZmQ5MGNmLi5iMzhkNGQ5 MWZiNCAxMDA2NDQKLS0tIGEvc3JjL2luY2x1ZGUvbm9kZXMvcGxhbm5vZGVzLmgKKysrIGIv c3JjL2luY2x1ZGUvbm9kZXMvcGxhbm5vZGVzLmgKQEAgLTEwNjAsNiArMTA2MCwxMiBAQCB0 eXBlZGVmIHN0cnVjdCBNZW1vaXplCiAKIAkvKiBwYXJhbWlkcyBmcm9tIHBhcmFtX2V4cHJz ICovCiAJQml0bWFwc2V0ICAqa2V5cGFyYW1pZHM7CisKKwkvKgorCSAqIEVzdGltYXRlZCBu dW1iZXIgb2YgZGlzdGluY3QgbWVtb2l6YXRpb24ga2V5cywKKwkgKiB1c2VkIGZvciBjYWNo ZSBzaXplIGV2YWx1YXRpb24uIEtlcHQgZm9yIEVYUExBSU4KKwkgKi8KKwlkb3VibGUJCWVz dF91bmlxdWVfa2V5czsKIH0gTWVtb2l6ZTsKIAogLyogLS0tLS0tLS0tLS0tLS0tLQotLSAK Mi4zNC4xCgo= --------------sVUBP7tGzzXNo06YF4da0tVm--