pg_stats_user_tables last_autovacuum for distrubuted tables

[saveas]

Hi, running select * from pg_stat_user_tables returns values for last_autovacuum only for non-distributed tables. For distributed tables it returns null. Is this a normal behaviour? How can i check the autovacuum status for distributed tables?

[onurctirtir]

Given that shell tables for distributed tables are actually empty, pg_stat_user_tables fails to report correct stats for the distributed tables. I've made up a UDF that you can use to query the stats for the shards of your distributed table instead:

CREATE FUNCTION citus_stat_user_shard_tables (distributed_table regclass)
RETURNS TABLE (
    shard_rel_name name,
    seq_scan bigint,
    last_seq_scan timestamp with time zone,
    seq_tup_read bigint,
    idx_scan bigint,
    last_idx_scan timestamp with time zone,
    idx_tup_fetch bigint,
    n_tup_ins bigint,
    n_tup_upd bigint,
    n_tup_del bigint,
    n_tup_hot_upd bigint,
    n_tup_newpage_upd bigint,
    n_live_tup bigint,
    n_dead_tup bigint,
    n_mod_since_analyze bigint,
    n_ins_since_vacuum bigint,
    last_vacuum timestamp with time zone,
    last_autovacuum timestamp with time zone,
    last_analyze timestamp with time zone,
    last_autoanalyze timestamp with time zone,
    vacuum_count bigint,
    autovacuum_count bigint,
    analyze_count bigint,
    autoanalyze_count bigint
) AS
$func$
    SELECT
        (shard_info->>'relname')::name AS shard_rel_name,
        (shard_info->>'seq_scan')::bigint AS seq_scan,
        (shard_info->>'last_seq_scan')::timestamp with time zone AS last_seq_scan,
        (shard_info->>'seq_tup_read')::bigint AS seq_tup_read,
        (shard_info->>'idx_scan')::bigint AS idx_scan,
        (shard_info->>'last_idx_scan')::timestamp with time zone AS last_idx_scan,
        (shard_info->>'idx_tup_fetch')::bigint AS idx_tup_fetch,
        (shard_info->>'n_tup_ins')::bigint AS n_tup_ins,
        (shard_info->>'n_tup_upd')::bigint AS n_tup_upd,
        (shard_info->>'n_tup_del')::bigint AS n_tup_del,
        (shard_info->>'n_tup_hot_upd')::bigint AS n_tup_hot_upd,
        (shard_info->>'n_tup_newpage_upd')::bigint AS n_tup_newpage_upd,
        (shard_info->>'n_live_tup')::bigint AS n_live_tup,
        (shard_info->>'n_dead_tup')::bigint AS n_dead_tup,
        (shard_info->>'n_mod_since_analyze')::bigint AS n_mod_since_analyze,
        (shard_info->>'n_ins_since_vacuum')::bigint AS n_ins_since_vacuum,
        (shard_info->>'last_vacuum')::timestamp with time zone AS last_vacuum,
        (shard_info->>'last_autovacuum')::timestamp with time zone AS last_autovacuum,
        (shard_info->>'last_analyze')::timestamp with time zone AS last_analyze,
        (shard_info->>'last_autoanalyze')::timestamp with time zone AS last_autoanalyze,
        (shard_info->>'vacuum_count')::bigint AS vacuum_count,
        (shard_info->>'autovacuum_count')::bigint AS autovacuum_count,
        (shard_info->>'analyze_count')::bigint AS analyze_count,
        (shard_info->>'autoanalyze_count')::bigint AS autoanalyze_count
    FROM (
        SELECT jsonb_array_elements(result::jsonb)::jsonb AS shard_info FROM run_command_on_placements(
            distributed_table,
            $$
            SELECT jsonb_agg(p.*)
            FROM pg_stat_user_tables p
            WHERE (relid = '%1$s'::regclass)
            $$
        )
    );
    $func$
LANGUAGE SQL;

Usage:

SELECT * FROM citus_stat_user_shard_tables('dist');

[saveas]

that's great! thanks :)

[JelteF]

I'll keep the issue open, since it would be great if this UDF was actually part of citus.