Re: Cardinality estimate of the inner relation

On 11/23/24 03:07, Andrei Lepikhov wrote:
> Thanks for the case provided!
> 

Thanks for your answer!

> 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'.

There are many such IDs in my test case (only one in our initial 
customer's case, but I thought the test case should be more generic). It 
doesn't really change the reasoning much, but I say it for the sake of 
accuracy.

> 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.
> 

I'm not sure that I understand the idea, but I've never tried to hack 
into the planner's code yet, so...

> 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.

Interesting, thanks!