[DOCS] Stats views and functions not in order?

On Tue, Aug 2, 2022 at 9:40 AM Peter Smith <smithpb2250@gmail.com> wrote:

Hi Hackers.

I noticed that there are quite a few items in Chapter 28.2 "The
Cumulative Statistics System" [1] which have no obvious order.

e.g.

- The views (28.2.3 -> 28.2.23) don't seem to be in any order that I
could work out. Why not alphabetical?

- [2] Table 2.1 "Dynamic Statistics View" views are not in alphabetical order?

- [2] Table 2.2 "Collected Statistics View" views are not in alphabetical order?

- [3] Table 28.34 "Additional Statistics Functions" the
'pg_stat_clear_snapshot' is the only one not in order?

- [3] Table 28.35 "Per-Backend Statistics Functions" the
'pg_stat_get_backend_idset' is the only one not in order?

~~

So it doesn't seem as readable as it could be. If other people think
the same, I can write a patch for it.

I received no feedback when I reported this about a month ago, so I
went ahead and made patches to fix the problem anyway.

PSA. Note that no content was harmed in the making of these patches -
I only moved things around to be ordered.

IMO these docs look better now.

Thoughts?

------
Kind Regards,
Peter Smith.
Fujitsu Australia

A rebase was needed.

PSA v2*.

------
Kind Regards,
Peter Smith.
Fujitsu Australia

A rebase was needed.

PSA v3*.

------
Kind Regards,
Peter Smith.
Fujitsu Australia

Sorry, I forgot the attachments in the previous post. PSA.

Peter Smith <smithpb2250@gmail.com> writes:

Sorry, I forgot the attachments in the previous post. PSA.

I spent a bit of time looking at this. I agree that a lot of the
current ordering choices here look like they were made with the
advice of a dartboard, and there's a number of things that are
pretty blatantly just sloppy merging (like the out-of-order
wait-event items). However, I'm not a big fan of "alphabetical
order at all costs", because that frequently leads to ordering
decisions that are not a lot better than random from a semantic
standpoint. For example, I resist the idea that it's sensible
to put pg_stat_all_indexes before pg_stat_all_tables.
I'm unconvinced that putting pg_stat_sys_tables and
pg_stat_user_tables far away from pg_stat_all_tables is great,
either.

So ... how do we proceed?

One thing I'm unhappy about that you didn't address is that
the subsection ordering in "28.4. Progress Reporting" could
hardly have been invented even with a dartboard. Perhaps it
reflects development order, but that's a poor excuse.
I'd be inclined to alphabetize by SQL command name, but maybe
leave Base Backup to the end since it's not a SQL command.

regards, tom lane

On Mon, Nov 7, 2022 at 5:50 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

Peter Smith <smithpb2250@gmail.com> writes:

Sorry, I forgot the attachments in the previous post. PSA.

I spent a bit of time looking at this. I agree that a lot of the
current ordering choices here look like they were made with the
advice of a dartboard, and there's a number of things that are
pretty blatantly just sloppy merging (like the out-of-order
wait-event items). However, I'm not a big fan of "alphabetical
order at all costs", because that frequently leads to ordering
decisions that are not a lot better than random from a semantic
standpoint. For example, I resist the idea that it's sensible
to put pg_stat_all_indexes before pg_stat_all_tables.
I'm unconvinced that putting pg_stat_sys_tables and
pg_stat_user_tables far away from pg_stat_all_tables is great,
either.

Thanks for taking the time to look at my patch. The "at all costs"
approach was not the intention - I was just trying only to apply some
sane ordering where I did not recognize a reason for the current
order.

So ... how do we proceed?

To proceed with the existing patches I need some guidance on exactly
which of the changes can be considered improvements versus which ones
are maybe just trading one 'random' order for another.

How about below?

Table 28.1. Dynamic Statistics Views -- Alphabetical order would be a
small improvement here, right?

Table 28.2. Collected Statistics Views -- Leave this one unchanged
(per your comments above).

