MIME-Version: 1.0
References: 
 <CAHgTRfedznOOrDxLhvDCHYhTMDvsbfE4uWCmxBPywcOS-GikXg@mail.gmail.com>
 <a05e0fed-dc66-4873-ac8c-4a8352c4e69d@gmail.com>
 <CAHgTRfdOsgSkHcgB+q-=M-+hb_jyyV1icwTSaCHFbby8C-CUxQ@mail.gmail.com>
 <e79d93fa-44b6-49d6-9761-5cffa59930c6@gmail.com>
 <CAHgTRfeHSNUavGxZTOVbyrZb4FDK6vDm0NYtWWpq_TLAR0AEeQ@mail.gmail.com>
 <CAHgTRff=zhQ5M_S_dXnppHhUK+scEA3fRzYR3ic6uBJK+iMw2Q@mail.gmail.com>
 <20e77723-3c1c-44f5-ba02-442f170dd365@gmail.com>
In-Reply-To: <20e77723-3c1c-44f5-ba02-442f170dd365@gmail.com>
From: James Pang <jamespang886@gmail.com>
Date: Thu, 3 Apr 2025 20:38:49 +0800
Message-ID: 
 <CAHgTRfdkrBDQwz2pMjCZr2L7nFa+REn4WhaG0F-BftqQ9dFn8g@mail.gmail.com>
Subject: Re: partition table optimizer join cost misestimation
To: Andrei Lepikhov <lepihov@gmail.com>,
 pgsql-performance@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000f87d2e0631df0d2d"
Archived-At: 
 <https://www.postgresql.org/message-id/CAHgTRfdkrBDQwz2pMjCZr2L7nFa%2BREn4WhaG0F-BftqQ9dFn8g%40mail.gmail.com>
Precedence: bulk

--000000000000f87d2e0631df0d2d
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

  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@gmail.com> =E6=96=BC 2025=E5=B9=B44=E6=9C=883=E6=
=97=A5=E9=80=B1=E5=9B=9B =E4=B8=8B=E5=8D=884:44=E5=AF=AB=E9=81=93=EF=BC=9A

> On 4/3/25 10:04, James Pang wrote:
> > one more comments, for vacuum/analyze, we enable autovacuum=3Don, 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 happe=
n.
> 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
>

--000000000000f87d2e0631df0d2d
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">=C2=A0 Follow your suggestion to increase statistics_targe=
t (I increase target_statistic to 1000 for=C2=A0 aa.mmm_id and cc.sss_id ,a=
nalyze tablea, tablec again),=C2=A0 optimizer choose the good SQL plan.=C2=
=A0<div><br></div><div>Thanks,</div><div><br></div><div>James=C2=A0</div></=
div><br><div class=3D"gmail_quote gmail_quote_container"><div dir=3D"ltr" c=
lass=3D"gmail_attr">Andrei Lepikhov &lt;<a href=3D"mailto:lepihov@gmail.com=
">lepihov@gmail.com</a>&gt; =E6=96=BC 2025=E5=B9=B44=E6=9C=883=E6=97=A5=E9=
=80=B1=E5=9B=9B =E4=B8=8B=E5=8D=884:44=E5=AF=AB=E9=81=93=EF=BC=9A<br></div>=
<blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-=
left:1px solid rgb(204,204,204);padding-left:1ex">On 4/3/25 10:04, James Pa=
ng wrote:<br>
&gt; one more comments, for vacuum/analyze, we enable autovacuum=3Don, that=
 may <br>
&gt; sometimes automatically analyze part of partition table directly.<br>
I see some incoherence in data provided. The ranges of joining columns <br>
intersects only partially:<br>
<br>
cc.sss_id: 5 100 001 101 - 7 999 999 601<br>
aa.mmm_id: 2 005 242 651 - 5 726 786 022<br>
<br>
So, the intersection range 5100001101 - 5726786022 - is about 10% of the <b=
r>
whole range.<br>
But I don&#39;t see it in the column statistics you provided. And Postgres =
<br>
may do the same.<br>
So, at first, I do recommend increasing default_statistics_target or <br>
just statistics_target on partitioned tables only. For such big tables I <b=
r>
usually set it at least to the 2500.<br>
Also, don&#39;t trust in autovacuum on partitioned table - to make an <br>
analyse it needs to lock each partition which is highly unlikely to happen.=
<br>
So, increase stat target, make ANALYZE tablea, tablec and let me know <br>
what will happen. May be after the analyse statistics will be more <br>
consistent.<br>
<br>
-- <br>
regards, Andrei Lepikhov<br>
</blockquote></div>

--000000000000f87d2e0631df0d2d--