From: Adrian Klaver <adrian.klaver@aklaver.com>
To: Paulo Silva <paulojjs@gmail.com>
To: pgsql-general@lists.postgresql.org
Subject: Re: PostgreSQL statistics
Date: Fri, 17 Nov 2023 08:14:43 -0800
Message-ID: <ae05aa14-cf22-480e-b397-a2c0c11a64ae@aklaver.com> (raw)
In-Reply-To: <CAHJdQrk7ycpKYJCUS8suYRF5G0NMzb08agr+Smu1NK5JrKGdxA@mail.gmail.com>
References: <CAHJdQrk7ycpKYJCUS8suYRF5G0NMzb08agr+Smu1NK5JrKGdxA@mail.gmail.com>
On 11/17/23 02:25, Paulo Silva wrote:
> Hi,
>
> I have a table in a PostgreSQL 11 server that isn't being used a lot.
>
> If I run a manual ANALYSE on it the last_analyse statistic isn't being
> update (I've waited enough time for it to be updated):
>
> # SELECT * FROM pg_catalog.pg_stat_user_tables WHERE schemaname='s1' and
> relname='t1';
> relid | schemaname | relname | seq_scan |
> seq_tup_read | idx_scan | idx_tup_fetch | n_tup_ins | n_tup_upd |
> n_tup_del | n_tup_hot_upd | n_live_tup | n_dead_tup |
> n_mod_since_analyze | last_vacuum | last_autovacuum |
> last_analyze | last_autoanalyze | vacuum_count | autovacuum_count |
> analyze_count | autoanalyze_count
> -----------+----------------------+-------------------+----------+--------------+----------+---------------+-----------+-----------+-----------+---------------+------------+------------+---------------------+-------------+-------------------------------+--------------+------------------+--------------+------------------+---------------+-------------------
> 237177743 | s1 | t1 | 0 | 0 | 0 |
> 0 | 0 | 0 | 0 | 0 | 4820 |
> 0 | 0 | | 2023-11-03
> 13:34:23.725802+00 | | | 0 |
> 1 | 0 | 0
> (1 row)
>
> But if I run a "SELECT * FROM s1.t1" I see the seq_scan increase to 1
> (as expected) and after that I can issue a manual ANALYZE and the
> last_analyze gets updated.
>
> Is this behaviour normal? Are there some parameters that are verified
> that prevent the ANALYSE from running?
The oldest Postgres instance I have access to is version 12 and I see
the update to pg_stat_user_tables when I run ANALYZE in psql in autocommit.
Are you maybe seeing the effects of this?:
https://www.postgresql.org/docs/11/monitoring-stats.html
"Another important point is that when a server process is asked to
display any of these statistics, it first fetches the most recent report
emitted by the collector process and then continues to use this snapshot
for all statistical views and functions until the end of its current
transaction. So the statistics will show static information as long as
you continue the current transaction. Similarly, information about the
current queries of all sessions is collected when any such information
is first requested within a transaction, and the same information will
be displayed throughout the transaction."
As in:
begin;
select relname, last_analyze from pg_stat_user_tables where relname =
'cell_per';
-[ RECORD 1 ]+------------------------------
relname | cell_per
last_analyze | 2023-11-17 08:11:18.614987-08
analyze cell_per ;
ANALYZE
select relname, last_analyze from pg_stat_user_tables where relname =
'cell_per';
-[ RECORD 1 ]+------------------------------
relname | cell_per
last_analyze | 2023-11-17 08:11:18.614987-08
commit;
select relname, last_analyze from pg_stat_user_tables where relname =
'cell_per';
-[ RECORD 1 ]+------------------------------
relname | cell_per
last_analyze | 2023-11-17 08:13:02.969537-08
>
> Regards
> --
> Paulo Silva <paulojjs@gmail.com <mailto:paulojjs@gmail.com>>
--
Adrian Klaver
adrian.klaver@aklaver.com
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-general@postgresql.org
Cc: adrian.klaver@aklaver.com, paulojjs@gmail.com
Subject: Re: PostgreSQL statistics
In-Reply-To: <ae05aa14-cf22-480e-b397-a2c0c11a64ae@aklaver.com>
References: <CAHJdQrk7ycpKYJCUS8suYRF5G0NMzb08agr+Smu1NK5JrKGdxA@mail.gmail.com> <ae05aa14-cf22-480e-b397-a2c0c11a64ae@aklaver.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox