Re: ANALYZE ONLY - Mailing list pgsql-hackers

From jian he
Subject Re: ANALYZE ONLY
Date
Msg-id CACJufxFeHWD_NKAchTErSu_ZxORv8YdsoeVeOdiMQ+rkdz-BTQ@mail.gmail.com
Whole thread Raw
In response to Re: ANALYZE ONLY  (David Rowley <dgrowleyml@gmail.com>)
List pgsql-hackers
On Mon, Sep 23, 2024 at 12:46 PM David Rowley <dgrowleyml@gmail.com> wrote:
>
> On Mon, 23 Sept 2024 at 15:29, jian he <jian.universality@gmail.com> wrote:
> > Given the above context, I am still confused with this sentence in
> > sql-analyze.html.
> > "If ONLY is specified before the table name, only that table is analyzed."
> >
> > like in the above sql example, only_inh_parent's child is also being analyzed.
>
> I guess it depends what you define "analyzed" to mean. In this
> context, it means gathering statistics specifically for a certain
> table.
>
> Would it be more clear if "only that table is analyzed." was changed
> to "then statistics are only gathered specifically for that table."?
>

looking at expand_vacuum_rel, analyze_rel.
if we
---------
if (onerel->rd_rel->relhassubclass)
do_analyze_rel(onerel, params, va_cols, acquirefunc, relpages,
  true, in_outer_xact, elevel);

change to

    if (onerel->rd_rel->relhassubclass && ((!relation ||
relation->inh) || onerel->rd_rel->relkind ==
RELKIND_PARTITIONED_TABLE))
        do_analyze_rel(onerel, params, va_cols, acquirefunc, relpages,
                       true, in_outer_xact, elevel);


then the inheritance table will behave the way the doc says.

for example:
drop table if exists only_inh_parent,only_inh_child;
CREATE TABLE only_inh_parent (a int , b INT) with (autovacuum_enabled = false);
CREATE TABLE only_inh_child () INHERITS (only_inh_parent) with
(autovacuum_enabled = false);
INSERT INTO only_inh_child(a,b) select g % 80, (g + 1) % 200 from
generate_series(1,1000) g;
ANALYZE ONLY only_inh_parent;
select stadistinct,starelid::regclass,staattnum, stainherit
from  pg_statistic
where starelid = ANY ('{only_inh_parent, only_inh_child}'::regclass[]);

will return zero rows, since the physical table only_inh_parent has no storage.




sql-analyze.html
For partitioned tables, ANALYZE gathers statistics by sampling rows
from all partitions. Each leaf partition is also recursively analyzed
and the statistics updated. This recursive part may be disabled by
using the ONLY keyword, otherwise, leaf partitions are analyzed only
once, even with multi-level partitioning. No statistics are collected
for only the parent table (without data from its partitions), because
with partitioning it's guaranteed to be empty.

allow me to ask anenglish language question.
here "otherwise" means  specify ONLY or not?
As far as i understand.
if you specify ONLY, postgres will only do "For partitioned tables,
ANALYZE gathers statistics by sampling rows from all partitions"
if you not specify ONLY, postgres will do  "For partitioned tables,
ANALYZE gathers statistics by sampling rows from all partitions *AND*
also recursively analyze each leaf partition"

Is my understanding correct?


I think there is a whitespace error in "ANALYZE ONLY vacparted(a,b); "
in vacuum.out.



pgsql-hackers by date:

Previous
From: Sandeep Thakkar
Date:
Subject: Re: PostgreSQL 17 RC1 & GA dates
Next
From: Tatsuo Ishii
Date:
Subject: Re: Add memory/disk usage for WindowAgg nodes in EXPLAIN