Skip to content

Commit a166d40

Browse files
alvherreTatsuro Yamada
alvherre
and
Tatsuro Yamada
committed
Report progress of ANALYZE commands
This uses the progress reporting infrastructure added by c16dc1a, adding support for ANALYZE. Co-authored-by: Álvaro Herrera <alvherre@alvh.no-ip.org> Co-authored-by: Tatsuro Yamada <tatsuro.yamada.tf@nttcom.co.jp> Reviewed-by: Julien Rouhaud, Robert Haas, Anthony Nowocien, Kyotaro Horiguchi, Vignesh C, Amit Langote
1 parent 16a4a3d commit a166d40

File tree

11 files changed

+311
-8
lines changed

11 files changed

+311
-8
lines changed

‎doc/src/sgml/monitoring.sgml

Lines changed: 185 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -344,6 +344,14 @@ postgres 27093 0.0 0.0 30096 2752 ? Ss 11:34 0:00 postgres: ser
344344
</entry>
345345
</row>
346346

347+
<row>
348+
<entry><structname>pg_stat_progress_analyze</structname><indexterm><primary>pg_stat_progress_analyze</primary></indexterm></entry>
349+
<entry>One row for each backend (including autovacuum worker processes) running
350+
<command>ANALYZE</command>, showing current progress.
351+
See <xref linkend='analyze-progress-reporting'/>.
352+
</entry>
353+
</row>
354+
347355
<row>
348356
<entry><structname>pg_stat_progress_create_index</structname><indexterm><primary>pg_stat_progress_create_index</primary></indexterm></entry>
349357
<entry>One row for each backend running <command>CREATE INDEX</command> or <command>REINDEX</command>, showing
@@ -3505,11 +3513,185 @@ SELECT pg_stat_get_backend_pid(s.backendid) AS pid,
35053513
<para>
35063514
<productname>PostgreSQL</productname> has the ability to report the progress of
35073515
certain commands during command execution. Currently, the only commands
3508-
which support progress reporting are <command>CREATE INDEX</command>,
3509-
<command>VACUUM</command> and
3510-
<command>CLUSTER</command>. This may be expanded in the future.
3516+
which support progress reporting are <command>ANALYZE</command>,
3517+
<command>CLUSTER</command>,
3518+
<command>CREATE INDEX</command>, and <command>VACUUM</command>.
3519+
This may be expanded in the future.
3520+
</para>
3521+
3522+
<sect2 id="analyze-progress-reporting">
3523+
<title>ANALYZE Progress Reporting</title>
3524+
3525+
<para>
3526+
Whenever <command>ANALYZE</command> is running, the
3527+
<structname>pg_stat_progress_analyze</structname> view will contain a
3528+
row for each backend that is currently running that command. The tables
3529+
below describe the information that will be reported and provide
3530+
information about how to interpret it.
35113531
</para>
35123532

