RE: Postgres DB Slowness

<soumik.bhattacharjee@xxxxxxx> · Thu, 22 Aug 2019 15:04:19 +0000

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
[postgres@slnc7r1513 ~]$

CREATE TABLE npcurren.num_aangesloten_nr
(
    anr_nummer_laag character varying(20) COLLATE pg_catalog."default" NOT NULL,
    anr_nummer_hoog character varying(20) COLLATE pg_catalog."default" NOT NULL,
    fk_eigenaar_nop_int_oper_id character varying(6) COLLATE pg_catalog."default",
    fk_sta_id character varying(1) COLLATE pg_catalog."default",
    fk_sac_id character varying(1) COLLATE pg_catalog."default",
    fk_moeder_hst_spc_id character varying(4) COLLATE pg_catalog."default",
    fk_moeder_cgb_code integer,
    fk_exploit_nop_int_oper_id character varying(6) COLLATE pg_catalog."default",
    fk_dienst_hst_spc_id character varying(4) COLLATE pg_catalog."default",
    fk_dienst_cgb_code integer,
    anr_soort_dienst character varying(2) COLLATE pg_catalog."default",
    anr_roepnummer character varying(20) COLLATE pg_catalog."default",
    anr_lri character varying(24) COLLATE pg_catalog."default",
    anr_logisch_mutatiemoment timestamp without time zone,
    anr_technisch_mutatiemoment timestamp without time zone NOT NULL,
    anr_mutatie_gebruiker_id character varying(8) COLLATE pg_catalog."default" NOT NULL,
    anr_netnummer character varying(4) COLLATE pg_catalog."default",
    anr_heeft_cvi_dienst_jn character varying(1) COLLATE pg_catalog."default",
    fk_num_voip_platform_id smallint,
    anr_heeft_cvi_zm_dienst_yn character varying(1) COLLATE pg_catalog."default",
    anr_cvi_kenmerk character varying(10) COLLATE pg_catalog."default",
    anr_cvi_telco character varying(8) COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT anr_prx PRIMARY KEY (anr_nummer_laag),
    CONSTRAINT sys_c0012384 FOREIGN KEY (fk_sac_id)
        REFERENCES npcurren.num_soort_actie (sac_id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION,
    CONSTRAINT sys_c0012385 FOREIGN KEY (fk_sta_id)
        REFERENCES npcurren.num_status (sta_id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION,
    CONSTRAINT sys_c0012386 FOREIGN KEY (fk_num_voip_platform_id)
        REFERENCES npcurren.num_voip_platform (platform_id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE npcurren.num_aangesloten_nr
    OWNER to postgres;

-- Index: anr_idx1

-- DROP INDEX npcurren.anr_idx1;

CREATE INDEX anr_idx1
    ON npcurren.num_aangesloten_nr USING btree
    (fk_moeder_hst_spc_id COLLATE pg_catalog."default", fk_moeder_cgb_code)
    TABLESPACE pg_default;

-- Index: anr_idx2

-- DROP INDEX npcurren.anr_idx2;

CREATE INDEX anr_idx2
    ON npcurren.num_aangesloten_nr USING btree
    (fk_dienst_hst_spc_id COLLATE pg_catalog."default", fk_dienst_cgb_code)
    TABLESPACE pg_default;

-- Index: anr_idx3

-- DROP INDEX npcurren.anr_idx3;

CREATE INDEX anr_idx3
    ON npcurren.num_aangesloten_nr USING btree
    (fk_sac_id COLLATE pg_catalog."default")
    TABLESPACE pg_default;

-- Index: anr_idx4

-- DROP INDEX npcurren.anr_idx4;

CREATE INDEX anr_idx4
    ON npcurren.num_aangesloten_nr USING btree
    (fk_sta_id COLLATE pg_catalog."default")
    TABLESPACE pg_default;

-- Index: anr_idx5

-- DROP INDEX npcurren.anr_idx5;

CREATE INDEX anr_idx5
    ON npcurren.num_aangesloten_nr USING btree
    (fk_eigenaar_nop_int_oper_id COLLATE pg_catalog."default")
    TABLESPACE pg_default;

-- Index: anr_idx6

-- DROP INDEX npcurren.anr_idx6;

CREATE INDEX anr_idx6
    ON npcurren.num_aangesloten_nr USING btree
    (fk_exploit_nop_int_oper_id COLLATE pg_catalog."default")
    TABLESPACE pg_default;

-- Index: anr_idx7

-- DROP INDEX npcurren.anr_idx7;

CREATE INDEX anr_idx7
    ON npcurren.num_aangesloten_nr USING btree
    (anr_nummer_hoog COLLATE pg_catalog."default")
    TABLESPACE pg_default;

-- Index: anr_idx8

-- DROP INDEX npcurren.anr_idx8;

CREATE INDEX anr_idx8
    ON npcurren.num_aangesloten_nr USING btree
    (anr_netnummer COLLATE pg_catalog."default")
    TABLESPACE pg_default;

-- Index: anr_idx9

-- DROP INDEX npcurren.anr_idx9;

CREATE INDEX anr_idx9
    ON npcurren.num_aangesloten_nr USING btree
    (fk_num_voip_platform_id)
    TABLESPACE pg_default;