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!
