RE: Postgres DB Slowness
От | |
---|---|
Тема | RE: Postgres DB Slowness |
Дата | |
Msg-id | 2A91BEF8171A5349931391E0C721CC535B627ADE@CPEMS-KPN501.KPNCNL.LOCAL обсуждение исходный текст |
Ответ на | Re: Postgres DB Slowness (Juan José Santamaría Flecha <juanjo.santamaria@gmail.com>) |
Список | pgsql-admin |
-----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,
Вложения
В списке pgsql-admin по дате отправления:
Предыдущее
От: Luca FerrariДата:
Сообщение: Re: Postgres PITR: Recovery does not seem to fetch from Archive Dir