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