Re: partition table optimizer join cost misestimation

Follow your suggestion to increase statistics_target (I increase
target_statistic to 1000 for aa.mmm_id and cc.sss_id ,analyze tablea,
tablec again), optimizer choose the good SQL plan.

Thanks,

James

Andrei Lepikhov <lepihov(at)gmail(dot)com> 於 2025年4月3日週四 下午4:44寫道：

> On 4/3/25 10:04, James Pang wrote:
> > one more comments, for vacuum/analyze, we enable autovacuum=on, that may
> > sometimes automatically analyze part of partition table directly.
> I see some incoherence in data provided. The ranges of joining columns
> intersects only partially:
>
> cc.sss_id: 5 100 001 101 - 7 999 999 601
> aa.mmm_id: 2 005 242 651 - 5 726 786 022
>
> So, the intersection range 5100001101 - 5726786022 - is about 10% of the
> whole range.
> But I don't see it in the column statistics you provided. And Postgres
> may do the same.
> So, at first, I do recommend increasing default_statistics_target or
> just statistics_target on partitioned tables only. For such big tables I
> usually set it at least to the 2500.
> Also, don't trust in autovacuum on partitioned table - to make an
> analyse it needs to lock each partition which is highly unlikely to happen.
> So, increase stat target, make ANALYZE tablea, tablec and let me know
> what will happen. May be after the analyse statistics will be more
> consistent.
>
> --
> regards, Andrei Lepikhov
>