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 1s8ypR-00GWXK-IR for pgsql-hackers@arkaria.postgresql.org; Mon, 20 May 2024 08:57:11 +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 1s8ypR-00HLd2-Iv for pgsql-hackers@arkaria.postgresql.org; Mon, 20 May 2024 08:57:09 +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 1s8ypQ-00HLcu-Mw for pgsql-hackers@lists.postgresql.org; Mon, 20 May 2024 08:57:09 +0000 Received: from m16.mail.163.com ([117.135.210.2]) by makus.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1s8ypL-0015zu-1m for pgsql-hackers@lists.postgresql.org; Mon, 20 May 2024 08:57:06 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=163.com; s=s110527; h=From:Subject:Date:Message-ID:MIME-Version: Content-Type; bh=gvfZPVZ/MIiGZWm9Sl+02UsyuHXpdoBOgDjLY4KsI5g=; b=Eq2CUssMiIqyAU7Vz5AezwsY0Whgt0nW3ElJpKsESR8JZUcDFxBKl9l5OPV61K 33W8h9hI64oi21abgfx2FBv+U7cQyqxaf/kRv4lPOOUZIp94Mcvg1ulV/eBzrVC4 DvxmuBbuVf6XNcvJcHJCCVsiRUGg9FuWBxHoHRDJKio9U= Received: from 8235eee8a2a0 (unknown [140.205.118.163]) by gzga-smtp-mta-g3-3 (Coremail) with SMTP id _____wDHD3pQEEtmBXtsBA--.49725S3; Mon, 20 May 2024 16:56:49 +0800 (CST) 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> <866452b8-67b3-4f67-b85c-c3bb8e831ff2@postgrespro.ru> User-agent: mu4e 1.10.7; emacs 29.1 From: Andy Fan To: Andrei Lepikhov Cc: Tomas Vondra , Julien Rouhaud , Andres Freund , pgsql-hackers@postgresql.org, Justin Pryzby , pgsql-hackers@lists.postgresql.org Subject: Re: using extended statistics to improve join estimates Date: Mon, 20 May 2024 16:52:07 +0800 In-reply-to: <866452b8-67b3-4f67-b85c-c3bb8e831ff2@postgrespro.ru> Message-ID: <87o790kgj3.fsf@163.com> MIME-Version: 1.0 Content-Type: text/plain X-CM-TRANSID:_____wDHD3pQEEtmBXtsBA--.49725S3 X-Coremail-Antispam: 1Uf129KBjvdXoWrtryUuw45Kw1DXF47JFy5Jwb_yoWfZwc_ta sFgFWDWw4q9Fs8JF4xA3Z3Gws3tFZrXFZ0vFWftr9xXr18GFZxCFWvkFnxurWkJrWfWFn3 tr1kurW3WF4IgjkaLaAFLSUrUUUUjb8apTn2vfkv8UJUUUU8Yxn0WfASr-VFAUDa7-sFnT 9fnUUvcSsGvfC2KfnxnUUI43ZEXa7IUjpBT7UUUUU== X-Originating-IP: [140.205.118.163] X-CM-SenderInfo: x2klx3xlid0iqsrtqiywtou0bp/xtbBZxTkU2V4HMo08gAAsJ List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Hi Andrei, > 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? What do you mean the "strategy 'c'"? > 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. The idea LGTM. But do you want + if (clauselist_selectivity_hook) + s1 = clauselist_selectivity_hook(root, clauses, varRelid, jointype, + rather than + if (clauselist_selectivity_hook) + *return* clauselist_selectivity_hook(root, clauses, ..) ? -- Best Regards Andy Fan