Performance catastrophique sur un update
Posté : sam. 15 juin 2019 21:27
Merci à Julien pour sa question :
"J'ai des performances catastrophiques sur un update d'une table depuis une autre table. En remplaçant les vraies tables par la table lancers du site ça donnerait ça :
Pourquoi est-ce aussi long ?"
Réponse 1 :
Au vu du coût annoncé, le planner a tout à fait conscience que ce sera TRÈS long à l'exécution. PostgreSQL ne peut donc pas exécuter dans de bonnes conditions cet update pourtant simple.
La fonction to_char(smallint) n'est pas une fonction fournie par défaut avec PostgreSQL, est-ce une fonction ad-hoc créée par l'applicatif ?
Merci à Julien pour son suivi :
"Oui. La fonction to_char a été écrite pour la double compatibilité avec Oracle. Voici sa définition :"
La fonction semble efficace, elle transforme juste un smallint en text...
Réponse 2 :
Les fonctions sont marquées VOLATILE par défaut lorsque rien n'est précisé. Si une fonction est en fait STABLE ou IMMUTABLE, il FAUT le préciser. Ici, cettte fonction to_char(smallint) est une simple conversion de type. Elle peut être marquée IMMUTABLE. Cela donne :
Le planner prévoit un plan bien plus efficace et l'exécution s'achève en effet en un peu plus d'1 minute.
"J'ai des performances catastrophiques sur un update d'une table depuis une autre table. En remplaçant les vraies tables par la table lancers du site ça donnerait ça :
Code : Tout sélectionner
create table lancers (idge smallint, idcl smallint, dtl timestamp, perf integer );
CREATE TABLE
insert into lancers(dtl, idge, idcl, perf)
with recursive serie(i, r) as
(select 1, trunc(random() * 1000 + 1)
UNION ALL
select i + 1, trunc(random() * 1000 + 1) from serie where i < 10000000) select clock_timestamp() - ((i || ' minutes')::interval)::interval, r, mod(r::int, 100), trunc(random() * 100000 + 1) from serie;
create table lancers_corrige (idl text, perf integer );
insert into lancers_corrige(idl, perf)
select idge::text||idcl || to_char(dtl, 'YYYYMMDDHH24MISS'), perf*random() from lancers where random() < 0.3;
explain update lancers l set perf = lc.perf from lancers_corrige lc where lc.idl = concat(to_char(l.idge), to_char(l.idcl), to_char(dtl, 'YYYYMMDDHH24MISS'));
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
Update on lancers l (cost=0.00..15785819356931.38 rows=10000115 width=28)
-> Nested Loop (cost=0.00..15785819356931.38 rows=10000115 width=28)
Join Filter: (lc.idl = concat(to_char(l.idge), to_char(l.idcl), to_char(l.dtl, 'YYYYMMDDHH24MISS'::text)))
-> Seq Scan on lancers_corrige lc (cost=0.00..49139.74 rows=3001874 width=29)
-> Materialize (cost=0.00..272291.73 rows=10000115 width=18)
-> Seq Scan on lancers l (cost=0.00..163696.15 rows=10000115 width=18)
update lancers l set perf = lc.perf from lancers_corrige lc where lc.idl = concat(to_char(l.idge), to_char(l.idcl), to_char(dtl, 'YYYYMMDDHH24MISS'));
...break au bout de 20 minutes...
Réponse 1 :
Au vu du coût annoncé, le planner a tout à fait conscience que ce sera TRÈS long à l'exécution. PostgreSQL ne peut donc pas exécuter dans de bonnes conditions cet update pourtant simple.
La fonction to_char(smallint) n'est pas une fonction fournie par défaut avec PostgreSQL, est-ce une fonction ad-hoc créée par l'applicatif ?
Merci à Julien pour son suivi :
"Oui. La fonction to_char a été écrite pour la double compatibilité avec Oracle. Voici sa définition :"
Code : Tout sélectionner
CREATE OR REPLACE FUNCTION to_char(valeur smallint)
RETURNS text
LANGUAGE plpgsql
AS $function$
begin
return valeur::text;
end;
$function$;
Réponse 2 :
Les fonctions sont marquées VOLATILE par défaut lorsque rien n'est précisé. Si une fonction est en fait STABLE ou IMMUTABLE, il FAUT le préciser. Ici, cettte fonction to_char(smallint) est une simple conversion de type. Elle peut être marquée IMMUTABLE. Cela donne :
Code : Tout sélectionner
CREATE OR REPLACE FUNCTION to_char(valeur smallint)
RETURNS text
IMMUTABLE PARALLEL SAFE STRICT
LANGUAGE plpgsql
AS $function$
begin
return valeur::text;
end;
$function$;
explain update lancers l set perf = lc.perf from lancers_corrige lc where lc.idl = concat(to_char(l.idge), to_char(l.idcl), to_char(dtl, 'YYYYMMDDHH24MISS'));
QUERY PLAN
------------------------------------------------------------------------------------------------------------------
Update on lancers l (cost=107184.17..8146180.30 rows=10000115 width=28)
-> Hash Join (cost=107184.17..8146180.30 rows=10000115 width=28)
Hash Cond: (concat(to_char(l.idge), to_char(l.idcl), to_char(l.dtl, 'YYYYMMDDHH24MISS'::text)) = lc.idl)
-> Seq Scan on lancers l (cost=0.00..163696.15 rows=10000115 width=18)
-> Hash (cost=49139.74..49139.74 rows=3001874 width=29)
-> Seq Scan on lancers_corrige lc (cost=0.00..49139.74 rows=3001874 width=29)
update lancers l set perf = lc.perf from lancers_corrige lc where lc.idl = concat(to_char(l.idge), to_char(l.idcl), to_char(dtl, 'YYYYMMDDHH24MISS'));
UPDATE 3001875
Durée : 68804,763 ms (01:08,805)