Re: Help with row estimate problem

On Tue, Jul 30, 2024 at 11:34 AM Andrei Lepikhov <lepihov(at)gmail(dot)com> wrote:
>
> Thanks for report. I see such cases frequently enough and the key
> problem here is data skew, as you already mentioned. Extended statistics
> doesn't help here. Also, because we can't estimate specific values
> coming from the outer NestLoop - we can't involve MCV to estimate
> selectivity of the population. That's the reason why the optimiser uses
> ndistinct value.
> What you can do? I see only one option - split the table to some
> partitions where data will be distributed more or less uniformly. And
> invent a criteria for pruning unnecessary partitions.
> Of course, you can also try pg_hint_plan and force planner to use
> MergeJoin or HashJoin in that suspicious case.

Thanks for the reply, Andrei, and for the advice about partitioning.

- Jon