MIME-Version: 1.0
References: <87bjjv1v96.fsf@gmail.com>
In-Reply-To: <87bjjv1v96.fsf@gmail.com>
From: Greg Sabino Mullane <htamfids@gmail.com>
Date: Fri, 19 Dec 2025 09:49:48 -0500
Message-ID: <CAKAnmmL3_WPtWRrCy1t3zJOcDConYA__so4FZj_Bzzza-inJjw@mail.gmail.com>
Subject: Re: Dealing with SeqScans when Time-based Partitions Cut Over
To: Matthew Planchard <msplanchard@gmail.com>
Cc: pgsql-general@postgresql.org
Content-Type: multipart/alternative; boundary="00000000000043b58406464f3263"
Archived-At: <https://www.postgresql.org/message-id/CAKAnmmL3_WPtWRrCy1t3zJOcDConYA__so4FZj_Bzzza-inJjw%40mail.gmail.com>
Precedence: bulk

--00000000000043b58406464f3263
Content-Type: text/plain; charset="UTF-8"

If this is still an issue after you check David's theory about premature
analyzing, another approach is to pre-populate and pre-analyze future
tables. Something like this:

* disable autovac on the future table
* detach the table from the main partition
* insert a few hundred thousand rows into it, then run analyze on it
* can pull rows from a current table, or just use random data on a key
column- whatever is enough to generate "good" stats
* delete the rows - the stats will remain
* reattach the table
* enable autovac if you like; I would not


Cheers,
Greg

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

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

<div dir=3D"ltr"><div dir=3D"ltr"><div>If this is still an issue after you =
check David&#39;s theory about premature analyzing, another approach is to =
pre-populate and pre-analyze future tables. Something like this:</div><div>=
<br></div><div dir=3D"ltr"><div>* disable autovac on the future table</div>=
<div>* detach the table from the main partition</div><div>* insert a few hu=
ndred thousand rows into it, then run analyze on it</div><div>* can pull ro=
ws from a current table, or just use random data on a key column- whatever =
is enough to generate &quot;good&quot; stats</div><div>* delete the rows - =
the stats will remain</div><div>* reattach the table</div><div>* enable aut=
ovac if you like; I would not</div><div><br></div><div><br></div></div><div=
 dir=3D"ltr" class=3D"gmail_signature"><div dir=3D"ltr"><div>Cheers,</div><=
div>Greg</div><div><br></div><div>--</div><div>Crunchy Data - <a href=3D"ht=
tps://www.crunchydata.com" target=3D"_blank">https://www.crunchydata.com</a=
></div><div>Enterprise Postgres Software Products &amp; Tech Support</div><=
div><br></div></div></div></div>
</div>

--00000000000043b58406464f3263--