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
Date: Thu, 31 Mar 2022 01:49:41 -0500
Message-ID: <20220331064941.GM28503@telsasoft.com> (raw)
In-Reply-To: <CADdgF6evsukuCYogXSBk1K0eJbwqk46+4d_FJ7qXmQ30vrpMnQ@mail.gmail.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>
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.
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-performance@postgresql.org
Cc: pryzby@telsasoft.com, rambabu.g2564@gmail.com, pgsql-performance@lists.postgresql.org
Subject: Re: HIGH IO and Less CPU utilization
In-Reply-To: <20220331064941.GM28503@telsasoft.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