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 1ovpEY-0002BB-V7 for pgsql-hackers@arkaria.postgresql.org; Fri, 18 Nov 2022 00:27:55 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1ovpEX-00058O-PG for pgsql-hackers@arkaria.postgresql.org; Fri, 18 Nov 2022 00:27:53 +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 1ovpEX-00055N-0o for pgsql-hackers@lists.postgresql.org; Fri, 18 Nov 2022 00:27:53 +0000 Received: from mail-ej1-x629.google.com ([2a00:1450:4864:20::629]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1ovpEU-0003q9-7T for pgsql-hackers@lists.postgresql.org; Fri, 18 Nov 2022 00:27:51 +0000 Received: by mail-ej1-x629.google.com with SMTP id k2so9312760ejr.2 for ; Thu, 17 Nov 2022 16:27:50 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=enterprisedb.com; s=google; h=content-transfer-encoding:in-reply-to:from:content-language :references:cc:to:subject:user-agent:mime-version:date:message-id :from:to:cc:subject:date:message-id:reply-to; bh=JupVrOZh8v9fWmDpDzf/4wyJ9uhzMaZtqapyyj8jsBY=; b=Wxj414S+cH4xp3BCz1VJGrsBPKpSzf74imZmixWJ73JBRtgoSmAq8LhDYURJHoZttH AtsaThAtvi4IsBK+wh04s0CWuWHZhLS2vpdx9iOsgvlN1P15E38vxHjxlOpDrrl2cexO aud9mIHjK9No+ECth6wCi845o//6ppkZQgj/LnqlcQQZ1rDK+Mezj4ks4JdD08LXdWD9 QvdAUIiC9wCzsotOULF7swxNsNuslsTEJIjr93KuBXkx4L8MFnadjMPbRZfLKSJsLjro fAxtbUs9bwE/xzOJ+DInkwW7C08dLqcn7NxCk5AH+Wab14dNPooW1mtp6oRPnPU5DoOG MmoQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=content-transfer-encoding:in-reply-to:from:content-language :references:cc:to:subject:user-agent:mime-version:date:message-id :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=JupVrOZh8v9fWmDpDzf/4wyJ9uhzMaZtqapyyj8jsBY=; b=cUoSKFtTciOyoC8hcMG3cZjUH0tTEYs3bbIv6JoLnlYmOKy7i0pQBF85TV9921bpcB R8SW1a409qkj6QTYrynbXBjk7Jk1VUc1BkZahzd70d7lknihVK7f1Q63KYYq0n15qVzU B8VghlW5S1rgbvlKyfesUDaivOUi2CTsaHmExuaNhUzvv/KB2gSj50QtRk9lrCVi3tAe HmyLe3csE7I2LnMRuLqGZi6kJfA+age3Q+9bsNhV13VyuH4KIZ/BlGp84oYa4DLEc/iC P6qEpYdOgTSYaTLshb2iX1t00pv46hkmfExGAea43aM9oJ+x0toLGoN9uRNxkZhvFqQm cNuw== X-Gm-Message-State: ANoB5pkwOs/zfrT9udgnmz4/DDMpm13h9EXL9FnCGPnrMAeC+m4MlLMo 40i0tFLdJsPc0yb5Hg0MAiYgVWqcb02hHH5F3B0o+3AeGdZGCZdJUvJPdq8Z5NxlbAoRoyAE0Lf rBK8AOROUW2vcXaMJsn0eAdyq9LK7V+tQ8/eTT2zQOTgUiB9gGT9BpSugw2/tph1MQf5RpOviJC f+zODLbdNzUhAlupgL/3k+aAEwMhOm8WOaVtCdLPyHTVTz6s5sd0zH96CWoMbapsvySqbP30Jnf VL01TxWxNgtaCAqu9tqI7di/fFfLyXz4VOEEFPR1AZiZVZXGG7xU470G8I+ X-Google-Smtp-Source: AA0mqf6Cr65KZFB8aS+HgSHFjJPVxk+Ob0H7KVqeNpmqa5+ChFDOO7z7vZfGk8MP+HmKJDnATQAPCw== X-Received: by 2002:a17:906:a094:b0:7ad:88f8:761f with SMTP id q20-20020a170906a09400b007ad88f8761fmr3810562ejy.417.1668731268563; Thu, 17 Nov 2022 16:27:48 -0800 (PST) Received: from [10.137.0.17] (ip-86-49-228-162.bb.vodafone.cz. [86.49.228.162]) by smtp.gmail.com with ESMTPSA id kv15-20020a17090778cf00b00780f24b797dsm979898ejc.108.2022.11.17.16.27.47 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Thu, 17 Nov 2022 16:27:47 -0800 (PST) Message-ID: <2f30ba9c-b707-55ae-0afd-68eb6a8a7708@enterprisedb.com> Date: Fri, 18 Nov 2022 01:27:45 +0100 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.4.1 Subject: Re: Optimize join selectivity estimation by not reading MCV stats for unique join attributes To: David Geier , Tom Lane Cc: PostgreSQL Developers References: <522124.1668179795@sss.pgh.pa.us> <16acb727-24e8-6453-1711-aae339802966@gmail.com> Content-Language: en-US From: Tomas Vondra In-Reply-To: <16acb727-24e8-6453-1711-aae339802966@gmail.com> Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 7bit X-CLOUD-SEC-AV-Info: enterprisedb,google_mail,monitor X-CLOUD-SEC-AV-Sent: true X-Gm-Spam: 0 X-Gm-Phishy: 0 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 11/14/22 10:19, David Geier wrote: > Hi Tom, >> There won't *be* any MCV stats for a column that ANALYZE perceives to >> be unique, so I'm not quite sure where the claimed savings comes from. > > We save if one join attribute is unique while the other isn't. In that > case stored MCV stats are read for the non-unique attribute but then > never used. This is because MCV stats in join selectivity estimation are > only used if they're present on both columns > Right - if we only have MCV on one side of the join, we currently end up loading the MCV we have only to not use it anyway. The uniqueness is a simple way to detect some of those cases. I'd bet the savings can be quite significant for small joins and/or cases with large MCV. I wonder if we might be yet a bit smarter, though. For example, assume the first attribute is not defined as "unique" but we still don't have a MCV (it may be unique - or close to unique - in practice, or maybe it's just uniform distribution). We end up with have_mcvs1 = false Can't we just skip trying to load the second MCV? So we could do if (have_mcvs1 && HeapTupleIsValid(vardata2.statsTuple)) { ... try loading mcv2 ... } Or perhaps what if we have a function that quickly determines if the attribute has MCV, without loading it? I'd bet the expensive part of get_attstatslot() is the deconstruct_array(). We could have a function that only does the first small loop over slots, and returns true/false if we have a slot of the requested stakind. It might even check the isunique flag first, to make it more convenient. And only if both sides return "true" we'd load the MCV, deconstruct the array and all that. regards -- Tomas Vondra EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company