Table 28.12 Wait Events of type LWLock -- Seems a clear case of bad
merging. Alphabetical order is surely needed here, right?

Table 28.34 Additional Statistic Functions -- Alphabetical order would
be a small improvement here, right?

Table 28.35 Per-Backend Statistics Functions -- Alphabetical order
would be a small improvement here, right?

One thing I'm unhappy about that you didn't address is that
the subsection ordering in "28.4. Progress Reporting" could
hardly have been invented even with a dartboard. Perhaps it
reflects development order, but that's a poor excuse.
I'd be inclined to alphabetize by SQL command name, but maybe
leave Base Backup to the end since it's not a SQL command.

Yes, I had previously only looked at the content of section 28.2
because I didn't want to get carried away by changing too much until
there was some support for doing the first part.

Now PSA a separate patch for fixing section "28.4. Progress Reporting"
order as suggested.

-----
Kind Regards,
Peter Smith.
Fujitsu Australia.

On Mon, Nov 7, 2022 at 5:19 PM Peter Smith <smithpb2250@gmail.com> wrote:

On Mon, Nov 7, 2022 at 5:50 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

Peter Smith <smithpb2250@gmail.com> writes:

Sorry, I forgot the attachments in the previous post. PSA.

I spent a bit of time looking at this. I agree that a lot of the
current ordering choices here look like they were made with the
advice of a dartboard, and there's a number of things that are
pretty blatantly just sloppy merging (like the out-of-order
wait-event items). However, I'm not a big fan of "alphabetical
order at all costs", because that frequently leads to ordering
decisions that are not a lot better than random from a semantic
standpoint. For example, I resist the idea that it's sensible
to put pg_stat_all_indexes before pg_stat_all_tables.
I'm unconvinced that putting pg_stat_sys_tables and
pg_stat_user_tables far away from pg_stat_all_tables is great,
either.

Thanks for taking the time to look at my patch. The "at all costs"
approach was not the intention - I was just trying only to apply some
sane ordering where I did not recognize a reason for the current
order.

So ... how do we proceed?

To proceed with the existing patches I need some guidance on exactly
which of the changes can be considered improvements versus which ones
are maybe just trading one 'random' order for another.

How about below?

Table 28.1. Dynamic Statistics Views -- Alphabetical order would be a
small improvement here, right?

The present ordering seems mostly OK, though just like the "Progress"
update below the bottom 6 pg_stat_progress_* entries should be
alphabetized; but leaving them as a group at the end seems desirable.

Move pg_stat_recovery_prefetch either after subscription or after activity
- the replication/received/subscription stuff all seems like it should be
grouped together. As well as the security related ssl/gssapi.

Table 28.2. Collected Statistics Views -- Leave this one unchanged
(per your comments above).

I would suggest moving the 3 pg_statio_*_tables rows between the
pg_stat_*_tables and the pg_stat_xact_*_tables groups.

Everything pertaining to cluster, database, tables, indexes, functions.
slru and replication slots should likewise shift to the (near) top in the
cluster/database grouping.

Table 28.12 Wait Events of type LWLock -- Seems a clear case of bad
merging. Alphabetical order is surely needed here, right?

+1 Agreed.

Table 28.34 Additional Statistic Functions -- Alphabetical order would
be a small improvement here, right?

No. All "reset" items should be grouped at the end like they are. I don't
see an alternative ordering among them that is clearly superior. Same for
the first four.

Table 28.35 Per-Backend Statistics Functions -- Alphabetical order
would be a small improvement here, right?

This one I would rearrange alphabetically. Or, at least, I have a
different opinion of what would make a decent order but it doesn't seem all
that clearly better than alphabetical.

I'd be inclined to alphabetize by SQL command name, but maybe
leave Base Backup to the end since it's not a SQL command.

Yes, I had previously only looked at the content of section 28.2
because I didn't want to get carried away by changing too much until
there was some support for doing the first part.

