Re: Table health

Thanks

On Wed, 18 Oct, 2023, 7:51 PM Ron, <ronljohnsonjr(at)gmail(dot)com> wrote:

>
> * 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
> ;
>
>
> On 10/18/23 08:37, Rajesh Kumar wrote:
>
> 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(at)gmail(dot)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(dot)dba09(at)gmail(dot)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
>> <https://www.facebook.com/tomasz.main/photos_albums>
>>
>
> --
> Born in Arizona, moved to Babylonia.
>