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 1s8yR9-00GUUm-5m for pgsql-hackers@arkaria.postgresql.org; Mon, 20 May 2024 08:32:05 +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 1s8yR8-00HEdQ-Am for pgsql-hackers@arkaria.postgresql.org; Mon, 20 May 2024 08:32:02 +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 1s8yR7-00HEdI-Sp for pgsql-hackers@lists.postgresql.org; Mon, 20 May 2024 08:32:02 +0000 Received: from mail.postgrespro.ru ([93.174.131.139]) by magus.postgresql.org with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1s8yR3-001A4L-S0 for pgsql-hackers@lists.postgresql.org; Mon, 20 May 2024 08:32:00 +0000 Received: from [172.30.8.38] (unknown [172.30.8.38]) (using TLSv1.2 with cipher ECDHE-RSA-AES128-GCM-SHA256 (128/128 bits)) (Client did not present a certificate) (Authenticated sender: a.lepikhov@postgrespro.ru) by mail.postgrespro.ru (Postfix/587) with ESMTPSA id C9C47E20C32; Mon, 20 May 2024 11:31:51 +0300 (MSK) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=postgrespro.ru; s=mx2023; t=1716193917; bh=tQM22JVgayrHH8Js1IKRIZoEU0V+wkRZLOa5AD73sUU=; h=Message-ID:Date:User-Agent:Subject:To:Cc:References:From: In-Reply-To:From; b=bM3qQTF793BJHZp89B4nWT/kDUBrYvPjev8UAyvIBMImXdksJcSmF9sM4SlVnA2lh mjTT89rQSDV121MySPl1Mar6/KScNo0uYGhbDHbi19TL10zzRO2xN3H67FxxPlQoVG kAomPvG3ONEav1eoJqUwiOyRfIOVci9MRFvSwH7pWWjJpgQJLwF2uaJc/oLc0/1XL0 pDDwDAbFUubPKYwEupFD4GFlm6FHXSDG5ErUeW34VeTLdNSKyC5h8kxGpGQQ2HEfBy LWSFsCxoWrELVgRoSIxj3G/BcFODBeev6gI45woP2rW7rsijumWj+0xabYPkJBz90m WaQOIiQDkOYKg== Content-Type: multipart/mixed; boundary="------------cdtXL2kSBvZNkqmVxR0BKvq7" Message-ID: <866452b8-67b3-4f67-b85c-c3bb8e831ff2@postgrespro.ru> Date: Mon, 20 May 2024 15:31:38 +0700 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: using extended statistics to improve join estimates To: Tomas Vondra Cc: Julien Rouhaud , Andres Freund , pgsql-hackers@postgresql.org, pgsql-hackers@lists.postgresql.org, Andy Fan , Justin Pryzby References: <2ed75657-e084-9539-c6de-597e5675014c@enterprisedb.com> <20211122012315.GH17618@telsasoft.com> <0213d237-efd5-8e7e-fa29-3f36e6e26023@enterprisedb.com> <1ea3bf2b-57f9-e632-251a-e718623d7a72@enterprisedb.com> <20220104235550.dgr4rz6zkjx2rykz@alap3.anarazel.de> <20220119101809.wzrjvh4jsnoakgny@jrouhaud> <20220302173821.GG15744@telsasoft.com> <20220302173943.GA28810@telsasoft.com> <87cyr89nk5.fsf@163.com> <2a9604a5-520d-48ee-b3c8-5f3342b607d3@enterprisedb.com> Content-Language: en-AU From: Andrei Lepikhov Organization: Postgres Professional In-Reply-To: <2a9604a5-520d-48ee-b3c8-5f3342b607d3@enterprisedb.com> X-KSMG-AntiPhishing: NotDetected X-KSMG-AntiSpam-Interceptor-Info: not scanned X-KSMG-AntiSpam-Status: not scanned, disabled by settings X-KSMG-AntiVirus: Kaspersky Secure Mail Gateway, version 2.1.0.7854, bases: 2024/05/20 04:38:00 #25249872 X-KSMG-AntiVirus-Status: NotDetected, skipped X-KSMG-LinksScanning: not scanned, disabled by settings X-KSMG-Message-Action: skipped X-KSMG-Rule-ID: 1 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. --------------cdtXL2kSBvZNkqmVxR0BKvq7 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit On 4/3/24 01:22, Tomas Vondra wrote: > Cool! There's obviously no chance to get this into v18, and I have stuff > to do in this CF. But I'll take a look after that. I'm looking at your patch now - an excellent start to an eagerly awaited feature! A couple of questions: 1. I didn't find the implementation of strategy 'c' - estimation by the number of distinct values. Do you forget it? 2. Can we add a clauselist selectivity hook into the core (something similar the code in attachment)? It can allow the development and testing of multicolumn join estimations without patching the core. -- regards, Andrei Lepikhov Postgres Professional --------------cdtXL2kSBvZNkqmVxR0BKvq7 Content-Type: text/x-patch; charset=UTF-8; name="clauselist_selectivity_hook.diff" Content-Disposition: attachment; filename="clauselist_selectivity_hook.diff" Content-Transfer-Encoding: base64 ZGlmZiAtLWdpdCBhL3NyYy9iYWNrZW5kL29wdGltaXplci9wYXRoL2NsYXVzZXNlbC5jIGIv c3JjL2JhY2tlbmQvb3B0aW1pemVyL3BhdGgvY2xhdXNlc2VsLmMKaW5kZXggMGFiMDIxYzFl OC4uMjcxZDM2YTUyMiAxMDA2NDQKLS0tIGEvc3JjL2JhY2tlbmQvb3B0aW1pemVyL3BhdGgv Y2xhdXNlc2VsLmMKKysrIGIvc3JjL2JhY2tlbmQvb3B0aW1pemVyL3BhdGgvY2xhdXNlc2Vs LmMKQEAgLTEyOCw2ICsxMjgsOSBAQCBjbGF1c2VsaXN0X3NlbGVjdGl2aXR5X2V4dChQbGFu bmVySW5mbyAqcm9vdCwKIAlMaXN0Q2VsbCAgICpsOwogCWludAkJCWxpc3RpZHg7CiAKKwlp ZiAoY2xhdXNlbGlzdF9zZWxlY3Rpdml0eV9ob29rKQorCQlzMSA9IGNsYXVzZWxpc3Rfc2Vs ZWN0aXZpdHlfaG9vayhyb290LCBjbGF1c2VzLCB2YXJSZWxpZCwgam9pbnR5cGUsCisJCQkJ CQkJCQkJIHNqaW5mbywgJmVzdGltYXRlZGNsYXVzZXMpOwogCS8qCiAJICogSWYgdGhlcmUn cyBleGFjdGx5IG9uZSBjbGF1c2UsIGp1c3QgZ28gZGlyZWN0bHkgdG8KIAkgKiBjbGF1c2Vf c2VsZWN0aXZpdHlfZXh0KCkuIE5vbmUgb2Ygd2hhdCB3ZSBtaWdodCBkbyBiZWxvdyBpcyBy ZWxldmFudC4KZGlmZiAtLWdpdCBhL3NyYy9iYWNrZW5kL3V0aWxzL2FkdC9zZWxmdW5jcy5j IGIvc3JjL2JhY2tlbmQvdXRpbHMvYWR0L3NlbGZ1bmNzLmMKaW5kZXggNWY1ZDc5NTlkOC4u ZmY5OGZkYTA4YyAxMDA2NDQKLS0tIGEvc3JjL2JhY2tlbmQvdXRpbHMvYWR0L3NlbGZ1bmNz LmMKKysrIGIvc3JjL2JhY2tlbmQvdXRpbHMvYWR0L3NlbGZ1bmNzLmMKQEAgLTE0Niw2ICsx NDYsNyBAQAogLyogSG9va3MgZm9yIHBsdWdpbnMgdG8gZ2V0IGNvbnRyb2wgd2hlbiB3ZSBh c2sgZm9yIHN0YXRzICovCiBnZXRfcmVsYXRpb25fc3RhdHNfaG9va190eXBlIGdldF9yZWxh dGlvbl9zdGF0c19ob29rID0gTlVMTDsKIGdldF9pbmRleF9zdGF0c19ob29rX3R5cGUgZ2V0 X2luZGV4X3N0YXRzX2hvb2sgPSBOVUxMOworY2xhdXNlbGlzdF9zZWxlY3Rpdml0eV9ob29r X3R5cGUgY2xhdXNlbGlzdF9zZWxlY3Rpdml0eV9ob29rID0gTlVMTDsKIAogc3RhdGljIGRv dWJsZSBlcXNlbF9pbnRlcm5hbChQR19GVU5DVElPTl9BUkdTLCBib29sIG5lZ2F0ZSk7CiBz dGF0aWMgZG91YmxlIGVxam9pbnNlbF9pbm5lcihPaWQgb3BmdW5jb2lkLCBPaWQgY29sbGF0 aW9uLApkaWZmIC0tZ2l0IGEvc3JjL2luY2x1ZGUvdXRpbHMvc2VsZnVuY3MuaCBiL3NyYy9p bmNsdWRlL3V0aWxzL3NlbGZ1bmNzLmgKaW5kZXggZjI1NjNhZDFjYi4uZWUyOGQzYmE5YiAx MDA2NDQKLS0tIGEvc3JjL2luY2x1ZGUvdXRpbHMvc2VsZnVuY3MuaAorKysgYi9zcmMvaW5j bHVkZS91dGlscy9zZWxmdW5jcy5oCkBAIC0xNDgsNiArMTQ4LDE1IEBAIHR5cGVkZWYgYm9v bCAoKmdldF9pbmRleF9zdGF0c19ob29rX3R5cGUpIChQbGFubmVySW5mbyAqcm9vdCwKIAkJ CQkJCQkJCQkgICBWYXJpYWJsZVN0YXREYXRhICp2YXJkYXRhKTsKIGV4dGVybiBQR0RMTElN UE9SVCBnZXRfaW5kZXhfc3RhdHNfaG9va190eXBlIGdldF9pbmRleF9zdGF0c19ob29rOwog CisvKiBIb29rcyBmb3IgcGx1Z2lucyB0byBnZXQgY29udHJvbCB3aGVuIHdlIGFzayBmb3Ig c2VsZWN0aXZpdHkgZXN0aW1hdGlvbiAqLwordHlwZWRlZiBib29sICgqY2xhdXNlbGlzdF9z ZWxlY3Rpdml0eV9ob29rX3R5cGUpIChQbGFubmVySW5mbyAqcm9vdCwKKwkJCQkJCQkJCQkJ CSAgTGlzdCAqY2xhdXNlcywKKwkJCQkJCQkJCQkJCSAgaW50IHZhclJlbGlkLAorCQkJCQkJ CQkJCQkJICBKb2luVHlwZSBqb2ludHlwZSwKKwkJCQkJCQkJCQkJCSAgU3BlY2lhbEpvaW5J bmZvICpzamluZm8sCisJCQkJCQkJCQkJCQkgIEJpdG1hcHNldCAqKmVzdGltYXRlZGNsYXVz ZXMpOworZXh0ZXJuIFBHRExMSU1QT1JUIGNsYXVzZWxpc3Rfc2VsZWN0aXZpdHlfaG9va190 eXBlIGNsYXVzZWxpc3Rfc2VsZWN0aXZpdHlfaG9vazsKKwogLyogRnVuY3Rpb25zIGluIHNl bGZ1bmNzLmMgKi8KIAogZXh0ZXJuIHZvaWQgZXhhbWluZV92YXJpYWJsZShQbGFubmVySW5m byAqcm9vdCwgTm9kZSAqbm9kZSwgaW50IHZhclJlbGlkLAo= --------------cdtXL2kSBvZNkqmVxR0BKvq7--