MIME-Version: 1.0
References: <87bjjv1v96.fsf@gmail.com> <CAKAnmmL3_WPtWRrCy1t3zJOcDConYA__so4FZj_Bzzza-inJjw@mail.gmail.com>
 <64ccdcb7b15a17cbf610854e51cd8235c246cca8.camel@cybertec.at>
In-Reply-To: <64ccdcb7b15a17cbf610854e51cd8235c246cca8.camel@cybertec.at>
From: Greg Sabino Mullane <htamfids@gmail.com>
Date: Sun, 21 Dec 2025 10:10:22 -0500
Message-ID: <CAKAnmmKo5zsJu9FQSMtu0eJrFSGcTk_0gsRUshFpQgv8br8GVg@mail.gmail.com>
Subject: Re: Dealing with SeqScans when Time-based Partitions Cut Over
To: Laurenz Albe <laurenz.albe@cybertec.at>
Cc: Matthew Planchard <msplanchard@gmail.com>, pgsql-general@postgresql.org
Content-Type: multipart/alternative; boundary="0000000000006a3713064677b781"
Archived-At: <https://www.postgresql.org/message-id/CAKAnmmKo5zsJu9FQSMtu0eJrFSGcTk_0gsRUshFpQgv8br8GVg%40mail.gmail.com>
Precedence: bulk

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

On Fri, Dec 19, 2025 at 12:42=E2=80=AFPM Laurenz Albe <laurenz.albe@cyberte=
c.at>
wrote:

> I doubt that that is good advice.  For one, wrong statistics are not
> necessarily better than no statistics.


But clearly in this case, "wrong" statistics are better than none. Frankly,
any plan is better than the one they are getting (full seq scans). Also,
with a time-based highly active partition, we can be very sure that the
stats for one partition are going to be similar to another, so doing some
"pre-estimation" seems fine with me. At the end of the day, this is a query
planner issue, and the goal is to prevent that. The same data will be
returned.


>   Disabling autovacuum is dangerous - and re-enabling it would trigger
> another autovacuum, which would undo your efforts.
>

No, re-enabling it will put it back in the pool of tables that may get
vacuumed/analyzed once thresholds are reached. It will not immediately
trigger another autovac.


> *Not* re-enabling autovacuum is not an option, unless you schedule
> explicit VACUUM runs on the partition.
>

*shrug* Well, for an hourly partition, if the stats you set at the top of
the hour are the same as the stats when you analyze at the end of the
hour, and give the same plan, analyzing doesn't really matter. If this were
a normal table, I might advocate cron-based or insert-based vacuum runs for
all the other benefits vacuum provides, but with time-partitioning, the app
usually only cares about very recent partitions, and there is little-to-no
updating going on.

Cheers,
Greg

--
Crunchy Data - https://www.crunchydata.com
Enterprise Postgres Software Products & Tech Support

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

<div dir=3D"ltr"><div dir=3D"ltr">On Fri, Dec 19, 2025 at 12:42=E2=80=AFPM =
Laurenz Albe &lt;<a href=3D"mailto:laurenz.albe@cybertec.at">laurenz.albe@c=
ybertec.at</a>&gt; wrote:</div><div class=3D"gmail_quote gmail_quote_contai=
ner"><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;bo=
rder-left:1px solid rgb(204,204,204);padding-left:1ex">I doubt that that is=
 good advice.=C2=A0 For one, wrong statistics are not necessarily better th=
an no statistics.</blockquote><div><br></div><div>But clearly in this case,=
 &quot;wrong&quot; statistics are better than none. Frankly, any plan is be=
tter than the one they are getting (full seq scans). Also, with a time-base=
d highly active partition, we can be very sure that the stats for one parti=
tion are going to be similar to another, so doing some &quot;pre-estimation=
&quot; seems fine with me. At the end of the day, this is a query planner i=
ssue, and the goal is to prevent that. The same data will be returned.</div=
><div>=C2=A0</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">=C2=A0 =
Disabling autovacuum is dangerous - and re-enabling it would trigger anothe=
r autovacuum, which would undo your efforts.<br></blockquote><div><br></div=
><div>No, re-enabling it will put it back in the pool of tables that may ge=
t vacuumed/analyzed once thresholds are reached. It will not immediately tr=
igger another autovac.</div><div>=C2=A0</div><blockquote class=3D"gmail_quo=
te" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204=
);padding-left:1ex">
*Not* re-enabling autovacuum is not an option, unless you schedule explicit=
 VACUUM runs on the partition.<br></blockquote><div><br></div><div>*shrug* =
Well, for an hourly partition, if the stats you set at the top of the hour =
are the same as the stats when you analyze at the end of the hour,=C2=A0and=
 give the=C2=A0same plan, analyzing doesn&#39;t really matter. If this were=
 a normal table, I might advocate cron-based or insert-based vacuum=C2=A0ru=
ns for all the other benefits vacuum provides, but with time-partitioning, =
the app usually only cares about very recent partitions, and there is littl=
e-to-no updating going on.</div><div><br></div><div>Cheers,</div></div><div=
 dir=3D"ltr" class=3D"gmail_signature"><div dir=3D"ltr"><div>Greg</div><div=
><br></div><div>--</div><div>Crunchy Data - <a href=3D"https://www.crunchyd=
ata.com" target=3D"_blank">https://www.crunchydata.com</a></div><div>Enterp=
rise Postgres Software Products &amp; Tech Support</div><div><br></div></di=
v></div></div>

--0000000000006a3713064677b781--