Обсуждение: analyze-in-stages post upgrade questions

On 6/27/25 06:35, Zechman, Derek S wrote:
> We recently performed an upgrade from pg14 (14.18) to pg16 (16.9) and 
> performed the analyze-in-stages post upgrade.  It has been noticed that 
> some plans changed to use hash joins instead of nested loops.  Further 
> investigation found it was because the parent table of partitioned 
> tables did not have stats.  After running an ANALYZE on the parent 
> tables we got similar plan an execution times as before.
> 
> I have two questions
> 
> 1 - Why does analyze-in-stages not analyze the parent tables?
> 
> 2 – What happens if we do not run analyze-in-stages post upgrade and 
> just run an analyze?

It is spelled out in the docs:

https://www.postgresql.org/docs/current/pgupgrade.html

Emphasis added

"Using vacuumdb --all --analyze-only can efficiently generate such 
statistics, and the use of --jobs can speed it up. Option 
--analyze-in-stages can be used to generate **minimal statistics** 
quickly. If vacuum_cost_delay is set to a non-zero value, this can be 
overridden to speed up statistics generation using PGOPTIONS, e.g., 
PGOPTIONS='-c vacuum_cost_delay=0' vacuumdb ...."

and from here:

https://www.postgresql.org/docs/current/app-vacuumdb.html

"--analyze-in-stages

     Only calculate statistics for use by the optimizer (no vacuum), 
like --analyze-only. Run three stages of analyze; the first stage uses 
the lowest possible statistics target (see default_statistics_target) to 
produce usable statistics faster, and subsequent stages build the full 
statistics.

     This option is only useful to analyze a database that currently has 
no statistics or has wholly incorrect ones, such as if it is newly 
populated from a restored dump or by pg_upgrade. Be aware that running 
with this option in a database with existing statistics may cause the 
query optimizer choices to become transiently worse due to the low 
statistics targets of the early stages.
"

> 
> Thanks,
> 
> Sean
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com

On Fri, 2025-06-27 at 08:31 -0700, Adrian Klaver wrote:
> On 6/27/25 06:35, Zechman, Derek S wrote:
> > We recently performed an upgrade from pg14 (14.18) to pg16 (16.9) and
> > performed the analyze-in-stages post upgrade.  It has been noticed that
> > some plans changed to use hash joins instead of nested loops.  Further
> > investigation found it was because the parent table of partitioned
> > tables did not have stats.  After running an ANALYZE on the parent
> > tables we got similar plan an execution times as before.
> >
> > I have two questions
> >
> > 1 - Why does analyze-in-stages not analyze the parent tables?
> >
> > 2 – What happens if we do not run analyze-in-stages post upgrade and
> > just run an analyze?
>
> It is spelled out in the docs:
>
> https://www.postgresql.org/docs/current/pgupgrade.html
>
> Emphasis added
>
> "Using vacuumdb --all --analyze-only can efficiently generate such
> statistics, and the use of --jobs can speed it up. Option
> --analyze-in-stages can be used to generate **minimal statistics**
> quickly. If vacuum_cost_delay is set to a non-zero value, this can be
> overridden to speed up statistics generation using PGOPTIONS, e.g.,
> PGOPTIONS='-c vacuum_cost_delay=0' vacuumdb ...."
>
> and from here:
>
> https://www.postgresql.org/docs/current/app-vacuumdb.html
>
> "--analyze-in-stages
>
>      Only calculate statistics for use by the optimizer (no vacuum),
> like --analyze-only. Run three stages of analyze; the first stage uses
> the lowest possible statistics target (see default_statistics_target) to
> produce usable statistics faster, and subsequent stages build the full
> statistics.
>
>      This option is only useful to analyze a database that currently has
> no statistics or has wholly incorrect ones, such as if it is newly
> populated from a restored dump or by pg_upgrade. Be aware that running
> with this option in a database with existing statistics may cause the
> query optimizer choices to become transiently worse due to the low
> statistics targets of the early stages.

