Date: Thu, 22 Aug 2019 22:30:28 +0200
From: "Jehan-Guillaume (ioguix) de Rorthais" <ioguix@free.fr>
To: <soumik.bhattacharjee@kpn.com>
Cc: <ronljohnsonjr@gmail.com>, <pgsql-admin@lists.postgresql.org>
Subject: Re: Postgres DB Slowness
Message-ID: <20190822223028.5d1a30f3@firost>
In-Reply-To: 
 <2A91BEF8171A5349931391E0C721CC5359E7511B@CPEMS-KPN301.KPNCNL.LOCAL>
References: 
 <2A91BEF8171A5349931391E0C721CC5359E73FC3@CPEMS-KPN301.KPNCNL.LOCAL>
	<9840f6c7-5644-1928-e224-b06ecd9297eb@gmail.com>
	<2A91BEF8171A5349931391E0C721CC5359E75079@CPEMS-KPN301.KPNCNL.LOCAL>
	<71b2a6be-9aae-04bb-d006-c46ded2ab243@gmail.com>
	<2A91BEF8171A5349931391E0C721CC5359E7511B@CPEMS-KPN301.KPNCNL.LOCAL>
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable
Precedence: bulk

On Thu, 22 Aug 2019 15:25:50 +0000
<soumik.bhattacharjee@kpn.com> wrote:
> On 8/22/19 9:04 AM,
> soumik.bhattacharjee@kpn.com<mailto:soumik.bhattacharjee@kpn.com> wrote:
>=20
> Hello Members,
>=20
> We have the below PostgreSQL database recently migrated from Oracle.
>=20
> The postgres DB parameters are attached here.
>=20
>=20
> # DB Version: 10
>=20
> # OS Type: Linux
>=20
> # Total Memory (RAM): 30 GB
> #CPU
> nproc --all
> 2
>=20
> There is huge slowness in the database now with any queries.
>=20
> Oracle - Select * from TABLENAME- in takes  0.009 milliseconds
> PostgreSQL - Same query takes more than 2 minutes.
>=20
[...]

> Table definition is attached here.
>=20
> EXPLAIN SELECT *
>                 FROM npcurren.num_aangesloten_nr;
>=20
> "Seq Scan on num_aangesloten_nr  (cost=3D0.00..268192.46 rows=3D9649046
> width=3D113)"

First, this query can not use any kind of index as it just returns the whole
table.

Second, does your table really have about 10 million rows? If yes, do not
expect to have a result in 0.009 milliseconds. As Holger Jakobs wrote, mayb=
e=20
you compare the time to return the very first row? I can't believe Oracle c=
an
provide 10Mo rows in 9=C2=B5s.

[...]
>=20
> The select * from query takes all of the 2 CPU=E2=80=99s.
>=20
>=20
> top - 17:24:33 up 42 days,  5:12,  1 user,  load average: 0.43, 0.54, 0.57
> Tasks: 227 total,   4 running, 223 sleeping,   0 stopped,   0 zombie
> %Cpu(s): 35.2 us,  6.9 sy,  0.0 ni, 51.9 id,  0.0 wa,  0.0 hi,  6.0 si,  =
0.0
> st KiB Mem : 32947032 total, 29998788 free,   375068 used,  2573176 buff/=
cache
> KiB Swap:  2097148 total,  1953380 free,   143768 used. 30803956 avail Mem
>=20
>   PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMA=
ND
> 11331 postgres  20   0 5976024  16036  13432 R  92.1  0.0   0:13.98 postg=
res
> 11163 postgres  20   0 5981000  16764   9088 S   1.3  0.1   0:01.24 postg=
res

No. it takes one core. In top 100% means "one core is burning somewhere". On
your "2 CPU" server, %CPU can go as high as 200%.

Regards,