RE: Postgres DB Slowness

<soumik.bhattacharjee@xxxxxxx> · Mon, 26 Aug 2019 10:14:20 +0000

-----Original Message-----

From: Juan José Santamaría Flecha <juanjo.santamaria@xxxxxxxxx> 

Sent: Friday, August 23, 2019 1:08 PM

To: Bhattacharjee, Soumik <soumik.bhattacharjee@xxxxxxx>

Cc: eddy.adarsh@xxxxxxxxx; pgsql-admin@xxxxxxxxxxxxxxxxxxxx

Subject: Re: Postgres DB Slowness

On Fri, Aug 23, 2019 at 10:17 AM <soumik.bhattacharjee@xxxxxxx> 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,

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;