Well, that wouldn't explain why it doesn't work on partitioned tables.
I am under the impression that it should.

Derek, can cou share the pg_stats entries for the partitioned table?

Yours,
Laurenz Albe

On Sat, 2025-06-28 at 01:23 +0000, Zechman, Derek S wrote:
> > Well, that wouldn't explain why it doesn't work on partitioned tables.
> > I am under the impression that it should.
> >
> > Derek, can cou share the pg_stats entries for the partitioned table?
>
> There are no entries in pg_stats for the parent table until after I manually run an analyze on it – Example below

You are right.  I looked at the code, and "vacuumdb" does not process
partitiond tables, even if --analyze-only is specified.  I find that
surprising, aince the SQL command ANALYZE (without a table name) will
also collect statistics for partitioned tables.

I think that it would be a good idea to change that behavior.
In particular, it makes a lot of sense to collect statistics for
partitioned tables after a "pg_upgrade".

Attached is a patch to make "vacuumdb --analyze-only" consider
partitioned tables as well.

Yours,
Laurenz Albe

On Wed, 2025-07-09 at 11:30 +0000, Zechman, Derek S wrote:
> > > There are no entries in pg_stats for the parent table until after I manually run an analyze on it – Example below
> >
> > You are right.  I looked at the code, and "vacuumdb" does not process
> > partitiond tables, even if --analyze-only is specified.  I find that
> > surprising, aince the SQL command ANALYZE (without a table name) will
> > also collect statistics for partitioned tables.
> >
> > I think that it would be a good idea to change that behavior.
> > In particular, it makes a lot of sense to collect statistics for
> > partitioned tables after a "pg_upgrade".
> >
> > Attached is a patch to make "vacuumdb --analyze-only" consider
> > partitioned tables as well.
>
> Is there a plan to include this patch in future releases/patches of postgres?

I have added the patch to the current commitfest:
https://commitfest.postgresql.org/patch/5871/

So far, it has not got any peer review.  So yes, I'd like to include
the patch, but I cannot make it happen by myself.
Essentially, patches get applied if
a) they get peer review and
b) a committer applies them

If you want this to happen, the best thing you could do would be
to review the patch and see if it works for you, if it does what you
need and so on:
https://wiki.postgresql.org/wiki/Reviewing_a_Patch

Yours,
Laurenz Albe

Hi Laurenz,

I have added the patch to the current commitfest:
https://commitfest.postgresql.org/patch/5871/

Just to let you know that I have added a review through the commitfest app. 

You can see it here: https://www.postgresql.org/message-id/flat/175189219162.2200286.3306593311375985296.pgcf@coridan.postgresql.org

Kind regards,

Mircea Cadariu

On Wed, 2025-07-09 at 17:37 +0100, Mircea Cadariu wrote:
> Just to let you know that I have added a review through the commitfest app. 

Thanks!

The patch is still in state "needs review".
If there is something that I should change, you should set it to
"waiting on author".  If you think that the patch is ready to go
as it is, please set it to "ready for committer".

Yours,
Laurenz Albe

Hi Laurenz,


Got it. I have only one suggestion for the patch. Consider adding a 
corresponding test in src/bin/scripts/t/100_vacuumdb.pl.

Proposal (I used this to check the patch):

$node->safe_psql('postgres',
     "CREATE TABLE parent_table (a INT) PARTITION BY LIST (a);\n"
       . "CREATE TABLE child_table PARTITION OF parent_table FOR VALUES 
IN (1);\n"
       . "INSERT INTO parent_table VALUES (1);\n");
$node->issues_sql_like(
     [
         'vacuumdb', '--analyze-only', 'postgres'
     ],
     qr/statement:\s+ANALYZE\s+public\.parent_table/s,
     '--analyze_only updates statistics for partitioned tables');


Kind regards,

Mircea Cadariu