Re: Postgres DB Slowness

Can you please paste explain analyze SELECT *
                FROM npcurren.num_aangesloten_nr output?

On Thu, Aug 22, 2019 at 8:57 PM <[email protected]> wrote:

>
>
>
>
> *From:* Bhattacharjee, Soumik
> *Sent:* Thursday, August 22, 2019 5:05 PM
> *To:* 'Ron' <[email protected]>; [email protected]
> *Subject:* RE: Postgres DB Slowness
>
>
>
> *From:* Ron <[email protected]> <[email protected]>
> *Sent:* Thursday, August 22, 2019 4:26 PM
> *To:* [email protected]
> *Subject:* Re: Postgres DB Slowness
>
>
>
> On 8/22/19 9:04 AM, [email protected] 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.
>
>
> Have you validated that the Postgres server has the same indexes?
>
> If so, did you ANALYZE all the tables?
>
>
>
> Yes Ron, we migrated from Oracle as per same structure and indexes.
>
>
>
> I did the analyze for 2-3 tables now as per my SQL query I wrote and also
> ran for one particular table below –
>
>
>
> VACUUM (FULL,ANALYZE) table_name.
>
>
> What does the query plan for that query look like?
>
> What's the table definition?
>
> Is the hardware comparable?  (Might it be a SAN or VM issue?)
>
>
>
> 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)"
>
>
>
>
>
> -          Oracle 11gR2  was hosted on physical HP-UX server last updated
> 12 years back- legacy system
>
> -          Current postgres server- physical
>
> $ cat /etc/redhat-release
>
> Red Hat Enterprise Linux Server release 7.6 (Maipo)
>
> $ uname -a
>
> Linux slnc7r1513.db.gen.local 3.10.0-957.1.3.el7.x86_64 #1 SMP Thu Nov 15
> 17:36:42 UTC 2018 x86_64 x86_64 x86_64 GNU/Linux
>
>
>
>
>
> 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
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>