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 1tvGVk-002ujP-4Q for pgsql-hackers@arkaria.postgresql.org; Thu, 20 Mar 2025 14:04:40 +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 1tvGUj-0027cn-3W for pgsql-hackers@arkaria.postgresql.org; Thu, 20 Mar 2025 14:03:37 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tvGUi-0027cf-Br for pgsql-hackers@lists.postgresql.org; Thu, 20 Mar 2025 14:03:36 +0000 Received: from forward502b.mail.yandex.net ([178.154.239.146]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1tvGUe-0008aA-2n for pgsql-hackers@lists.postgresql.org; Thu, 20 Mar 2025 14:03:35 +0000 Received: from mail-nwsmtp-smtp-production-main-60.sas.yp-c.yandex.net (mail-nwsmtp-smtp-production-main-60.sas.yp-c.yandex.net [IPv6:2a02:6b8:c27:153f:0:640:5e4f:0]) by forward502b.mail.yandex.net (Yandex) with ESMTPS id 5459C6147E; Thu, 20 Mar 2025 17:03:29 +0300 (MSK) Received: by mail-nwsmtp-smtp-production-main-60.sas.yp-c.yandex.net (smtp/Yandex) with ESMTPSA id P3LeQQ2LbOs0-iWKLINZe; Thu, 20 Mar 2025 17:03:28 +0300 X-Yandex-Fwd: 1 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=tantorlabs.com; s=mail; t=1742479408; bh=zQvgt19faQFpw++W2deYJfFbQTy4uojwn+zxTnQpkV8=; h=In-Reply-To:Cc:Date:References:To:Subject:Message-ID:From; b=xaR490nRkV2ldfYXnAiR/2ugIkuhSzFADQJMJpJ4OKSIE2DD5ortCinlKahutfFoD FM8kbwCAi16Dx1lqp61X0IQr93fJSTZ7M5/JbXi5f1GJISU8imsNNPj++vcNbpulS5 usiHrb7xT77O1ja1ymPPkRR7Z3CAl4bNpbBknAhI= Authentication-Results: mail-nwsmtp-smtp-production-main-60.sas.yp-c.yandex.net; dkim=pass header.i=@tantorlabs.com Content-Type: multipart/mixed; boundary="------------gfI0ff8qxYmw98G7K8r26Zow" Message-ID: Date: Thu, 20 Mar 2025 17:03:25 +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> Content-Language: en-US From: Ilia Evdokimov In-Reply-To: <44229379-3901-4cb0-8812-b354ef70d5e2@gmail.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. --------------gfI0ff8qxYmw98G7K8r26Zow Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit On 20.03.2025 15:32, Andrei Lepikhov wrote: > I quite frequently need the number of distinct values (or groups) > predicted during the Memoize node creation to understand why caching > is sometimes employed or not. > But I had thought about an alternative way: having an extensible > EXPLAIN (thanks to Robert), we may save optimisation-stage data (I > have the same necessity in the case of IncrementalSort, for example) > and put it into the Plan node on-demand. So, the way I want to go is a > Plan::extlist node and create_plan hook, which may allow copying > best_path data to the final plan. So, here, we will add a new > parameter and avoid touching the core code. > But I would give +1 to current approach if it were done in a shorter > time. Then before proceeding further, I think we need to benchmark this change to ensure there are no performance regressions. If performance issues arise, then using extensible EXPLAIN might be the only viable approach. I have addressed most of the review comments except for the ExplainPropertyText change. I am attaching v3 of the patch with these updates. If anyone notices any performance issues, please let me know. Issue with ExplainPropertyText for this thread I'll fix in the next patches if it will be necessary. So far, I have tested it on small machines. There are no performance issues yet. I'll run benchmarks on larger ones soon. -- Best regards, Ilia Evdokimov, Tantor Labs LLC. --------------gfI0ff8qxYmw98G7K8r26Zow Content-Type: text/x-patch; charset=UTF-8; name="v3-0001-Show-ndistinct-and-est_entries-in-EXPLAIN-for-Memoize.patch" Content-Disposition: attachment; filename*0="v3-0001-Show-ndistinct-and-est_entries-in-EXPLAIN-for-Memoiz"; filename*1="e.patch" Content-Transfer-Encoding: base64 RnJvbSAwMmE5YjQ3OTNkZWI3NGYyZTY3ZGFjYWJlYzI1OWM0Y2Q5MjlkOTk1IE1vbiBTZXAg MTcgMDA6MDA6MDAgMjAwMQpGcm9tOiBJbGlhIEV2ZG9raW1vdiA8aWx5YS5ldmRva2ltb3ZA dGFudG9ybGFicy5ydT4KRGF0ZTogVGh1LCAyMCBNYXIgMjAyNSAxNjo1ODo0OCArMDMwMApT dWJqZWN0OiBbUEFUQ0ggdjNdIFNob3cgbmRpc3RpbmN0IGFuZCBlc3RfZW50cmllcyBpbiBF WFBMQUlOIGZvciBNZW1vaXplCgotLS0KIHNyYy9iYWNrZW5kL2NvbW1hbmRzL2V4cGxhaW4u YyAgICAgICAgICB8IDEyICsrKysrKysrKysrKwogc3JjL2JhY2tlbmQvb3B0aW1pemVyL3Bh dGgvY29zdHNpemUuYyAgIHwgIDMgKysrCiBzcmMvYmFja2VuZC9vcHRpbWl6ZXIvcGxhbi9j cmVhdGVwbGFuLmMgfCAxMCArKysrKysrLS0tCiBzcmMvYmFja2VuZC9vcHRpbWl6ZXIvdXRp bC9wYXRobm9kZS5jICAgfCAgMyArKysKIHNyYy9pbmNsdWRlL25vZGVzL3BhdGhub2Rlcy5o ICAgICAgICAgICB8ICAyICsrCiBzcmMvaW5jbHVkZS9ub2Rlcy9wbGFubm9kZXMuaCAgICAg ICAgICAgfCAgNiArKysrKysKIDYgZmlsZXMgY2hhbmdlZCwgMzMgaW5zZXJ0aW9ucygrKSwg MyBkZWxldGlvbnMoLSkKCmRpZmYgLS1naXQgYS9zcmMvYmFja2VuZC9jb21tYW5kcy9leHBs YWluLmMgYi9zcmMvYmFja2VuZC9jb21tYW5kcy9leHBsYWluLmMKaW5kZXggMzNhMTZkMmQ4 ZTIuLjRkOGU5NGQzZmViIDEwMDY0NAotLS0gYS9zcmMvYmFja2VuZC9jb21tYW5kcy9leHBs YWluLmMKKysrIGIvc3JjL2JhY2tlbmQvY29tbWFuZHMvZXhwbGFpbi5jCkBAIC0zNjI5LDYg KzM2MjksMTEgQEAgc2hvd19tZW1vaXplX2luZm8oTWVtb2l6ZVN0YXRlICptc3RhdGUsIExp c3QgKmFuY2VzdG9ycywgRXhwbGFpblN0YXRlICplcykKIAl7CiAJCUV4cGxhaW5Qcm9wZXJ0 eVRleHQoIkNhY2hlIEtleSIsIGtleXN0ci5kYXRhLCBlcyk7CiAJCUV4cGxhaW5Qcm9wZXJ0 eVRleHQoIkNhY2hlIE1vZGUiLCBtc3RhdGUtPmJpbmFyeV9tb2RlID8gImJpbmFyeSIgOiAi bG9naWNhbCIsIGVzKTsKKwkJaWYgKGVzLT5jb3N0cykKKwkJeworCQkJRXhwbGFpblByb3Bl cnR5RmxvYXQoIkVzdGltYXRlZCBDYXBhY2l0eSIsICIiLCAoKE1lbW9pemUgKikgcGxhbikt PmVzdF9lbnRyaWVzLCAwLCBlcyk7CisJCQlFeHBsYWluUHJvcGVydHlGbG9hdCgiRXN0aW1h dGVkIERpc3RpbmN0IExvb2t1cCBLZXlzIiwgIiIsICgoTWVtb2l6ZSAqKSBwbGFuKS0+ZXN0 X3VuaXF1ZV9rZXlzLCAwLCBlcyk7CisJCX0KIAl9CiAJZWxzZQogCXsKQEAgLTM2MzYsNiAr MzY0MSwxMyBAQCBzaG93X21lbW9pemVfaW5mbyhNZW1vaXplU3RhdGUgKm1zdGF0ZSwgTGlz dCAqYW5jZXN0b3JzLCBFeHBsYWluU3RhdGUgKmVzKQogCQlhcHBlbmRTdHJpbmdJbmZvKGVz LT5zdHIsICJDYWNoZSBLZXk6ICVzXG4iLCBrZXlzdHIuZGF0YSk7CiAJCUV4cGxhaW5JbmRl bnRUZXh0KGVzKTsKIAkJYXBwZW5kU3RyaW5nSW5mbyhlcy0+c3RyLCAiQ2FjaGUgTW9kZTog JXNcbiIsIG1zdGF0ZS0+YmluYXJ5X21vZGUgPyAiYmluYXJ5IiA6ICJsb2dpY2FsIik7CisJ CWlmIChlcy0+Y29zdHMpCisJCXsKKwkJCUV4cGxhaW5JbmRlbnRUZXh0KGVzKTsKKwkJCWFw cGVuZFN0cmluZ0luZm8oZXMtPnN0ciwgIkVzdGltYXRlZCBDYXBhY2l0eTogJXUsIEVzdGlt YXRlZCBEaXN0aW5jdCBMb29rdXAgS2V5czogJTAuMGZcbiIsCisJCQkJCQkJKChNZW1vaXpl ICopIHBsYW4pLT5lc3RfZW50cmllcywKKwkJCQkJCQkoKE1lbW9pemUgKikgcGxhbiktPmVz dF91bmlxdWVfa2V5cyk7CisJCX0KIAl9CiAKIAlwZnJlZShrZXlzdHIuZGF0YSk7CmRpZmYg LS1naXQgYS9zcmMvYmFja2VuZC9vcHRpbWl6ZXIvcGF0aC9jb3N0c2l6ZS5jIGIvc3JjL2Jh Y2tlbmQvb3B0aW1pemVyL3BhdGgvY29zdHNpemUuYwppbmRleCAyNTY1NjhkMDVhMi4uNGU0 MmFmYmY1M2EgMTAwNjQ0Ci0tLSBhL3NyYy9iYWNrZW5kL29wdGltaXplci9wYXRoL2Nvc3Rz aXplLmMKKysrIGIvc3JjL2JhY2tlbmQvb3B0aW1pemVyL3BhdGgvY29zdHNpemUuYwpAQCAt MjYwNCw2ICsyNjA0LDkgQEAgY29zdF9tZW1vaXplX3Jlc2NhbihQbGFubmVySW5mbyAqcm9v dCwgTWVtb2l6ZVBhdGggKm1wYXRoLAogCW1wYXRoLT5lc3RfZW50cmllcyA9IE1pbihNaW4o bmRpc3RpbmN0LCBlc3RfY2FjaGVfZW50cmllcyksCiAJCQkJCQkJIFBHX1VJTlQzMl9NQVgp OwogCisJLyogUmVtZW1iZXIgbmRpc3RpbmN0IGZvciBhIHBvdGVudGlhbCBFWFBMQUlOIGxh dGVyICovCisJbXBhdGgtPmVzdF91bmlxdWVfa2V5cyA9IG5kaXN0aW5jdDsKKwogCS8qCiAJ ICogV2hlbiB0aGUgbnVtYmVyIG9mIGRpc3RpbmN0IHBhcmFtZXRlciB2YWx1ZXMgaXMgYWJv dmUgdGhlIGFtb3VudCB3ZSBjYW4KIAkgKiBzdG9yZSBpbiB0aGUgY2FjaGUsIHRoZW4gd2Un bGwgaGF2ZSB0byBldmljdCBzb21lIGVudHJpZXMgZnJvbSB0aGUKZGlmZiAtLWdpdCBhL3Ny Yy9iYWNrZW5kL29wdGltaXplci9wbGFuL2NyZWF0ZXBsYW4uYyBiL3NyYy9iYWNrZW5kL29w dGltaXplci9wbGFuL2NyZWF0ZXBsYW4uYwppbmRleCA3NWUyYjBiOTAzNi4uNTk2YjgyYzVk YzkgMTAwNjQ0Ci0tLSBhL3NyYy9iYWNrZW5kL29wdGltaXplci9wbGFuL2NyZWF0ZXBsYW4u YworKysgYi9zcmMvYmFja2VuZC9vcHRpbWl6ZXIvcGxhbi9jcmVhdGVwbGFuLmMKQEAgLTI4 NCw3ICsyODQsOCBAQCBzdGF0aWMgTWF0ZXJpYWwgKm1ha2VfbWF0ZXJpYWwoUGxhbiAqbGVm dHRyZWUpOwogc3RhdGljIE1lbW9pemUgKm1ha2VfbWVtb2l6ZShQbGFuICpsZWZ0dHJlZSwg T2lkICpoYXNob3BlcmF0b3JzLAogCQkJCQkJCSBPaWQgKmNvbGxhdGlvbnMsIExpc3QgKnBh cmFtX2V4cHJzLAogCQkJCQkJCSBib29sIHNpbmdsZXJvdywgYm9vbCBiaW5hcnlfbW9kZSwK LQkJCQkJCQkgdWludDMyIGVzdF9lbnRyaWVzLCBCaXRtYXBzZXQgKmtleXBhcmFtaWRzKTsK KwkJCQkJCQl1aW50MzIgZXN0X2VudHJpZXMsIEJpdG1hcHNldCAqa2V5cGFyYW1pZHMsCisJ CQkJCQkJZG91YmxlIGVzdF91bmlxdWVfa2V5cyk7CiBzdGF0aWMgV2luZG93QWdnICptYWtl X3dpbmRvd2FnZyhMaXN0ICp0bGlzdCwgV2luZG93Q2xhdXNlICp3YywKIAkJCQkJCQkJIGlu dCBwYXJ0TnVtQ29scywgQXR0ck51bWJlciAqcGFydENvbElkeCwgT2lkICpwYXJ0T3BlcmF0 b3JzLCBPaWQgKnBhcnRDb2xsYXRpb25zLAogCQkJCQkJCQkgaW50IG9yZE51bUNvbHMsIEF0 dHJOdW1iZXIgKm9yZENvbElkeCwgT2lkICpvcmRPcGVyYXRvcnMsIE9pZCAqb3JkQ29sbGF0 aW9ucywKQEAgLTE3MDMsNyArMTcwNCw4IEBAIGNyZWF0ZV9tZW1vaXplX3BsYW4oUGxhbm5l ckluZm8gKnJvb3QsIE1lbW9pemVQYXRoICpiZXN0X3BhdGgsIGludCBmbGFncykKIAogCXBs YW4gPSBtYWtlX21lbW9pemUoc3VicGxhbiwgb3BlcmF0b3JzLCBjb2xsYXRpb25zLCBwYXJh bV9leHBycywKIAkJCQkJCWJlc3RfcGF0aC0+c2luZ2xlcm93LCBiZXN0X3BhdGgtPmJpbmFy eV9tb2RlLAotCQkJCQkJYmVzdF9wYXRoLT5lc3RfZW50cmllcywga2V5cGFyYW1pZHMpOwor CQkJCQkJYmVzdF9wYXRoLT5lc3RfZW50cmllcywga2V5cGFyYW1pZHMsCisJCQkJCQliZXN0 X3BhdGgtPmVzdF91bmlxdWVfa2V5cyk7CiAKIAljb3B5X2dlbmVyaWNfcGF0aF9pbmZvKCZw bGFuLT5wbGFuLCAoUGF0aCAqKSBiZXN0X3BhdGgpOwogCkBAIC02NjM2LDcgKzY2MzgsOCBA QCBtYXRlcmlhbGl6ZV9maW5pc2hlZF9wbGFuKFBsYW4gKnN1YnBsYW4pCiBzdGF0aWMgTWVt b2l6ZSAqCiBtYWtlX21lbW9pemUoUGxhbiAqbGVmdHRyZWUsIE9pZCAqaGFzaG9wZXJhdG9y cywgT2lkICpjb2xsYXRpb25zLAogCQkJIExpc3QgKnBhcmFtX2V4cHJzLCBib29sIHNpbmds ZXJvdywgYm9vbCBiaW5hcnlfbW9kZSwKLQkJCSB1aW50MzIgZXN0X2VudHJpZXMsIEJpdG1h cHNldCAqa2V5cGFyYW1pZHMpCisJCQl1aW50MzIgZXN0X2VudHJpZXMsIEJpdG1hcHNldCAq a2V5cGFyYW1pZHMsCisJCQlkb3VibGUgZXN0X3VuaXF1ZV9rZXlzKQogewogCU1lbW9pemUg ICAgKm5vZGUgPSBtYWtlTm9kZShNZW1vaXplKTsKIAlQbGFuCSAgICpwbGFuID0gJm5vZGUt PnBsYW47CkBAIC02NjU0LDYgKzY2NTcsNyBAQCBtYWtlX21lbW9pemUoUGxhbiAqbGVmdHRy ZWUsIE9pZCAqaGFzaG9wZXJhdG9ycywgT2lkICpjb2xsYXRpb25zLAogCW5vZGUtPmJpbmFy eV9tb2RlID0gYmluYXJ5X21vZGU7CiAJbm9kZS0+ZXN0X2VudHJpZXMgPSBlc3RfZW50cmll czsKIAlub2RlLT5rZXlwYXJhbWlkcyA9IGtleXBhcmFtaWRzOworCW5vZGUtPmVzdF91bmlx dWVfa2V5cyA9IGVzdF91bmlxdWVfa2V5czsKIAogCXJldHVybiBub2RlOwogfQpkaWZmIC0t Z2l0IGEvc3JjL2JhY2tlbmQvb3B0aW1pemVyL3V0aWwvcGF0aG5vZGUuYyBiL3NyYy9iYWNr ZW5kL29wdGltaXplci91dGlsL3BhdGhub2RlLmMKaW5kZXggOTNlNzNjYjQ0ZGIuLjFmYmNk YTk5MDY3IDEwMDY0NAotLS0gYS9zcmMvYmFja2VuZC9vcHRpbWl6ZXIvdXRpbC9wYXRobm9k ZS5jCisrKyBiL3NyYy9iYWNrZW5kL29wdGltaXplci91dGlsL3BhdGhub2RlLmMKQEAgLTE3 MDEsNiArMTcwMSw5IEBAIGNyZWF0ZV9tZW1vaXplX3BhdGgoUGxhbm5lckluZm8gKnJvb3Qs IFJlbE9wdEluZm8gKnJlbCwgUGF0aCAqc3VicGF0aCwKIAlBc3NlcnQoZW5hYmxlX21lbW9p emUpOwogCXBhdGhub2RlLT5wYXRoLmRpc2FibGVkX25vZGVzID0gc3VicGF0aC0+ZGlzYWJs ZWRfbm9kZXM7CiAKKwkvKiBFc3RpbWF0ZWQgbnVtYmVyIG9mIGRpc3RpbmN0IG1lbW9pemF0 aW9uIGtleXMsIGNvbXB1dGVkIHVzaW5nIGVzdGltYXRlX251bV9ncm91cHMoKSAqLworCXBh dGhub2RlLT5lc3RfdW5pcXVlX2tleXMgPSAwOworCiAJLyoKIAkgKiBBZGQgYSBzbWFsbCBh ZGRpdGlvbmFsIGNoYXJnZSBmb3IgY2FjaGluZyB0aGUgZmlyc3QgZW50cnkuICBBbGwgdGhl CiAJICogaGFyZGVyIGNhbGN1bGF0aW9ucyBmb3IgcmVzY2FucyBhcmUgcGVyZm9ybWVkIGlu IGNvc3RfbWVtb2l6ZV9yZXNjYW4oKS4KZGlmZiAtLWdpdCBhL3NyYy9pbmNsdWRlL25vZGVz L3BhdGhub2Rlcy5oIGIvc3JjL2luY2x1ZGUvbm9kZXMvcGF0aG5vZGVzLmgKaW5kZXggYzI0 YTFmYzg1MTQuLjA5NDZjY2VhOTk0IDEwMDY0NAotLS0gYS9zcmMvaW5jbHVkZS9ub2Rlcy9w YXRobm9kZXMuaAorKysgYi9zcmMvaW5jbHVkZS9ub2Rlcy9wYXRobm9kZXMuaApAQCAtMjA0 MCw2ICsyMDQwLDggQEAgdHlwZWRlZiBzdHJ1Y3QgTWVtb2l6ZVBhdGgKIAl1aW50MzIJCWVz dF9lbnRyaWVzOwkvKiBUaGUgbWF4aW11bSBudW1iZXIgb2YgZW50cmllcyB0aGF0IHRoZQog CQkJCQkJCQkgKiBwbGFubmVyIGV4cGVjdHMgd2lsbCBmaXQgaW4gdGhlIGNhY2hlLCBvciAw CiAJCQkJCQkJCSAqIGlmIHVua25vd24gKi8KKwlkb3VibGUJCWVzdF91bmlxdWVfa2V5czsJ CS8qIEVzdGltYXRlZCBudW1iZXIgb2YgZGlzdGluY3QgbWVtb2l6YXRpb24ga2V5cywKKwkJ CQkJCQkJICogdXNlZCBmb3IgY2FjaGUgc2l6ZSBldmFsdWF0aW9uLiBLZXB0IGZvciBFWFBM QUlOICovCiB9IE1lbW9pemVQYXRoOwogCiAvKgpkaWZmIC0tZ2l0IGEvc3JjL2luY2x1ZGUv bm9kZXMvcGxhbm5vZGVzLmggYi9zcmMvaW5jbHVkZS9ub2Rlcy9wbGFubm9kZXMuaAppbmRl eCBmNzhiZmZkOTBjZi4uYjM4ZDRkOTFmYjQgMTAwNjQ0Ci0tLSBhL3NyYy9pbmNsdWRlL25v ZGVzL3BsYW5ub2Rlcy5oCisrKyBiL3NyYy9pbmNsdWRlL25vZGVzL3BsYW5ub2Rlcy5oCkBA IC0xMDYwLDYgKzEwNjAsMTIgQEAgdHlwZWRlZiBzdHJ1Y3QgTWVtb2l6ZQogCiAJLyogcGFy YW1pZHMgZnJvbSBwYXJhbV9leHBycyAqLwogCUJpdG1hcHNldCAgKmtleXBhcmFtaWRzOwor CisJLyoKKwkgKiBFc3RpbWF0ZWQgbnVtYmVyIG9mIGRpc3RpbmN0IG1lbW9pemF0aW9uIGtl eXMsCisJICogdXNlZCBmb3IgY2FjaGUgc2l6ZSBldmFsdWF0aW9uLiBLZXB0IGZvciBFWFBM QUlOCisJICovCisJZG91YmxlCQllc3RfdW5pcXVlX2tleXM7CiB9IE1lbW9pemU7CiAKIC8q IC0tLS0tLS0tLS0tLS0tLS0KLS0gCjIuMzQuMQoK --------------gfI0ff8qxYmw98G7K8r26Zow--