Re: Cardinality estimate of the inner relation - Mailing list pgsql-performance

From Frédéric Yhuel
Subject Re: Cardinality estimate of the inner relation
Date
Msg-id fe839a18-6bd5-49c2-a59f-52c726ee2302@dalibo.com
Whole thread Raw
In response to Re: Cardinality estimate of the inner relation  (Andrei Lepikhov <lepihov@gmail.com>)
List pgsql-performance

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!




pgsql-performance by date:

Previous
From: Andrei Lepikhov
Date:
Subject: Re: Performance of Query 60 on TPC-DS Benchmark
Next
From: Ba Jinsheng
Date:
Subject: Performance of TPC-DS Query 95