Subject: Re: Dealing with SeqScans when Time-based Partitions Cut Over

From: Laurenz Albe <[email-masked]>
Date: Fri, 19 Dec 2025 18:42:34 +0100

On Fri, 2025-12-19 at 09:49 -0500, Greg Sabino Mullane wrote:
> 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

I doubt that that is good advice.  For one, wrong statistics are not necessarily
better than no statistics.  Disabling autovacuum is dangerous - and re-enabling
it would trigger another autovacuum, which would undo your efforts.
*Not* re-enabling autovacuum is not an option, unless you schedule explicit
VACUUM runs on the partition.

Yours,
Laurenz Albe

---

From: Greg Sabino Mullane <[email-masked]>
Date: Sun, 21 Dec 2025 10:10:22 -0500

On Fri, Dec 19, 2025 at 12:42 PM Laurenz Albe <[email-masked]>
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