3533+
<table id="pg-stat-progress-analyze-view" xreflabel="pg_stat_progress_analyze">
3534+
<title><structname>pg_stat_progress_analyze</structname> View</title>
3535+
<tgroup cols="3">
3536+
<thead>
3537+
<row>
3538+
<entry>Column</entry>
3539+
<entry>Type</entry>
3540+
<entry>Description</entry>
3541+
</row>
3542+
</thead>
3543+
3544+
<tbody>
3545+
<row>
3546+
<entry><structfield>pid</structfield></entry>
3547+
<entry><type>integer</type></entry>
3548+
<entry>Process ID of backend.</entry>
3549+
</row>
3550+
<row>
3551+
<entry><structfield>datid</structfield></entry>
3552+
<entry><type>oid</type></entry>
3553+
<entry>OID of the database to which this backend is connected.</entry>
3554+
</row>
3555+
<row>
3556+
<entry><structfield>datname</structfield></entry>
3557+
<entry><type>name</type></entry>
3558+
<entry>Name of the database to which this backend is connected.</entry>
3559+
</row>
3560+
<row>
3561+
<entry><structfield>relid</structfield></entry>
3562+
<entry><type>oid</type></entry>
3563+
<entry>OID of the table being analyzed.</entry>
3564+
</row>
3565+
<row>
3566+
<entry><structfield>phase</structfield></entry>
3567+
<entry><type>text</type></entry>
3568+
<entry>Current processing phase. See <xref linkend="analyze-phases" />.</entry>
3569+
</row>
3570+
<row>
3571+
<entry><structfield>sample_blks_total</structfield></entry>
3572+
<entry><type>bigint</type></entry>
3573+
<entry>
3574+
Total number of heap blocks that will be sampled.
3575+
</entry>
3576+
</row>
3577+
<row>
3578+
<entry><structfield>sample_blks_scanned</structfield></entry>
3579+
<entry><type>bigint</type></entry>
3580+
<entry>
3581+
Number of heap blocks scanned.
3582+
</entry>
3583+
</row>
3584+
<row>
3585+
<entry><structfield>ext_stats_total</structfield></entry>
3586+
<entry><type>bigint</type></entry>
3587+
<entry>
3588+
Number of extended statistics.
3589+
</entry>
3590+
</row>
3591+
<row>
3592+
<entry><structfield>ext_stats_computed</structfield></entry>
3593+
<entry><type>bigint</type></entry>
3594+
<entry>
3595+
Number of computed extended statistics computed. This counter only advances when
3596+
the phase is <literal>computing extended statistics</literal>.
3597+
</entry>
3598+
</row>
3599+
<row>
3600+
<entry><structfield>child_tables_total</structfield></entry>
3601+
<entry><type>bigint</type></entry>
3602+
<entry>
3603+
Number of child tables.
3604+
</entry>
3605+
</row>
3606+
<row>
3607+
<entry><structfield>child_tables_done</structfield></entry>
3608+
<entry><type>bigint</type></entry>
3609+
<entry>
3610+
Number of child tables scanned. This counter only advances when the phase
3611+
is <literal>acquiring inherited sample rows</literal>.
3612+
</entry>
3613+
</row>
3614+
<row>
3615+
<entry><structfield>current_child_table_relid</structfield></entry>
3616+
<entry><type>oid</type></entry>
3617+
<entry>OID of the child table currently being scanned. This field is only valid when
3618+
the phase is <literal>computing extended statistics</literal>.
3619+
</entry>
3620+
</row>
3621+
</tbody>
3622+
</tgroup>
3623+
</table>
3624+
3625+
<table id="analyze-phases">
3626+
<title>ANALYZE phases</title>
3627+
<tgroup cols="2">
3628+
<thead>
3629+
<row>
3630+
<entry>Phase</entry>
3631+
<entry>Description</entry>
3632+
</row>
3633+
</thead>
3634+
<tbody>
3635+
<row>
3636+
<entry><literal>initializing</literal></entry>
3637+
<entry>
3638+
The command is preparing to begin scanning the heap. This phase is
3639+
expected to be very brief.
3640+
</entry>
3641+
</row>
3642+
<row>
3643+
<entry><literal>acquiring sample rows</literal></entry>
3644+
<entry>
3645+
The command is currently scanning the table given by
3646+
<structfield>current_relid</structfield> to obtain sample rows.
3647+
</entry>
3648+
</row>
3649+
<row>
3650+
<entry><literal>acquiring inherited sample rows</literal></entry>
3651+
<entry>
3652+
The command is currently scanning child tables to obtain sample rows. Columns
3653+
<structfield>child_tables_total</structfield>,
3654+
<structfield>child_tables_done</structfield>, and
3655+
<structfield>current_child_table_relid</structfield> contain the progress
3656+
information for this phase.
3657+
</entry>
3658+
</row>
3659+
<row>
3660+
<entry><literal>computing statistics</literal></entry>
3661+
<entry>
3662+
The command is computing statistics from the samples rows obtained during
3663+
the table scan.
3664+
</entry>
3665+
</row>
3666+
<row>
3667+
<entry><literal>computing extended statistics</literal></entry>
3668+
<entry>
3669+
The command is computing extended statistics from the samples rows obtained
3670+
durring the table scan.
3671+
</entry>
3672+
</row>
3673+
<row>
3674+
<entry><literal>finalizing analyze</literal></entry>
3675+
<entry>
3676+
The command is updating pg_class. When this phase is completed,
3677+
<command>ANALYZE</command> will end.
3678+
</entry>
3679+
</row>
3680+
</tbody>
3681+
</tgroup>
3682+
</table>
3683+
3684+
<note>
3685+
<para>
3686+
Note that when <command>ANALYZE</command> is run on a partitioned table,
3687+
all of its partitions are also recursively analyzed as also mentioned on
3688+
<xref linkend="sql-analyze"/>. In that case, <command>ANALYZE</command>
3689+
progress is reported first for the parent table, whereby its inheritance
3690+
statistics are collected, followed by that for each partition.
3691+
</para>
3692+
</note>
3693+
</sect2>
3694+
35133695
<sect2 id="create-index-progress-reporting">
35143696
<title>CREATE INDEX Progress Reporting</title>
35153697

