Обсуждение: Postgres DB Slowness

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

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)

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

Rebuilt all of the indexes?

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?

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?

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

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

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

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

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

-----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,