RE: Postgres DB Slowness

[<soumik.bhattacharjee@xxxxxxx>]

Additional information’s – another query with same table gets executed in Oracle in 0.0243 ms.

 

 

Table size

==============

SELECT pg_size_pretty( pg_total_relation_size('npcurren.NUM_AANGESLOTEN_NR') );

pg_size_pretty

----------------

3585 MB

(1 row)

 

-          It’s a physical server, remote DB server in same country(Netherlands)

 

With the same table - 'npcurren.NUM_AANGESLOTEN_NR' another query it’s not even getting executed , it’s get “killed” in postgres please find the plan below

 

Query

===========

SELECT

    i.*

FROM

    npcurren.num_cps_instelling i,

    npcurren.num_aangesloten_nr n

WHERE

    n.fk_exploit_nop_int_oper_id = 'PTT'

    AND i.telefoonnummer != n.anr_nummer_hoog

    AND i.telefoonnummer != n.anr_nummer_laag;

   

 

Explain Plan

======================

"Nested Loop  (cost=2068.47..1769226624.33 rows=101090505159 width=73)"

"  Join Filter: (((i.telefoonnummer)::text <> (n.anr_nummer_hoog)::text) AND ((i.telefoonnummer)::text <> (n.anr_nummer_laag)::text))"

"  ->  Seq Scan on num_cps_instelling i  (cost=0.00..12485.52 rows=539852 width=73)"

"  ->  Materialize  (cost=2068.47..130399.99 rows=187256 width=22)"

"        ->  Bitmap Heap Scan on num_aangesloten_nr n  (cost=2068.47..129463.71 rows=187256 width=22)"

"              Recheck Cond: ((fk_exploit_nop_int_oper_id)::text = 'PTT'::text)"

"              ->  Bitmap Index Scan on anr_idx6  (cost=0.00..2021.65 rows=187256 width=0)"

"                    Index Cond: ((fk_exploit_nop_int_oper_id)::text = 'PTT'::text)"

 

 

 

From: Bhattacharjee, Soumik
Sent: Friday, August 23, 2019 10:02 AM
To: 'Adarsh Sharma' <eddy.adarsh@xxxxxxxxx>
Cc: pgsql-admin@xxxxxxxxxxxxxxxxxxxx
Subject: RE: Postgres DB Slowness

 

Hi,

 

Please find below

 

 

"Seq Scan on num_aangesloten_nr  (cost=0.00..268192.46 rows=9649046 width=113) (actual time=0.102..2226.525 rows=9649110 loops=1)"

"Planning Time: 2.050 ms"

"Execution Time: 2785.262 ms"

 

 

 

From: Adarsh Sharma <eddy.adarsh@xxxxxxxxx>
Sent: Thursday, August 22, 2019 7:12 PM
To: Bhattacharjee, Soumik <soumik.bhattacharjee@xxxxxxx>
Cc: pgsql-admin@xxxxxxxxxxxxxxxxxxxx
Subject: 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 <soumik.bhattacharjee@xxxxxxx> wrote:

 

 

From: Bhattacharjee, Soumik
Sent: Thursday, August 22, 2019 5:05 PM
To: 'Ron' <ronljohnsonjr@xxxxxxxxx>; pgsql-admin@xxxxxxxxxxxxxxxxxxxx
Subject: RE: Postgres DB Slowness

 

From: Ron <ronljohnsonjr@xxxxxxxxx>
Sent: Thursday, August 22, 2019 4:26 PM
To: pgsql-admin@xxxxxxxxxxxxxxxxxxxx
Subject: Re: Postgres DB Slowness

 

On 8/22/19 9:04 AM, soumik.bhattacharjee@xxxxxxx 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