Now PSA a separate patch for fixing section "28.4. Progress Reporting"
order as suggested.

This seems like a clear win.

David J.

On Thu, Nov 10, 2022 at 10:04 AM David G. Johnston
<david.g.johnston@gmail.com> wrote:

...

So ... how do we proceed?

To proceed with the existing patches I need some guidance on exactly
which of the changes can be considered improvements versus which ones
are maybe just trading one 'random' order for another.

How about below?

Table 28.1. Dynamic Statistics Views -- Alphabetical order would be a
small improvement here, right?

The present ordering seems mostly OK, though just like the "Progress" update below the bottom 6 pg_stat_progress_* entries should be alphabetized; but leaving them as a group at the end seems desirable.

Move pg_stat_recovery_prefetch either after subscription or after activity - the replication/received/subscription stuff all seems like it should be grouped together. As well as the security related ssl/gssapi.

Table 28.2. Collected Statistics Views -- Leave this one unchanged
(per your comments above).

I would suggest moving the 3 pg_statio_*_tables rows between the pg_stat_*_tables and the pg_stat_xact_*_tables groups.

Everything pertaining to cluster, database, tables, indexes, functions. slru and replication slots should likewise shift to the (near) top in the cluster/database grouping.

Table 28.12 Wait Events of type LWLock -- Seems a clear case of bad
merging. Alphabetical order is surely needed here, right?

+1 Agreed.

Table 28.34 Additional Statistic Functions -- Alphabetical order would
be a small improvement here, right?

No. All "reset" items should be grouped at the end like they are. I don't see an alternative ordering among them that is clearly superior. Same for the first four.

Table 28.35 Per-Backend Statistics Functions -- Alphabetical order
would be a small improvement here, right?

This one I would rearrange alphabetically. Or, at least, I have a different opinion of what would make a decent order but it doesn't seem all that clearly better than alphabetical.

I'd be inclined to alphabetize by SQL command name, but maybe
leave Base Backup to the end since it's not a SQL command.

Yes, I had previously only looked at the content of section 28.2
because I didn't want to get carried away by changing too much until
there was some support for doing the first part.

Now PSA a separate patch for fixing section "28.4. Progress Reporting"
order as suggested.

This seems like a clear win.

David J.

Thanks for the review and table ordering advice. AFAICT I have made
all the changes according to the suggestions.

Each re-ordering was done as a separate patch (so maybe they can be
pushed separately, in case some but not all are OK). PSA.

~~

I was also wondering (but have not yet done) if the content *outside*
the tables should be reordered to match the table 28.1/28.2 order.

e.g. Currently it is not quite the same:

CURRENT
28.2.3. pg_stat_activity
28.2.4. pg_stat_replication
28.2.5. pg_stat_replication_slots
28.2.6. pg_stat_wal_receiver
28.2.7. pg_stat_recovery_prefetch
28.2.8. pg_stat_subscription
28.2.9. pg_stat_subscription_stats
28.2.10. pg_stat_ssl
28.2.11. pg_stat_gssapi

28.2.12. pg_stat_archiver
28.2.13. pg_stat_bgwriter
28.2.14. pg_stat_wal
28.2.15. pg_stat_database
28.2.16. pg_stat_database_conflicts
28.2.17. pg_stat_all_tables
28.2.18. pg_stat_all_indexes
28.2.19. pg_statio_all_tables
28.2.20. pg_statio_all_indexes
28.2.21. pg_statio_all_sequences
28.2.22. pg_stat_user_functions
28.2.23. pg_stat_slru

SUGGESTED
28.2.3. pg_stat_activity
28.2.4. pg_stat_replication
28.2.6. pg_stat_wal_receiver
28.2.7. pg_stat_recovery_prefetch
28.2.8. pg_stat_subscription
28.2.10. pg_stat_ssl
28.2.11. pg_stat_gssapi

