Thread: Postgres DB Slowness

Postgres DB Slowness

From
Date:

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.

 

Your immediate response is appreciated.

 

 

Thanks

Soumik

 

Attachment

FW: Postgres DB Slowness

From
Date:

Correction in version

 

# select version();

                                                 version

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

PostgreSQL 11.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-36), 64-bit

(1 row)

 

From: Bhattacharjee, Soumik
Sent: Thursday, August 22, 2019 4:05 PM
To: pgsql-admin@lists.postgresql.org; pgsql-admin@postgresql.org
Subject: Postgres DB Slowness
Importance: High

 

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.

 

 

Your immediate response is appreciated.

 

 

Thanks

Soumik

 

Attachment

Re: Postgres DB Slowness

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


Have you validated that the Postgres server has the same indexes?

If so, did you ANALYZE all the tables?

--
Angular momentum makes the world go 'round.

RE: Postgres DB Slowness

From
Perry Chandler
Date:

Rebuilt all of the indexes?

 

From: Ron <ronljohnsonjr@gmail.com>
Sent: 22 August 2019 15:26
To: pgsql-admin@lists.postgresql.org
Subject: Re: Postgres DB Slowness

 

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


Have you validated that the Postgres server has the same indexes?

If so, did you ANALYZE all the tables?

--
Angular momentum makes the world go 'round.

TaxCalc is a trading name of Acorah Software Products Limited, a company registered in England and Wales number 03948264. Registered Office: Rubra One, Fishponds Road, Wokingham, Berkshire, RG41 2GY.

Any files attached to this email will have been checked by us with virus detection software before transmission. You should carry out your own virus checks before opening any attachment. Acorah Software Products Limited accepts no liability for any loss or damage which may be caused by software viruses.

The contents of this e-mail (including any attachments) may be CONFIDENTIAL and is for the intended addressee only. If you are not the intended recipient of this e-mail, any disclosure, copying, distribution or use of its contents is strictly prohibited, and you should please notify the sender immediately and then delete it (including any attachments) from your system.

Any unauthorised use, dissemination of the information, or copying of this message is prohibited.

RE: Postgres DB Slowness

From
Date:

 

From: Ron <ronljohnsonjr@gmail.com>
Sent: Thursday, August 22, 2019 4:26 PM
To: pgsql-admin@lists.postgresql.org
Subject: Re: Postgres DB Slowness

 

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


Have you validated that the Postgres server has the same indexes?

If so, did you ANALYZE all the tables?

--
Angular momentum makes the world go 'round.

 

 

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.

 

 

 

 

Re: Postgres DB Slowness

From
Ron
Date:
On 8/22/19 9:35 AM, soumik.bhattacharjee@kpn.com wrote:

 

From: Ron <ronljohnsonjr@gmail.com>
Sent: Thursday, August 22, 2019 4:26 PM
To: pgsql-admin@lists.postgresql.org
Subject: Re: Postgres DB Slowness

 

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


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?)

--
Angular momentum makes the world go 'round.

RE: Postgres DB Slowness

From
Date:

From: Ron <ronljohnsonjr@gmail.com>
Sent: Thursday, August 22, 2019 4:26 PM
To: pgsql-admin@lists.postgresql.org
Subject: Re: Postgres DB Slowness

 

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


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

[postgres@slnc7r1513 ~]$

 

 

 

 

 

 

 

 

Attachment

RE: Postgres DB Slowness

From
Holger Jakobs
Date:
Could it be that you are comparing the time until first row to time until all rows have been transferred?

Otherwise the times don't seem sensible.


Am 22. August 2019 17:04:19 MESZ schrieb soumik.bhattacharjee@kpn.com:

From: Ron <ronljohnsonjr@gmail.com>
Sent: Thursday, August 22, 2019 4:26 PM
To: pgsql-admin@lists.postgresql.org
Subject: Re: Postgres DB Slowness

 

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


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

[postgres@slnc7r1513 ~]$

 

 

 

 

 

 

 

 


--
Holger Jakobs, Bergisch Gladbach
+49 178 9759012
- sent from mobile, therefore short -

RE: Postgres DB Slowness

From
Date:

 

 

From: Bhattacharjee, Soumik
Sent: Thursday, August 22, 2019 5:05 PM
To: 'Ron' <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.org
Subject: RE: Postgres DB Slowness

 

From: Ron <ronljohnsonjr@gmail.com>
Sent: Thursday, August 22, 2019 4:26 PM
To: pgsql-admin@lists.postgresql.org
Subject: Re: Postgres DB Slowness

 

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


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

 

 

 

 

 

 

 

 

 

 

Re: Postgres DB Slowness

From
Adarsh Sharma
Date:
Can you please paste explain analyze SELECT *
                FROM npcurren.num_aangesloten_nr output?

On Thu, Aug 22, 2019 at 8:57 PM <soumik.bhattacharjee@kpn.com> wrote:

 

 

From: Bhattacharjee, Soumik
Sent: Thursday, August 22, 2019 5:05 PM
To: 'Ron' <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.org
Subject: RE: Postgres DB Slowness

 

From: Ron <ronljohnsonjr@gmail.com>
Sent: Thursday, August 22, 2019 4:26 PM
To: pgsql-admin@lists.postgresql.org
Subject: Re: Postgres DB Slowness

 

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


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

 

 

 

 

 

 

 

 

 

 

Re: Postgres DB Slowness

From
"Jehan-Guillaume (ioguix) de Rorthais"
Date:
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:
>
> 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,



Re: Postgres DB Slowness

From
Jeff Janes
Date:
On Thu, Aug 22, 2019 at 10:05 AM <soumik.bhattacharjee@kpn.com> wrote:


There is huge slowness in the database now with any queries.

 

Oracle - Select * from TABLENAME- in takes  0.009 milliseconds


Oracle is not returning 10 million rows in 0.009 milliseconds.

Perhaps whatever you are doing in Oracle is the equivalent of "EXPLAIN" without ANALYZE in PostgreSQL, or to PREPARE in PostgreSQL.  It just plans the query without executing it.

Cheers,

Jeff

RE: Postgres DB Slowness

From
Date:

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@gmail.com>
Sent: Thursday, August 22, 2019 7:12 PM
To: Bhattacharjee, Soumik <soumik.bhattacharjee@kpn.com>
Cc: pgsql-admin@lists.postgresql.org
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@kpn.com> wrote:

 

 

From: Bhattacharjee, Soumik
Sent: Thursday, August 22, 2019 5:05 PM
To: 'Ron' <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.org
Subject: RE: Postgres DB Slowness

 

From: Ron <ronljohnsonjr@gmail.com>
Sent: Thursday, August 22, 2019 4:26 PM
To: pgsql-admin@lists.postgresql.org
Subject: Re: Postgres DB Slowness

 

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


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

 

 

 

 

 

 

 

 

 

 

RE: Postgres DB Slowness

From
Date:

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@gmail.com>
Cc: pgsql-admin@lists.postgresql.org
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@gmail.com>
Sent: Thursday, August 22, 2019 7:12 PM
To: Bhattacharjee, Soumik <soumik.bhattacharjee@kpn.com>
Cc: pgsql-admin@lists.postgresql.org
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@kpn.com> wrote:

 

 

From: Bhattacharjee, Soumik
Sent: Thursday, August 22, 2019 5:05 PM
To: 'Ron' <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.org
Subject: RE: Postgres DB Slowness

 

From: Ron <ronljohnsonjr@gmail.com>
Sent: Thursday, August 22, 2019 4:26 PM
To: pgsql-admin@lists.postgresql.org
Subject: Re: Postgres DB Slowness

 

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


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

 

 

 

 

 

 

 

 

 

 

Re: Postgres DB Slowness

From
Juan José Santamaría Flecha
Date:
On Fri, Aug 23, 2019 at 10:17 AM <soumik.bhattacharjee@kpn.com> wrote:
>
> 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)"
>

This query looks wrong, resulting in a cartesian join.

As other people have pointed out, the execution times do not seem
reasonable, could you change the 'SELECT *' into a 'SELECT count(1)'
for the comparison?

Regards,

Juan José Santamaría Flecha



Re: Postgres DB Slowness

From
legrand legrand
Date:
Hi
Could you explain us how you test

Oracle - Select * from TABLENAME-
in takes  0.009 milliseconds

Is it with oracle sql developer ?
How long does it take with sql*plus ?

Note that sql developer only display first 50 or 100 rows ,
You should use the « count rows » button to get a better duration estimate.

Regards
PAscal




--
Sent from: https://www.postgresql-archive.org/PostgreSQL-admin-f2076596.html



RE: Postgres DB Slowness

From
Date:

 

 

-----Original Message-----
From: Juan José Santamaría Flecha <juanjo.santamaria@gmail.com>
Sent: Friday, August 23, 2019 1:08 PM
To: Bhattacharjee, Soumik <soumik.bhattacharjee@kpn.com>
Cc: eddy.adarsh@gmail.com; pgsql-admin@lists.postgresql.org
Subject: Re: Postgres DB Slowness

 

On Fri, Aug 23, 2019 at 10:17 AM <soumik.bhattacharjee@kpn.com> wrote:

> 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)"

 

This query looks wrong, resulting in a cartesian join.

 

As other people have pointed out, the execution times do not seem reasonable, could you change the 'SELECT *' into a 'SELECT count(1)'

for the comparison?

 

Regards,

 

Juan José Santamaría Flecha

 

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

 

Hi Juan,

 

The query plan is below -

 

numbes_test=# explain analyze 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; 

 

                                                                       QUERY PLAN

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

Nested Loop  (cost=2071.86..1817248003.24 rows=103834572306 width=73) (actual time=68.512..28074371.506 rows=98046804138 loops=1)

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

   Rows Removed by Join Filter: 36398

   ->  Seq Scan on num_cps_instelling i  (cost=0.00..12485.52 rows=539852 width=73) (actual time=0.069..403.077 rows=539852 loops=1)

   ->  Materialize  (cost=2071.86..130606.58 rows=192339 width=22) (actual time=0.002..16.490 rows=181618 loops=539852)

         ->  Bitmap Heap Scan on num_aangesloten_nr n  (cost=2071.86..129644.88 rows=192339 width=22) (actual time=68.425..555.068 rows=181618 loops=1)

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

               Heap Blocks: exact=51470

               ->  Bitmap Index Scan on anr_idx6  (cost=0.00..2023.78 rows=192339 width=0) (actual time=59.276..59.276 rows=181618 loops=1)

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

Planning time: 4.702 ms

Execution time: 31536903.127 ms

(12 rows)

 

 

 

 

Does this also looks bad for performance with Data types- as there is varchar in a number records field ? - The table DDL is attached

 

    anr_nummer_laag character varying(20) COLLATE pg_catalog."default" NOT NULL,

    anr_nummer_hoog character varying(20) COLLATE pg_catalog."default" NOT NULL,

Attachment