Re: Postgres DB Slowness

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

> Table definition is attached here.
>
> EXPLAIN SELECT *
> FROM npcurren.num_aangesloten_nr;
>
> "Seq Scan on num_aangesloten_nr (cost=0.00..268192.46 rows=9649046
> width=113)"

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, maybe
you compare the time to return the very first row? I can't believe Oracle can
provide 10Mo rows in 9µs.

[...]
>
> The select * from query takes all of the 2 CPU’s.
>
>
> 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
>
> PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
> 11331 postgres 20 0 5976024 16036 13432 R 92.1 0.0 0:13.98 postgres
> 11163 postgres 20 0 5981000 16764 9088 S 1.3 0.1 0:01.24 postgres

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,