Re: Help with row estimate problem

On 29/7/2024 22:51, Jon Zeppieri wrote:
> Of course, I'd prefer not to have to materialize this relation
> explicitly. This particular query, for this particular user, benefits
> from it, but similar queries or queries for different users may not.
>
> I think the root of the problem is that population size (i.e., the
> number of members in a given population) has a high variance, and then
> planner is basing its estimates on the average population size (and
> maybe the average number of populations to which a user has access?),
> which is not especially useful. Is there anything I can do about this?
> Would any extended statistics be useful here?
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.

--
regards, Andrei Lepikhov