Performance catastrophique sur un update

[Phil]

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 :

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...

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

Code : Tout sélectionner

CREATE OR REPLACE FUNCTION to_char(valeur smallint)
RETURNS text
LANGUAGE plpgsql
AS $function$
begin
        return valeur::text;
end;
$function$;

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 :

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)

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.