28.2.12. pg_stat_archiver
28.2.13. pg_stat_bgwriter
28.2.14. pg_stat_wal
28.2.15. pg_stat_database
28.2.16. pg_stat_database_conflicts
28.2.23. pg_stat_slru
28.2.5. pg_stat_replication_slots
28.2.17. pg_stat_all_tables
28.2.18. pg_stat_all_indexes
28.2.19. pg_statio_all_tables
28.2.20. pg_statio_all_indexes
28.2.21. pg_statio_all_sequences
28.2.22. pg_stat_user_functions
28.2.9. pg_stat_subscription_stats

Thoughts?

------
Kind Regards,
Peter Smith.
Fujitsu Australia

On Tue, Nov 15, 2022 at 6:39 PM Peter Smith <smithpb2250@gmail.com> wrote:

I was also wondering (but have not yet done) if the content *outside*
the tables should be reordered to match the table 28.1/28.2 order.

Thoughts?

I would love to do away with the ToC listing of view names in 28.2
altogether.

Also, make it so each view ends up being its own separate page.

The name of the views in the table should then be the hyperlinks to those
pages.

Basically the way Chapter 54.1 works. Though the interplay between the top
Chapter 54 and 54.1 is a bit repetitive.

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

I wonder whether having the table be structured but the ToC be purely
alphabetical would be considered a good idea...

The tables need hyperlinks regardless. I wouldn't insist on changing the
ordering to match the table, especially with the hyperlinks, but I also
wouldn't reject it. Figuring out how to make them one-per-page would be
time better spent though.

David J.

On Thu, Nov 17, 2022 at 8:46 AM David G. Johnston
<david.g.johnston@gmail.com> wrote:

On Tue, Nov 15, 2022 at 6:39 PM Peter Smith <smithpb2250@gmail.com> wrote:

I was also wondering (but have not yet done) if the content *outside*
the tables should be reordered to match the table 28.1/28.2 order.

Thoughts?

Thanks for the feedback/suggestions

I would love to do away with the ToC listing of view names in 28.2 altogether.

OK, done. See patch 0006. To prevent all the views sections from
participating in the ToC I simply changed them to <sect3> instead of
<sect2>. I’m not 100% sure if this was a brilliant modification or a
total hack, but it does do exactly what you wanted.

Also, make it so each view ends up being its own separate page.

I did not do this. AFAIK those views of chapter 54 get rendered to
separate pages only because they are top-level <sect1>. So I do not
know how to put all these stats views onto different pages without
radically changing the document structure. Anyway – doing this would
be incompatible with my <sect3> changes of patch 0006 (see above).

The name of the views in the table should then be the hyperlinks to those pages.

OK done. See patch 0005. All the view names (in column one of the
tables) are hyperlinked to the views the same way as Chapter 54 does.
The tables are a lot cleaner now. A couple of inconsistent view ids
were also corrected.

Basically the way Chapter 54.1 works. Though the interplay between the top Chapter 54 and 54.1 is a bit repetitive.

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

I wonder whether having the table be structured but the ToC be purely alphabetical would be considered a good idea...

The tables need hyperlinks regardless. I wouldn't insist on changing the ordering to match the table, especially with the hyperlinks, but I also wouldn't reject it. Figuring out how to make them one-per-page would be time better spent though.

PSA new patches. Now there are 6 of them. If some of the earlier
patches are agreeable can those ones please be committed? (because I
think this patch may be susceptible to needing a big rebase if
anything in those tables changes).

------
Kind Regards,
Peter Smith.
Fujitsu Australia.

On 23.11.22 09:36, Peter Smith wrote:

PSA new patches. Now there are 6 of them. If some of the earlier
patches are agreeable can those ones please be committed? (because I
think this patch may be susceptible to needing a big rebase if
anything in those tables changes).

I have committed

v6-0001-Re-order-sections-of-28.4.-Progress-Reporting.patch
v6-0003-Re-order-Table-28.12-Wait-Events-of-type-LWLock.patch
v6-0004-Re-order-Table-28.35-Per-Backend-Statistics-Funct.patch

