Exploring pg_stat_last_operation in Greenplum

posted Apr 28, 2017, 12:01 PM by Sachchida Ojha
sachi=# select distinct staactionname from pg_stat_last_operation;
 staactionname 
---------------
 ALTER
 ANALYZE
 CREATE
 PRIVILEGE
 VACUUM
(5 rows)

sachi=# 

--- List of tables not analyzed in last 3 days

SELECT
n.nspname, 
c.relname,
a.statime,
'analyze '||n.nspname||'.'||c.relname||';'
FROM
pg_class c INNER JOIN pg_namespace n
ON (c.relnamespace = n.oid)
LEFT OUTER JOIN pg_partitions pgp
ON (
pgp.schemaname = n.nspname
AND pgp.tablename = c.relname
)
LEFT OUTER JOIN (
SELECT DISTINCT
objid,
statime
FROM
pg_stat_last_operation
WHERE
staactionname in ('ANALYZE')
) a
ON (a.objid = c.oid)
WHERE
n.nspname NOT IN ('pg_catalog','information_schema','gp_toolkit')
and c.relname not like 'stg%' and c.relname not like 'swap%'
AND relkind in ('r')
AND c.relstorage = 'h'
and date_trunc('day',statime) < date_trunc('day',localtimestamp- interval '3 day')
ORDER BY reltuples DESC;

-- List of table created in last 30 days

SELECT
n.nspname, 
c.relname,
a.statime
FROM
pg_class c INNER JOIN pg_namespace n
ON (c.relnamespace = n.oid)
LEFT OUTER JOIN pg_partitions pgp
ON (
pgp.schemaname = n.nspname
AND pgp.tablename = c.relname
)
LEFT OUTER JOIN (
SELECT DISTINCT
objid,
statime
FROM
pg_stat_last_operation
WHERE
staactionname in ('CREATE')
) a
ON (a.objid = c.oid)
WHERE
n.nspname NOT IN ('pg_catalog','information_schema','gp_toolkit')
and c.relname not like 'stg%' and c.relname not like 'swap%'
AND relkind in ('r')
AND c.relstorage = 'h'
and date_trunc('day',statime) >= date_trunc('day',localtimestamp- interval '30 day')
ORDER BY reltuples DESC;


-- List of tables altered in last 10 days

SELECT
n.nspname, 
c.relname,
a.statime
FROM
pg_class c INNER JOIN pg_namespace n
ON (c.relnamespace = n.oid)
LEFT OUTER JOIN pg_partitions pgp
ON (
pgp.schemaname = n.nspname
AND pgp.tablename = c.relname
)
LEFT OUTER JOIN (
SELECT DISTINCT
objid,
statime
FROM
pg_stat_last_operation
WHERE
staactionname in ('ALTER')
) a
ON (a.objid = c.oid)
WHERE
n.nspname NOT IN ('pg_catalog','information_schema','gp_toolkit')
and c.relname not like 'stg%' and c.relname not like 'swap%'
AND relkind in ('r')
AND c.relstorage = 'h'
and date_trunc('day',statime) >= date_trunc('day',localtimestamp- interval '10 day')
ORDER BY reltuples DESC;


-- List of tables not vacuumed in last 3 days

SELECT
n.nspname, 
c.relname,
a.statime
FROM
pg_class c INNER JOIN pg_namespace n
ON (c.relnamespace = n.oid)
LEFT OUTER JOIN pg_partitions pgp
ON (
pgp.schemaname = n.nspname
AND pgp.tablename = c.relname
)
LEFT OUTER JOIN (
SELECT DISTINCT
objid,
statime
FROM
pg_stat_last_operation
WHERE
staactionname in ('VACUUM')
) a
ON (a.objid = c.oid)
WHERE
n.nspname NOT IN ('pg_catalog','information_schema','gp_toolkit')
and c.relname not like 'stg%' and c.relname not like 'swap%'
AND relkind in ('r')
AND c.relstorage = 'h'
and date_trunc('day',statime) < date_trunc('day',localtimestamp- interval '3 day')
ORDER BY reltuples DESC;
Comments