‎src/backend/catalog/system_views.sql

Lines changed: 21 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -957,6 +957,27 @@ CREATE VIEW pg_stat_bgwriter AS
957957
pg_stat_get_buf_alloc() AS buffers_alloc,
958958
pg_stat_get_bgwriter_stat_reset_time() AS stats_reset;
959959

960+
CREATE VIEW pg_stat_progress_analyze AS
961+
SELECT
962+
S.pid AS pid, S.datid AS datid, D.datname AS datname,
963+
CAST(S.relid AS oid) AS relid,
964+
CASE S.param1 WHEN 0 THEN 'initializing'
965+
WHEN 1 THEN 'acquiring sample rows'
966+
WHEN 2 THEN 'acquiring inherited sample rows'
967+
WHEN 3 THEN 'computing statistics'
968+
WHEN 4 THEN 'computing extended statistics'
969+
WHEN 5 THEN 'finalizing analyze'
970+
END AS phase,
971+
S.param2 AS sample_blks_total,
972+
S.param3 AS sample_blks_scanned,
973+
S.param4 AS ext_stats_total,
974+
S.param5 AS ext_stats_computed,
975+
S.param6 AS child_tables_total,
976+
S.param7 AS child_tables_done,
977+
CAST(S.param8 AS oid) AS current_child_table_relid
978+
FROM pg_stat_get_progress_info('ANALYZE') AS S
979+
LEFT JOIN pg_database D ON S.datid = D.oid;
980+
960981
CREATE VIEW pg_stat_progress_vacuum AS
961982
SELECT
962983
S.pid AS pid, S.datid AS datid, D.datname AS datname,

‎src/backend/commands/analyze.c

