Date: Thu, 31 Mar 2022 01:49:41 -0500
From: Justin Pryzby <pryzby@telsasoft.com>
To: Rambabu g <rambabu.g2564@gmail.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: HIGH IO and Less CPU utilization
Message-ID: <20220331064941.GM28503@telsasoft.com>
References: 
 <CADdgF6ecxpubq-wm4bcDCyj3yt6uNjJgXKcdCS2Jn+NvE4abFw@mail.gmail.com>
 <20220329182453.GA28503@telsasoft.com>
 <CADdgF6co=CgJBXmyNwE_GRg-Kayw5fe5WAeUQ8LDLs8s2Da_sQ@mail.gmail.com>
 <20220329193941.GB28503@telsasoft.com>
 <CADdgF6evsukuCYogXSBk1K0eJbwqk46+4d_FJ7qXmQ30vrpMnQ@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <CADdgF6evsukuCYogXSBk1K0eJbwqk46+4d_FJ7qXmQ30vrpMnQ@mail.gmail.com>
User-Agent: Mutt/1.9.4 (2018-02-28)
Archived-At: 
 <https://www.postgresql.org/message-id/20220331064941.GM28503%40telsasoft.com>
Precedence: bulk

On Wed, Mar 30, 2022 at 10:17:38AM +0530, Rambabu g wrote:
> Hi Justin,
> 
> Only one query is causing the issue, sharing the def of indexes. Please
> have a look.
> 
> > > There are three indexes defined on the table, each one is around 20 to 25GB
> 
>  tp    | character varying(2000)   | yes    | tp       | extended |
> 
>    852 | 00:09:56.131136 | IO              | DataFileRead  | explain
> analyze select distinct  empno  from emp where sname='test' and tp='EMP
> NAME 1'

The server is doing a scan of the large table.
The tp index matches a lot of rows (13e6) which probably aren't clustered, so
it elects to scan the 500GB table each time.

Looking at this in isolation, maybe it'd be enough to create an index on
tp,empno (and maybe drop the tp index).  CREATE INDEX CONCURRENTLY if you don't
want to disrupt other queries.

But This seems like something that should be solved in a better way though ;
like keeping a table with all the necessary "empno" maintained with "INSERT ON
CONFLICT DO NOTHING".  Or a trigger.