TOAST tables don't get analyzed, and that doesn't please last_analyze

[gleu]

You can VACUUM and ANALYZE regular tables. You can VACUUM TOAST tables, but you can't ANALYZE them. So, if you have any TOAST tables (which is probably the case for every database), last_analyze will always return -Infinity.

Quick test case.

postgres=# CREATE DATABASE testcase;
CREATE DATABASE
postgres=# \c testcase
You are now connected to database "testcase" as user "postgres".
testcase=# CREATE TABLE foo (col text);
CREATE TABLE
testcase=# SELECT relname, last_vacuum, last_analyze
FROM pg_stat_all_tables
WHERE relid IN (SELECT unnest(array[oid,reltoastrelid]) FROM pg_class WHERE relname='foo');
┌────────────────┬─────────────┬──────────────┐
│    relname     │ last_vacuum │ last_analyze │
├────────────────┼─────────────┼──────────────┤
│ foo            │             │              │
│ pg_toast_16453 │             │              │
└────────────────┴─────────────┴──────────────┘
(2 rows)

testcase=# VACUUM foo;
VACUUM
testcase=# SELECT relname, last_vacuum, last_analyze
FROM pg_stat_all_tables
WHERE relid IN (SELECT unnest(array[oid,reltoastrelid]) FROM pg_class WHERE relname='foo');
┌────────────────┬───────────────────────────────┬──────────────┐
│    relname     │          last_vacuum          │ last_analyze │
├────────────────┼───────────────────────────────┼──────────────┤
│ foo            │ 2021-01-18 09:57:24.858464+01 │              │
│ pg_toast_16453 │ 2021-01-18 09:57:24.858673+01 │              │
└────────────────┴───────────────────────────────┴──────────────┘
(2 rows)

testcase=# ANALYZE foo;
ANALYZE
testcase=# SELECT relname, last_vacuum, last_analyze
FROM pg_stat_all_tables
WHERE relid IN (SELECT unnest(array[oid,reltoastrelid]) FROM pg_class WHERE relname='foo');
┌────────────────┬───────────────────────────────┬───────────────────────────────┐
│    relname     │          last_vacuum          │         last_analyze          │
├────────────────┼───────────────────────────────┼───────────────────────────────┤
│ foo            │ 2021-01-18 09:57:24.858464+01 │ 2021-01-18 09:57:31.986478+01 │
│ pg_toast_16453 │ 2021-01-18 09:57:24.858673+01 │                               │
└────────────────┴───────────────────────────────┴───────────────────────────────┘
(2 rows)

testcase=# VACUUM ANALYZE foo;
VACUUM
testcase=# SELECT relname, last_vacuum, last_analyze
FROM pg_stat_all_tables
WHERE relid IN (SELECT unnest(array[oid,reltoastrelid]) FROM pg_class WHERE relname='foo');
┌────────────────┬───────────────────────────────┬───────────────────────────────┐
│    relname     │          last_vacuum          │         last_analyze          │
├────────────────┼───────────────────────────────┼───────────────────────────────┤
│ foo            │ 2021-01-18 09:57:38.797244+01 │ 2021-01-18 09:57:38.797654+01 │
│ pg_toast_16453 │ 2021-01-18 09:57:38.797513+01 │                               │
└────────────────┴───────────────────────────────┴───────────────────────────────┘
(2 rows)

testcase=# \! .../check_pgactivity --version
check_pgactivity version 2.5, Perl 5.32.0

testcase=# \! .../check_pgactivity  -s last_analyze -w 1 -c 1 -F human
Service        : POSTGRES_LAST_ANALYZE
Returns        : 2 (CRITICAL)
Message        : testcase: 3m6s
Perfdata       : testcase=186.984147s warn=1 crit=1
Perfdata       : testcase analyze=0
Perfdata       : testcase autoanalyze=0

TOAST tables should be ignored on the ANALYZE tests.

[gleu]

OK, I was wrong on many levels.

1. check_pgactivity doesn't look at pg_stat_all_tables, but at pg_stat_user_tables, which gets rid of TOAST table.
2. Most of my tests are done with the postgres database, and check_pgactivity doesn't check this database (which sounds like an issue to me)
3. On my test above, it returns a critical warning because I set the critical level too low. It doesn't sent back -Infinity as I supposed.

All in all, I am the bug :) Sorry for the noise.

Oh, the same query is used to get informations about VACUUMs. So it won't detect TOAST tables not being vacuumed,right?

[ioguix]

All in all, I am the bug :) Sorry for the noise.

No worries

Oh, the same query is used to get information about VACUUMs. So it won't detect TOAST tables not being vacuumed,right?

Indeed, as discussed with you offline. This could be something to fix.