Lines changed: 32 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -35,6 +35,7 @@
3535
#include "catalog/pg_namespace.h"
3636
#include "catalog/pg_statistic_ext.h"
3737
#include "commands/dbcommands.h"
38+
#include "commands/progress.h"
3839
#include "commands/tablecmds.h"
3940
#include "commands/vacuum.h"
4041
#include "executor/executor.h"
@@ -251,6 +252,8 @@ analyze_rel(Oid relid, RangeVar *relation,
251252
LWLockAcquire(ProcArrayLock, LW_EXCLUSIVE);
252253
MyPgXact->vacuumFlags |= PROC_IN_ANALYZE;
253254
LWLockRelease(ProcArrayLock);
255+
pgstat_progress_start_command(PROGRESS_COMMAND_ANALYZE,
256+
RelationGetRelid(onerel));
254257

255258
/*
256259
* Do the normal non-recursive ANALYZE. We can skip this for partitioned
@@ -275,6 +278,8 @@ analyze_rel(Oid relid, RangeVar *relation,
275278
*/
276279
relation_close(onerel, NoLock);
277280

281+
pgstat_progress_end_command();
282+
278283
/*
279284
* Reset my PGXACT flag. Note: we need this here, and not in vacuum_rel,
280285
* because the vacuum flag is cleared by the end-of-xact code.
@@ -506,6 +511,9 @@ do_analyze_rel(Relation onerel, VacuumParams *params,
506511
* Acquire the sample rows
507512
*/
508513
rows = (HeapTuple *) palloc(targrows * sizeof(HeapTuple));
514+
pgstat_progress_update_param(PROGRESS_ANALYZE_PHASE,
515+
inh ? PROGRESS_ANALYZE_PHASE_ACQUIRE_SAMPLE_ROWS_INH :
516+
PROGRESS_ANALYZE_PHASE_ACQUIRE_SAMPLE_ROWS);
509517
if (inh)
510518
numrows = acquire_inherited_sample_rows(onerel, elevel,
511519
rows, targrows,
@@ -526,6 +534,9 @@ do_analyze_rel(Relation onerel, VacuumParams *params,
526534
MemoryContext col_context,
527535
old_context;
528536

537+
pgstat_progress_update_param(PROGRESS_ANALYZE_PHASE,
538+
PROGRESS_ANALYZE_PHASE_COMPUTE_STATS);
539+
529540
col_context = AllocSetContextCreate(anl_context,
530541
"Analyze Column",
531542
ALLOCSET_DEFAULT_SIZES);
@@ -596,6 +607,9 @@ do_analyze_rel(Relation onerel, VacuumParams *params,
596607
attr_cnt, vacattrstats);
597608
}
598609

610+
pgstat_progress_update_param(PROGRESS_ANALYZE_PHASE,
611+
PROGRESS_ANALYZE_PHASE_FINALIZE_ANALYZE);
612+
599613
/*
600614
* Update pages/tuples stats in pg_class ... but not if we're doing
601615
* inherited stats.
@@ -1034,6 +1048,8 @@ acquire_sample_rows(Relation onerel, int elevel,
10341048
ReservoirStateData rstate;
10351049
TupleTableSlot *slot;
10361050
TableScanDesc scan;
1051+
BlockNumber nblocks;
1052+
BlockNumber blksdone = 0;
10371053

10381054
Assert(targrows > 0);
10391055

@@ -1043,7 +1059,12 @@ acquire_sample_rows(Relation onerel, int elevel,
10431059
OldestXmin = GetOldestXmin(onerel, PROCARRAY_FLAGS_VACUUM);
10441060

10451061
/* Prepare for sampling block numbers */
1046-
BlockSampler_Init(&bs, totalblocks, targrows, random());
1062+
nblocks = BlockSampler_Init(&bs, totalblocks, targrows, random());
1063+
1064+
/* Report sampling block numbers */
1065+
pgstat_progress_update_param(PROGRESS_ANALYZE_BLOCKS_TOTAL,
1066+
nblocks);
1067+
10471068
/* Prepare for sampling rows */
10481069
reservoir_init_selection_state(&rstate, targrows);
10491070

@@ -1104,6 +1125,9 @@ acquire_sample_rows(Relation onerel, int elevel,
11041125

11051126
samplerows += 1;
11061127
}
1128+
1129+
pgstat_progress_update_param(PROGRESS_ANALYZE_BLOCKS_DONE,
1130+
++blksdone);
11071131
}
11081132

11091133
ExecDropSingleTupleTableSlot(slot);
@@ -1332,6 +1356,8 @@ acquire_inherited_sample_rows(Relation onerel, int elevel,
13321356
* rels have radically different free-space percentages, but it's not
13331357
* clear that it's worth working harder.)
13341358
*/
1359+
pgstat_progress_update_param(PROGRESS_ANALYZE_CHILD_TABLES_TOTAL,
1360+
nrels);
13351361
numrows = 0;
13361362
*totalrows = 0;
13371363
*totaldeadrows = 0;
@@ -1341,6 +1367,9 @@ acquire_inherited_sample_rows(Relation onerel, int elevel,
13411367
AcquireSampleRowsFunc acquirefunc = acquirefuncs[i];
13421368
double childblocks = relblocks[i];
13431369

1370+
pgstat_progress_update_param(PROGRESS_ANALYZE_CURRENT_CHILD_TABLE_RELID,
1371+
RelationGetRelid(childrel));
1372+
13441373
if (childblocks > 0)
13451374
{
13461375
int childtargrows;
@@ -1396,6 +1425,8 @@ acquire_inherited_sample_rows(Relation onerel, int elevel,
13961425
* pointers to their TOAST tables in the sampled rows.
13971426
*/
13981427
table_close(childrel, NoLock);
1428+
pgstat_progress_update_param(PROGRESS_ANALYZE_CHILD_TABLES_DONE,
1429+
i + 1);
13991430
}
14001431

14011432
return numrows;

0 commit comments

Comments
 (0)