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>
In-Reply-To: <CADofcAUMS2waLhJDdiz3Zv2RRPT8N_+cq0ZOtf9LjV51F0ht2Q@mail.gmail.com>
From: David Rowley <dgrowleyml@gmail.com>
Date: Wed, 7 Aug 2024 20:09:39 +1200
Message-ID: <CAApHDvosdmXz-=nyUF+ceUgTrLy=FcCct47vhMtTHDsRpBtaJA@mail.gmail.com>
Subject: Re: ANALYZE on partitioned tables vs on individual partitions
To: Michael Harris <harmic@gmail.com>
Cc: pgsql-general@lists.postgresql.org, Christophe Pettus <xof@thebuild.com>
Content-Type: text/plain; charset="UTF-8"
Archived-At: <https://www.postgresql.org/message-id/CAApHDvosdmXz-%3DnyUF%2BceUgTrLy%3DFcCct47vhMtTHDsRpBtaJA%40mail.gmail.com>
Precedence: bulk

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