Re: ANALYZE ONLY - Mailing list pgsql-hackers

On Mon, 23 Sept 2024 at 23:23, jian he <jian.universality@gmail.com> wrote:
> looks fine. but maybe we can add the following information
> "if The <literal>ONLY</literal> is specified, the second set of
> statistics won't include each children individual statistics"
> I think that's the main difference between specifying ONLY or not?

Ok, I think you're not understanding this yet and I'm not sure what I
can make more clear in the documents.

Let me explain... For inheritance parent tables, ANALYZE ONLY will
gather inheritance and non-inheritance statistics for ONLY the parent.

Here's an example of that:

drop table if exists parent,child;
create table parent(a int);
create table child () inherits (parent);
insert into parent values(1);
insert into child values(1);

analyze ONLY parent;
select starelid::regclass,stainherit,stadistinct from pg_statistic
where starelid in ('parent'::regclass,'child'::regclass);
 starelid | stainherit | stadistinct
----------+------------+-------------
 parent   | f          |          -1  <- this is the distinct estimate
for SELECT * FROM ONLY parent;
 parent   | t          |        -0.5 <- this is the distinct estimate
for SELECT * FROM parent;
(2 rows)

For the stainherit==false stats, only 1 row is sampled here as that's
the only row directly located in the "parent" table.
For the stainherit==true stats, 2 rows are sampled, both of them have
"a" == 1. The stadistinct reflects that fact.

Note there have been no statistics recorded for "child". However,
analyze did sample rows in that table as part of gathering sample rows
for "parent" for the stainherit==true row.

Now let's try again without ONLY.

analyze parent;

select starelid::regclass,stainherit,stadistinct from pg_statistic
where starelid in ('parent'::regclass,'child'::regclass);
 starelid | stainherit | stadistinct
----------+------------+-------------
 parent   | f          |          -1
 parent   | t          |        -0.5
 child    | f          |          -1
(3 rows)

All of the above rows were re-calculated with the "analyze parent"
command, the first two rows have the same values as nothing changed in
the table, however, there are now statistics stored for the "child"
table.

> catalog-pg-statistic.html second paragraph seems very clear to me.
> Maybe we can link it somehow

I don't know what "link it" means in this context.

David