public inbox for [email protected]
help / color / mirror / Atom feedRe: Dealing with SeqScans when Time-based Partitions Cut Over
2+ messages / 2 participants
[nested] [flat]
* Re: Dealing with SeqScans when Time-based Partitions Cut Over
@ 2025-12-19 17:42 Laurenz Albe <[email protected]>
2025-12-21 15:10 ` Re: Dealing with SeqScans when Time-based Partitions Cut Over Greg Sabino Mullane <[email protected]>
0 siblings, 1 reply; 2+ messages in thread
From: Laurenz Albe @ 2025-12-19 17:42 UTC (permalink / raw)
To: Greg Sabino Mullane <[email protected]>; Matthew Planchard <[email protected]>; +Cc: pgsql-general
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Dealing with SeqScans when Time-based Partitions Cut Over
2025-12-19 17:42 Re: Dealing with SeqScans when Time-based Partitions Cut Over Laurenz Albe <[email protected]>
@ 2025-12-21 15:10 ` Greg Sabino Mullane <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: Greg Sabino Mullane @ 2025-12-21 15:10 UTC (permalink / raw)
To: Laurenz Albe <[email protected]>; +Cc: Matthew Planchard <[email protected]>; pgsql-general
On Fri, Dec 19, 2025 at 12:42 PM Laurenz Albe <[email protected]>
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2025-12-21 15:10 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-12-19 17:42 Re: Dealing with SeqScans when Time-based Partitions Cut Over Laurenz Albe <[email protected]>
2025-12-21 15:10 ` Greg Sabino Mullane <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox