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.
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:
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.
