Message-ID: <dfb633b2-6e7b-453a-a2ed-a3a32d34bdd6@gmail.com>
Date: Sat, 23 Nov 2024 09:07:17 +0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Cardinality estimate of the inner relation
To: =?UTF-8?Q?Fr=C3=A9d=C3=A9ric_Yhuel?= <frederic.yhuel@dalibo.com>,
 pgsql-performance@lists.postgresql.org
Cc: Christophe Courtois <christophe.courtois@dalibo.com>
References: <b860c71a-7cab-4d88-ad87-8c1f2eea9ae8@dalibo.com>
Content-Language: en-US
From: Andrei Lepikhov <lepihov@gmail.com>
In-Reply-To: <b860c71a-7cab-4d88-ad87-8c1f2eea9ae8@dalibo.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/dfb633b2-6e7b-453a-a2ed-a3a32d34bdd6%40gmail.com>
Precedence: bulk

On 22/11/2024 22:53, Frédéric Yhuel wrote:
> My colleague Christophe Courtois and I have been trying to fix a bad 
> plan for one of Dalibo's clients. It is a (probably well-known) problem 
> with skewed data and a parameterized Nested Loop with an underestimation 
> of the cardinality of the inner relation.
> 
> Here is a test case (the script to create and populate the two tables is 
> at the end):
Thanks for the case provided!

I wonder if data science has invented a statistic or selectivity 
estimation technique that could tackle your case in general. As I see, 
we should touch the table of products to realise which specific ID has 
the product named 'Babar'.
I can imagine the trick when you build MCV on (id, name) and have a 
chance to find this popular ID, cache it, and use it during join clause 
estimation later. But it seems too expensive to do the same for 
arbitrary incoming queries.

If you want a workaround, such cases fit query-driven techniques. Among 
open-source ones, I can point your attention to the AQO extension 
(honestly, designed under my command). It can save information about an 
estimation error and correct the query next time.

In an enterprise-grade area, you can pick the sr_plan extension, which 
is designed to store the plan for a specific query (you can choose 
parameterisation on your own) and spread it globally across all 
instances' backends.

-- 
regards, Andrei Lepikhov