Re: PostgreSQL statistics

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