Re: Table health

* cache hit ratio for ... : does it matter?  You don't control that anyway.
* unused index removal : table pg_stat_all_indexes
* duplicate index removal : this query shows all indices, including columns.  Look for duplicates in table_name+index_columns:
select tbcl.relnamespace::regnamespace::text||'.'||tbcl.relname as table_name
        , ndcl.relname as index_name
        , array_agg(att.attname order by att.attnum) as index_columns
from pg_class ndcl
    inner join pg_index nd
        on (ndcl.oid = nd.indexrelid and ndcl.relkind = 'i')
    inner join pg_class tbcl
        on (nd.indrelid = tbcl.oid and tbcl.relkind = 'r')
    inner join pg_attribute att
        on att.attrelid = nd.indexrelid
where tbcl.relnamespace::regnamespace::text != 'pg_catalog'
group by tbcl.relnamespace::regnamespace::text||'.'||tbcl.relname
        , ndcl.relname
order by 1, 2
;

There is no problem with vaccum, i used to do it periodically...apart from vaccum i am asking things like table bloat, index bloat, duplicate index removal, unused index removal, cache hit ratio for table, cache hit ratio for index, .......what else?

Also, i saw cache hit ratio is only 2% for one big table and all other tables are around 9%. Is that a problem?

On Wed, 18 Oct, 2023, 6:10 PM Tomek, <tomekphotos@gmail.com> wrote:

As a preliminary and often but good enough test, I recommend VACUUM FREEZE VERBOSE ...
In 95% of cases such simple check will satisfy your needs .
Of course amcheck tool is much better but it is an extension. 

wt., 17 paź 2023 o 19:59 Rajesh Kumar <rajeshkumar.dba09@gmail.com> napisał(a):

I have been asked to check table health of specific big table.

What are list of things I have to check?

--

--
Polecam moje mojego autorstwa na FB