MIME-Version: 1.0
References: <CADofcAXVbD0yGp_EaC9chmzsOoSai3jcfBCnyva3j0RRdRvMVA@mail.gmail.com>
 <D8A15A79-9FCF-4855-AB1E-BD6088065CCC@thebuild.com> <CAApHDvrjTL3qo70C1snX7yLQwPpWoANL6sigcD6OTWqy7Y3+Nw@mail.gmail.com>
 <CADofcAUMS2waLhJDdiz3Zv2RRPT8N_+cq0ZOtf9LjV51F0ht2Q@mail.gmail.com> <CAApHDvosdmXz-=nyUF+ceUgTrLy=FcCct47vhMtTHDsRpBtaJA@mail.gmail.com>
In-Reply-To: <CAApHDvosdmXz-=nyUF+ceUgTrLy=FcCct47vhMtTHDsRpBtaJA@mail.gmail.com>
From: Michael Harris <harmic@gmail.com>
Date: Thu, 8 Aug 2024 11:02:45 +1000
Message-ID: <CADofcAXHy7WRK24-KqZz0E2ZUHLQqbXmngt-+Azbq_cBc+QkTw@mail.gmail.com>
Subject: Re: ANALYZE on partitioned tables vs on individual partitions
To: David Rowley <dgrowleyml@gmail.com>
Cc: pgsql-general@lists.postgresql.org
Content-Type: text/plain; charset="UTF-8"
Archived-At: <https://www.postgresql.org/message-id/CADofcAXHy7WRK24-KqZz0E2ZUHLQqbXmngt-%2BAzbq_cBc%2BQkTw%40mail.gmail.com>
Precedence: bulk

> You could probably do some sampling of the pg_stat_progress_analyze
> view to figure out what's taking the most time.

I did another run, sampling the pg_stat_progress_analyze every 30s.

For the first 4 minutes it was working on the partitioned table.

After that it began analyzing all the partitions, which took approx 29 minutes.

I think you are correct - an ONLY option for ANALYZE would be a huge
benefit. In my use case, the autovacuum processes are keeping the partitions
analyzed so there would seem to be little benefit to including them in
the manual
table level ANALYZE.

Regards
Mike

On Wed, 7 Aug 2024 at 18:09, David Rowley <dgrowleyml@gmail.com> wrote:
>
> On Wed, 7 Aug 2024 at 19:20, Michael Harris <harmic@gmail.com> wrote:
> > I found that running an ANALYZE specifying only those 4 columns only took
> > 5 minutes, compared to the 30 minutes for the whole table.
> >
> > That was a bit of a surprise as I imagined actually reading the table would take
> > most of the time and would be the same regardless of the number of columns
> > being analyzed, but I guess that is wrong.
>
> ANALYZE does do sampling of the data in the table.  It would only read
> all of the rows for fairly small tables.  The docs in [1] mention
> this:
>
> "For large tables, ANALYZE takes a random sample of the table
> contents, rather than examining every row. This allows even very large
> tables to be analyzed in a small amount of time."
>
> I think the reason it's taking so long is not because of it performing
> ANALYZE on the partitioned table which results in gathering statistics
> for the partitioned table which means proportionately (based on the
> size of the partition) sampling rows from each partition, it's more
> likely due to the fact that each partition is also analysed and the
> statistics for each of those is updated. There is no "ANALYZE ONLY"
> command similar to "FROM ONLY" in SELECT queries.
>
> You could probably do some sampling of the pg_stat_progress_analyze
> view to figure out what's taking the most time. If you find that the
> majority of the time is spent analysing the partitions and not the
> partitioned table then maybe we should expand ANALYZE to add the ONLY
> option...
>
> David
>
> [1] https://www.postgresql.org/docs/current/sql-analyze.html
> [2] https://www.postgresql.org/docs/current/progress-reporting.html