which seemed to have clear consensus.

v6-0002-Re-order-Table-28.2-Collected-Statistics-Views.patch

This one also seems ok, need a bit more time to look it over.

v6-0005-Cleanup-view-name-hyperlinks-for-Tables-28.1-and-.patch
v6-0006-Remove-all-stats-views-from-the-ToC-of-28.2.patch

I wasn't sure yet whether these had been reviewed yet, sine they were
late additions to the patch series.

On Fri, Nov 25, 2022 at 5:09 AM Peter Eisentraut <
peter.eisentraut@enterprisedb.com> wrote:

On 23.11.22 09:36, Peter Smith wrote:

v6-0005-Cleanup-view-name-hyperlinks-for-Tables-28.1-and-.patch
v6-0006-Remove-all-stats-views-from-the-ToC-of-28.2.patch

I wasn't sure yet whether these had been reviewed yet, sine they were
late additions to the patch series.

They have not been reviewed.

If it's a matter of either-or I'd really prefer one page per grouping over
getting rid of the table-of-contents. But I suspect there has to be some
way to add an sgml element to the markup to force a new page and would
prefer to confirm or refute that prior to committing 0006.

0005 seems a win either way though I haven't reviewed it yet.

David J.

On Wed, Nov 23, 2022 at 1:36 AM Peter Smith <smithpb2250@gmail.com> wrote:

On Thu, Nov 17, 2022 at 8:46 AM David G. Johnston
<david.g.johnston@gmail.com> wrote:

Also, make it so each view ends up being its own separate page.

I did not do this. AFAIK those views of chapter 54 get rendered to
separate pages only because they are top-level <sect1>. So I do not
know how to put all these stats views onto different pages without
radically changing the document structure. Anyway – doing this would
be incompatible with my <sect3> changes of patch 0006 (see above).

I did some experimentation and reading on this today. Short answer - turn
each view into a refentry under a dedicated sect2 where the table resides.

David J.

<chapter>
[...]
<sect1> <!--The Cumulative Statistics System -->
[...]
<sect2>
<title>Statistics Views</title>
<para>Table of Statistics Views...</para>

<refentry id="monitoring-pg-stat-activity-view">
<refnamediv><refname>pg_stat_activity</refname><refpurpose>Purpose</refpurpose></refnamediv>
<refsect1>
<title><structname>pg_stat_activity</structname></title>

<indexterm>
<primary>pg_stat_activity</primary>
</indexterm>

</refsect1></refentry>

</sect2> <!-- Statistics Views -->

</sect1>
</chapter>

I was doing quite a bit of experimentation and basically gutted the actual
page to make that easier. The end result looked basically like below.

Chapter 28. Monitoring Database Activity

Table of Contents

28.1. Standard Unix Tools
28.2. The Cumulative Statistics System

28.2.1. Statistics Collection Configuration
28.2.2. Viewing Statistics
28.2.3. Statistics Views

A database administrator frequently wonders, “What is the system doing
right now?” This chapter discusses how to find that out.

Several tools are available for monitoring database activity and analyzing
performance. Most of this chapter is devoted to describing PostgreSQL's
cumulative statistics system, but one should not neglect regular Unix
monitoring programs such as ps, top, iostat, and vmstat. Also, once one has
identified a poorly-performing query, further investigation might be needed
using PostgreSQL's EXPLAIN command. Section 14.1 discusses EXPLAIN and
other methods for understanding the behavior of an individual query.

============== Page for 28.2 (sect1) ==============
28.2. The Cumulative Statistics System

28.2.1. Statistics Collection Configuration
28.2.2. Viewing Statistics
28.2.3. Statistics Views

PostgreSQL's cumulative statistics system supports collection and reporting
of information about server activity. Presently, accesses to tables and
indexes in both disk-block and individual-row terms are counted. The total
number of rows in each table, and information about vacuum and analyze
actions for each table are also counted. If enabled, calls to user-defined
functions and the total time spent in each one are counted as well.

