Subject: ANALYZE ONLY

From: Michael Harris <[email-masked]>
Date: Tue, 20 Aug 2024 15:52:12 +1000

Hello Postgres Hackers

An application that I am developing uses Postgresql, and includes a fairly large
number of partitioned tables which are used to store time series data.

The tables are partitioned by time, and typically there is only one partition
at a time - the current partition - that is actually being updated.
Older partitions
are available for query and eventually dropped.

As per the documentation, partitioned tables are not analyzed by the autovacuum
workers, although their partitions are. Statistics are needed on the partitioned
table level for at least some query planning activities.

The problem is that giving an ANALYZE command targeting a partitioned table
causes it to update statistics for the partitioned table AND all the individual
partitions. There is currently no option to prevent it from including the
partitions.

This is wasteful for our application: for one thing the autovacuum
has already analyzed the individual partitions; for another most of
the partitions
will have had no changes, so they don't need to be analyzed repeatedly.

I took some measurements when running ANALYZE on one of our tables. It
took approx
4 minutes to analyze the partitioned table, then 29 minutes to analyze the
partitions. We have hundreds of these tables, so the cost is very significant.

For my use case at least it would be fantastic if we could add an ONLY option
to ANALYZE, which would cause it to analyze the named table only and not
descend into the partitions.

I took a look at the source and it looks doable, but before launching into it
I thought I would ask a few questions here.

  1. Would such a feature be welcomed? Are there any traps I might not
have thought of?

  2. The existing ANALYZE command has the following structure:

     ANALYZE [ ( option [, ...] ) ] [ table_and_columns [, ...] ]

     It would be easiest to add ONLY as another option, but that
doesn't look quite
     right to me - surely the ONLY should be attached to the table name?
     An alternative would be:

     ANALYZE [ ( option [, ...] ) ] [ONLY] [ table_and_columns [, ...] ]

Any feedback or advice would be great.

Regards
Mike.

---

From: Jelte Fennema-Nio <[email-masked]>
Date: Tue, 20 Aug 2024 09:42:43 +0200

>   1. Would such a feature be welcomed? Are there any traps I might not

I think this sounds like a reasonable feature.

> Any feedback or advice would be great.

Personally I'd prefer a new option to be added. But I agree ONLY isn't
a good name then. Maybe something like SKIP_PARTITIONS.

---

From: Ilia Evdokimov <[email-masked]>
Date: Tue, 20 Aug 2024 14:25:32 +0300

On 20.8.24 10:42, Jelte Fennema-Nio wrote:

> On Tue, 20 Aug 2024 at 07:52, Michael Harris<[email-masked]>  wrote:

Hi everyone,

Your proposal is indeed interesting, but I have a question: can't your 
issue be resolved by properly configuring |autovacuum| instead of 
developing a new feature for |ANALYZE|?

 From my perspective, |ANALYZE| is intended to forcibly gather 
statistics from all partitioned tables. If the goal is to ensure that 
statistics are updated at the right moment, adjusting the 
|autovacuum_analyze_threshold| and |autovacuum_analyze_scale_factor| 
parameters might be the solution.

-- 
Regards,
Ilia Evdokimov,
Tantor Labs LCC.

---

From: David Rowley <[email-masked]>
Date: Tue, 20 Aug 2024 23:49:41 +1200

On Tue, 20 Aug 2024 at 23:25, Ilia Evdokimov
<[email-masked]> wrote:
> Your proposal is indeed interesting, but I have a question: can't your issue be resolved by properly configuring autovacuum instead of developing a new feature for ANALYZE?

Basically, no. There's a "tip" in [1] which provides information on
the limitation, namely:

"The autovacuum daemon does not issue ANALYZE commands for partitioned
tables. Inheritance parents will only be analyzed if the parent itself
is changed - changes to child tables do not trigger autoanalyze on the
parent table. If your queries require statistics on parent tables for
proper planning, it is necessary to periodically run a manual ANALYZE
on those tables to keep the statistics up to date."

There is also some discussion about removing the limitation in [2].
While I agree that it would be nice to have autovacuum handle this,
it's not clear how exactly it would work. Additionally, if we had
that, it would still be useful if the ANALYZE command could be
instructed to just gather statistics for the partitioned table only.

David

[1] https://www.postgresql.org/docs/devel/routine-vacuuming.html#VACUUM-FOR-STATISTICS
[2] https://www.postgresql.org/message-id/flat/CAKkQ508_PwVgwJyBY%3D0Lmkz90j8CmWNPUxgHvCUwGhMrouz6UA%40mail.gmail.com

---

From: Melih Mutlu <[email-masked]>
Date: Tue, 20 Aug 2024 19:26:48 +0300

Hi Michael,

Thanks for starting this thread. I've also spent a bit time on this after
reading your first thread on this issue [1]

Michael Harris <[email-masked]>, 20 Ağu 2024 Sal, 08:52 tarihinde şunu

> will have had no changes, so they don't need to be analyzed repeatedly.

I agree that it's a waste to analyze partitions when they're already
analyzed by autovacuum. It would be nice to have a way to run analyze only
on a partitioned table without its partitions.

> partitions. We have hundreds of these tables, so the cost is very

I quickly tweaked the code a bit to exclude partitions when a partitioned
table is being analyzed. I can confirm that there is a significant gain
even on a simple case like a partitioned table with 10 partitions and 1M
rows in each partition.

>      right to me - surely the ONLY should be attached to the table name
>      An alternative would be:
>

I feel closer to adding this as an option instead of a new keyword in
ANALYZE grammar. To me, it would be easier to have this option and then
give the names of partitioned tables as opposed to typing ONLY before each
partition table.
But we should think of another name as ONLY is used differently (attached
to the table name as you mentioned) in other contexts.

I've been also thinking about how this new option should affect inheritance
tables. Should it have just no impact on them or only analyze the parent
table without taking child tables into account? There are two records for
an inheritance parent table in pg_statistic, one row for only the parent
table and a second row including children. We might only analyze the parent
table if this new "ONLY" option is specified. I'm not sure if that would be
something users would need or not, but I think this option should behave
similarly for both partitioned tables and inheritance tables.

If we decide to go with only partition tables and not care about
inheritance, then naming this option to SKIP_PARTITIONS as Jelte suggested
sounds fine. But that name wouldn't work if this option will affect
inheritance tables.

Thanks,
-- 
Melih Mutlu
Microsoft