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 <lepi...@gmail.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 >