PostgreSQL also supports reporting dynamic information about exactly what
is going on in the system right now, such as the exact command currently
being executed by other server processes, and which other connections exist
in the system. This facility is independent of the cumulative statistics
system.
28.2.1. Statistics Collection Configuration

Since collection of statistics adds some overhead to query execution, the
system can be configured to collect or not collect information. This is
controlled by configuration parameters that are normally set in
postgresql.conf. (See Chapter 20 for details about setting configuration
parameters.)

The parameter track_activities enables monitoring of the current command
being executed by any server process.

The parameter track_counts controls whether cumulative statistics are
collected about table and index accesses.

The parameter track_functions enables tracking of usage of user-defined
functions.

The parameter track_io_timing enables monitoring of block read and write
times.

The parameter track_wal_io_timing enables monitoring of WAL write times.

Normally these parameters are set in postgresql.conf so that they apply to
all server processes, but it is possible to turn them on or off in
individual sessions using the SET command. (To prevent ordinary users from
hiding their activity from the administrator, only superusers are allowed
to change these parameters with SET.)

Cumulative statistics are collected in shared memory. Every PostgreSQL
process collects statistics locally, then updates the shared data at
appropriate intervals. When a server, including a physical replica, shuts
down cleanly, a permanent copy of the statistics data is stored in the
pg_stat subdirectory, so that statistics can be retained across server
restarts. In contrast, when starting from an unclean shutdown (e.g., after
an immediate shutdown, a server crash, starting from a base backup, and
point-in-time recovery), all statistics counters are reset.
28.2.2. Viewing Statistics

test
28.2.3. Statistics Views

Table of Statistics Views...

===============
file:///usr/local/pgsql/share/doc/html/monitoring-pg-stat-activity-view.html
=============
(no ToC entry but the Next link in our footer does point to here)

pg_stat_activity

pg_stat_activity — Purpose
pg_stat_activity

The pg_stat_activity view will have one row per server process, showing
information related to the current activity of that process.

Here is an example of how wait events can be viewed:

SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE
wait_event is NOT NULL;
pid | wait_event_type | wait_event
------+-----------------+------------
2540 | Lock | relation
6644 | LWLock | ProcArray
(2 rows)

On Fri, Nov 25, 2022 at 11:09 PM Peter Eisentraut
<peter.eisentraut@enterprisedb.com> wrote:

On 23.11.22 09:36, Peter Smith wrote:

PSA new patches. Now there are 6 of them. If some of the earlier
patches are agreeable can those ones please be committed? (because I
think this patch may be susceptible to needing a big rebase if
anything in those tables changes).

I have committed

v6-0001-Re-order-sections-of-28.4.-Progress-Reporting.patch
v6-0003-Re-order-Table-28.12-Wait-Events-of-type-LWLock.patch
v6-0004-Re-order-Table-28.35-Per-Backend-Statistics-Funct.patch

which seemed to have clear consensus.

v6-0002-Re-order-Table-28.2-Collected-Statistics-Views.patch

This one also seems ok, need a bit more time to look it over.

v6-0005-Cleanup-view-name-hyperlinks-for-Tables-28.1-and-.patch
v6-0006-Remove-all-stats-views-from-the-ToC-of-28.2.patch

I wasn't sure yet whether these had been reviewed yet, sine they were
late additions to the patch series.

Thank you for pushing those ones.

PSA the remaining patches re-posted so cfbot can keep working

v6-0002 --> v7-0001
v6-0005 -> v7-0002
v6-0006 -> v7-0003

------
Kind Regards,
Peter Smith.
Fujitsu Australia

On Sat, Nov 26, 2022 at 2:43 PM David G. Johnston
<david.g.johnston@gmail.com> wrote:

On Wed, Nov 23, 2022 at 1:36 AM Peter Smith <smithpb2250@gmail.com> wrote:

