From: Ron <ronljohnsonjr@gmail.com>
To: pgsql-admin@lists.postgresql.org
Subject: Re: Table health
Date: Wed, 18 Oct 2023 09:11:58 -0500
Message-ID: <27150a91-1fc1-44b4-ad55-54598a3f58dc@gmail.com> (raw)
In-Reply-To: <CAJk5AtZ8NuNqhb1QU6pdJEx7u4cF2uP529AbbTaBc-Ae2kB5eA@mail.gmail.com>
References: <CAJk5AtbS6c-P+YzmSa=2jU=vSavUk1Or=3DEd++3iAJT+-UCDg@mail.gmail.com>
<CACUaW3SqAmG4bcjAGPcrOK2T8UsSN3Cd3nEgYd++vN7OqvFzMw@mail.gmail.com>
<CAJk5AtZ8NuNqhb1QU6pdJEx7u4cF2uP529AbbTaBc-Ae2kB5eA@mail.gmail.com>
* 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@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
> <https://www.facebook.com/tomasz.main/photos_albums;
>
--
Born in Arizona, moved to Babylonia.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: ronljohnsonjr@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: Table health
In-Reply-To: <27150a91-1fc1-44b4-ad55-54598a3f58dc@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox