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@xxxxxxxxx> 於 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