On Thu, Nov 17, 2022 at 8:46 AM David G. Johnston
<david.g.johnston@gmail.com> wrote:

Also, make it so each view ends up being its own separate page.

I did not do this. AFAIK those views of chapter 54 get rendered to
separate pages only because they are top-level <sect1>. So I do not
know how to put all these stats views onto different pages without
radically changing the document structure. Anyway – doing this would
be incompatible with my <sect3> changes of patch 0006 (see above).

I did some experimentation and reading on this today. Short answer - turn each view into a refentry under a dedicated sect2 where the table resides.

Thanks very much for your suggestion.

I will look at redoing the v7-0003 patch using that approach when I
get some more time (maybe in a day or so),

------
Kind Regards,
Peter Smith.
Fujitsu Australia

On Sat, Nov 26, 2022 at 2:43 PM David G. Johnston
<david.g.johnston@gmail.com> wrote:

On Wed, Nov 23, 2022 at 1:36 AM Peter Smith <smithpb2250@gmail.com> wrote:

On Thu, Nov 17, 2022 at 8:46 AM David G. Johnston
<david.g.johnston@gmail.com> wrote:

Also, make it so each view ends up being its own separate page.

I did not do this. AFAIK those views of chapter 54 get rendered to
separate pages only because they are top-level <sect1>. So I do not
know how to put all these stats views onto different pages without
radically changing the document structure. Anyway – doing this would
be incompatible with my <sect3> changes of patch 0006 (see above).

I did some experimentation and reading on this today. Short answer - turn each view into a refentry under a dedicated sect2 where the table resides.

David J.

<chapter>
[...]
<sect1> <!--The Cumulative Statistics System -->
[...]
<sect2>
<title>Statistics Views</title>
<para>Table of Statistics Views...</para>

<refentry id="monitoring-pg-stat-activity-view">
<refnamediv><refname>pg_stat_activity</refname><refpurpose>Purpose</refpurpose></refnamediv>
<refsect1>
<title><structname>pg_stat_activity</structname></title>

<indexterm>
<primary>pg_stat_activity</primary>
</indexterm>

</refsect1></refentry>

</sect2> <!-- Statistics Views -->

</sect1>
</chapter>

I was doing quite a bit of experimentation and basically gutted the actual page to make that easier. The end result looked basically like below.

Chapter 28. Monitoring Database Activity

Table of Contents

28.1. Standard Unix Tools
28.2. The Cumulative Statistics System

28.2.1. Statistics Collection Configuration
28.2.2. Viewing Statistics
28.2.3. Statistics Views

PSA v8* patches.

Here, patches 0001 and 0002 are unchanged, but 0003 has many changes
per David's suggestion [1]David's restructure suggestion /messages/by-id/CAKFQuwYkM5UZT+6tG+NgZvDcd5VavS+xNHsGsWC8jS-KJsxh7w@mail.gmail.com to change all these views to <refentry>
blocks.

So, I've done pretty much the same as per the above advice, except:
- I just called the <refpurpose> text for all these views "View"
- I changed the <refsect1> <title> to be "Description". This renders
nicer (without the double text of the view name) and is also more in
keeping with the example I found here [2]Example of a refentry https://tdg.docbook.org/tdg/3.1/refentry.html.

End result seems OK. YMMV.

~

Note that the refentry order within the monitoring.sgml is unchanged
from the previous <sect2> section order, so it's neither alphabetical
nor is it in the same order as within the tables. This is noticeable
only if you pay attention to the NEXT/PREV links at the bottom of the
browser page... so I'm not sure if it's worth shuffling these refentry
blocks into some better order or not?

------
[1]: David's restructure suggestion /messages/by-id/CAKFQuwYkM5UZT+6tG+NgZvDcd5VavS+xNHsGsWC8jS-KJsxh7w@mail.gmail.com
/messages/by-id/CAKFQuwYkM5UZT+6tG+NgZvDcd5VavS+xNHsGsWC8jS-KJsxh7w@mail.gmail.com
[2]: Example of a refentry https://tdg.docbook.org/tdg/3.1/refentry.html

