Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qt7HK-00AuKZ-Td for pgsql-admin@arkaria.postgresql.org; Wed, 18 Oct 2023 14:12:07 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qt7HJ-004UKW-0J for pgsql-admin@arkaria.postgresql.org; Wed, 18 Oct 2023 14:12:05 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qt7HI-004UJB-Jv for pgsql-admin@lists.postgresql.org; Wed, 18 Oct 2023 14:12:05 +0000 Received: from mail-yb1-xb2c.google.com ([2607:f8b0:4864:20::b2c]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qt7HE-001NJu-M1 for pgsql-admin@lists.postgresql.org; Wed, 18 Oct 2023 14:12:04 +0000 Received: by mail-yb1-xb2c.google.com with SMTP id 3f1490d57ef6-d9b2ca542e5so5977275276.3 for ; Wed, 18 Oct 2023 07:12:01 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1697638319; x=1698243119; darn=lists.postgresql.org; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:from:to:cc:subject:date:message-id :reply-to; bh=TVpFCewzGOncqyWRPZmq2COPhUJzf2VwcDMsLOzWruc=; b=YX6J8K7E5gLI08c/GJJlQ/ceGpNG6Qz5q3vCiWiUDjXOSu/kEXNmheKlB0Zg5TtwCT O/8kbDr/bERiSsh7bMiSac+6VcmAzWLSR5BGd+zAJVNZs3fvqpR2RBvbkDmsg7YCJ3oZ agIr76EAXDLwf7+DkV1og23KXY6kruW6z13mF3f74J5JdNTb2Yorhlz4YGFpLoZr1tVI VAa63F/XR4aJUFbPPEhnKkiI9wJhM/toUb6LRVnZWqcAdPSbLOQql0gMDPvENyScYQNf undmDvpl6NTCz2Ne0i86NMCPiiVeaOE2MgBlGckZn7NNrB22x4z813wEgfdhRNuT2s2C MqJA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1697638319; x=1698243119; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:x-gm-message-state:from:to:cc:subject :date:message-id:reply-to; bh=TVpFCewzGOncqyWRPZmq2COPhUJzf2VwcDMsLOzWruc=; b=u19TTR4uIJIydPytd3CYQhpDO71uF7R6bdnazKPPc2dM+uvHwjtSj9qMTYjXm2BP0s q8KkiNGv2FqnMiz90OWLq4g8ejHJNMdQGpu1rW3RpBUU2GdeKmpEHibwvuKjOtTBjXda LurswuHuHMzMc0FlwN5Ru0qRzoSdB7A8nIOtcbJ44VHwMJCEgki2zwXx0Y2qo7MIPISY bX+p9LBJMJFxfZBGIoWZiQOf8OdaCi+vjugMuq8QAbP/k4mzBLOb6acYunjIyK2T5ijC G991GunPunixzr8mvg2oZFvQMovtfXzm1OHY9+K+MlHAJveERDgZzy4+KZsE6/mwT27j NOGw== X-Gm-Message-State: AOJu0YxKWFFJsG+r5c4MzELodUkGGGQraUm1iwVp0tmcT8K7Do7DYmO/ qonFAv5FAQmHOyyefNNAk+r1B9AChj0= X-Google-Smtp-Source: AGHT+IGJ14Lp+x+ktqUqOXdM+CjLu5rtjsOBRWJ/t7rseYWAI1gey7WM1PP97goqfvku0sYfoLaUKw== X-Received: by 2002:a25:6b46:0:b0:d9a:4a5f:4a34 with SMTP id o6-20020a256b46000000b00d9a4a5f4a34mr5075703ybm.18.1697638319364; Wed, 18 Oct 2023 07:11:59 -0700 (PDT) Received: from ?IPV6:2600:1700:bd2c:b810:3d15:81b3:3a0:4d09? ([2600:1700:bd2c:b810:3d15:81b3:3a0:4d09]) by smtp.googlemail.com with ESMTPSA id e192-20020a2537c9000000b00d728a2738f1sm1317785yba.61.2023.10.18.07.11.59 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Wed, 18 Oct 2023 07:11:59 -0700 (PDT) Content-Type: multipart/alternative; boundary="------------Py0ibTmZqWJ0pksh5S84w3e9" Message-ID: <27150a91-1fc1-44b4-ad55-54598a3f58dc@gmail.com> Date: Wed, 18 Oct 2023 09:11:58 -0500 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: Table health Content-Language: en-US To: pgsql-admin@lists.postgresql.org References: From: Ron In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------Py0ibTmZqWJ0pksh5S84w3e9 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit * 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, 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 > 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 > > -- Born in Arizona, moved to Babylonia. --------------Py0ibTmZqWJ0pksh5S84w3e9 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit
* 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?


--

--
Born in Arizona, moved to Babylonia.
--------------Py0ibTmZqWJ0pksh5S84w3e9--