Kind Regards,
Peter Smith.
Fujitsu Australia

On 29.11.22 08:29, Peter Smith wrote:

PSA v8* patches.

Here, patches 0001 and 0002 are unchanged, but 0003 has many changes
per David's suggestion [1] to change all these views to <refentry>
blocks.

I don't understand what order 0001 is trying to achieve. I know we
didn't necessarily want to go fully alphabetic, but if we're going to
spend time on this, let's come up with a system that the next
contributor who adds a view will be able to understand and follow.

As an aside, I find the mixing of pg_stat_* and pg_statio_* views
visually distracting. It was easier to read before when they were in
separate blocks.

I think something like this would be manageable:

<!-- everything related to global objects, alphabetically -->
pg_stat_archiver
pg_stat_bgwriter
pg_stat_database
pg_stat_database_conflicts
pg_stat_replication_slots
pg_stat_slru
pg_stat_subscription_stats
pg_stat_wal

<!-- all "stat" for schema objects, by "importance" -->
pg_stat_all_tables
pg_stat_sys_tables
pg_stat_user_tables
pg_stat_xact_all_tables
pg_stat_xact_sys_tables
pg_stat_xact_user_tables
pg_stat_all_indexes
pg_stat_sys_indexes
pg_stat_user_indexes
pg_stat_user_functions
pg_stat_xact_user_functions

<!-- all "statio" for schema objects, by "importance" -->
pg_statio_all_tables
pg_statio_sys_tables
pg_statio_user_tables
pg_statio_all_indexes
pg_statio_sys_indexes
pg_statio_user_indexes
pg_statio_all_sequences
pg_statio_sys_sequences
pg_statio_user_sequences

In any case, the remaining patches are new and need further review, so
I'll move this to the next CF.

On Thu, Dec 1, 2022 at 2:20 AM Peter Eisentraut <
peter.eisentraut@enterprisedb.com> wrote:

On 29.11.22 08:29, Peter Smith wrote:

PSA v8* patches.

Here, patches 0001 and 0002 are unchanged, but 0003 has many changes
per David's suggestion [1] to change all these views to <refentry>
blocks.

I don't understand what order 0001 is trying to achieve.

The rule behind 0001 is:

All global object stats
All table object stats (stat > statio > xact; all > sys > user)
All index object stats
All sequence object stats
All function object stats

As an aside, I find the mixing of pg_stat_* and pg_statio_* views
visually distracting. It was easier to read before when they were in
separate blocks.

I found that having the statio at the end of each object type block added a
natural partitioning for tables and indexes that the existing order lacked
and that made reading the table be more "wall-of-text-ish", and thus more
difficult to read, than necessary.

I'm not opposed to the following though. The object-type driven order just
feels more useful but I really cannot justify it beyond that.

I'm not particularly enamored with the existing single large table but
don't have a better structure to offer at this time.

I think something like this would be manageable:

<!-- everything related to global objects, alphabetically -->
pg_stat_archiver
pg_stat_bgwriter
pg_stat_database
pg_stat_database_conflicts
pg_stat_replication_slots
pg_stat_slru
pg_stat_subscription_stats
pg_stat_wal

WAL being adjacent to archiver/bgwriter seemed reasonable so I left that
alone.
Replication and Subscription being adjacent seemed reasonable so I left
that alone.
Thus slru ended up last, with database* remaining as-is.

At 8 items, with a group size average of 2, pure alphabetical is also
reasonable.

<!-- all "stat" for schema objects, by "importance" -->

<!-- all "statio" for schema objects, by "importance" -->

David J.

I'd like to "fix" this but IIUC there is no consensus yet about what
order is best for patch 0001, right?

------
Kind Regards,
Peter Smith.
Fujitsu Australia