select version();
version
--------------------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 10.2 (Ubuntu 10.2-1.pgdg16.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.4) 5.4.0 20160609, 64-bit
(1 ligne)
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
NOTICE: l'extension " uuid-ossp " existe déjà, poursuite du traitement
CREATE EXTENSION
create table geants_seed(
idgu uuid primary key,
dtn timestamp,
taille smallint,
devise varchar(128),
berserk boolean);
CREATE TABLE
with recursive serie(i) as
(select 1
UNION ALL
select i + 1 from serie where i < 10000000)
insert into geants_seed(idgu, dtn, taille, devise, berserk)
select
uuid_generate_v4(),
current_timestamp - (trunc(random() * 10000 + 1) || ' days')::interval,
200 + (trunc(random() * 200 + 1)),
upper(md5(random()::text)),
case when random() < 0.001 then true else false end
from serie;
INSERT 0 1000000
create table geants_recrues_1(
idgu uuid,
dtn timestamp,
taille smallint,
devise varchar(128),
berserk boolean);
CREATE TABLE
create table geants_recrues_2(
idgu uuid,
dtn timestamp,
taille smallint,
devise varchar(128),
berserk boolean);
CREATE TABLE
create table geants_recrues_3(
idgu uuid,
dtn timestamp,
taille smallint,
devise varchar(128),
berserk boolean);
CREATE TABLE
with serie(idgu) as
(select case when random() <= 0.01 then idgu else uuid_generate_v4() end ,
current_timestamp - (trunc(random() * 10000 + 1) || ' days')::interval,
200 + (trunc(random() * 200 + 1)),
upper(md5(random()::text)),
case when random() < 0.001 then true else false end
from geants_seed TABLESAMPLE SYSTEM(20))
insert into geants_recrues_1
select * from serie;
INSERT 0 1998776
with serie(idgu) as
(select case when random() <= 0.50 then idgu else uuid_generate_v4() end ,
current_timestamp - (trunc(random() * 10000 + 1) || ' days')::interval,
200 + (trunc(random() * 200 + 1)),
upper(md5(random()::text)),
case when random() < 0.001 then true else false end
from geants_seed TABLESAMPLE SYSTEM(20))
insert into geants_recrues_2
select * from serie;
INSERT 0 1994696
with serie(idgu) as
(select case when random() <= 0.99 then idgu else uuid_generate_v4() end ,
current_timestamp - (trunc(random() * 10000 + 1) || ' days')::interval,
200 + (trunc(random() * 200 + 1)),
upper(md5(random()::text)),
case when random() < 0.001 then true else false end
from geants_seed TABLESAMPLE SYSTEM(20))
insert into geants_recrues_3
select * from serie;
INSERT 0 1991792
create table geants as select * from geants_seed;
SELECT 10000000
alter table geants add primary key(idgu);
ALTER TABLE
OK pour l'environnement. Première méthode utilisée, une boucle dans un bloc PL/pgSQL anonyme en variant un peu la manière de détecter et gérer le conflit de clé dupliquée :
do
$$
DECLARE
ligne_geants_recrues geants_recrues_N%rowtype;
begin
for ligne_geants_recrues in select * FROM geants_recrues_N loop
begin
insert into geants values(ligne_geants_recrues.*);
exception
when unique_violation then update geants set devise = ligne_geants_recrues.devise where idgu = ligne_geants_recrues.idgu ;
when others then raise notice '% %', SQLERRM, SQLSTATE;
end;
end loop;
end$$;
DO
-- avec geants_recrues_1 : Durée : 58258,427 ms (00:58,258)
-- avec geants_recrues_2 : Durée : 93298,238 ms (01:33,298)
-- avec geants_recrues_3 : Durée : 119986,162 ms (01:59,986)
do
$$
DECLARE
ligne_geants_recrues geants_recrues_N%rowtype;
begin
for ligne_geants_recrues in select * FROM geants_recrues_N loop
if exists (select 1 from geants where idgu = ligne_geants_recrues.idgu) then
update geants set devise = ligne_geants_recrues.devise where idgu = ligne_geants_recrues.idgu ;
else
insert into geants values(ligne_geants_recrues.*);
end if;
end loop;
exception
when others then raise notice '% %', SQLERRM, SQLSTATE;
end$$;
-- avec geants_recrues_1 : Durée : 64040,260 ms (01:04,040)
-- avec geants_recrues_2 : Durée : 77842,516 ms (01:17,843)
-- avec geants_recrues_3 : Durée : 83794,672 ms (01:23,795)
do
$$
DECLARE
ligne_geants_recrues geants_recrues_N%rowtype;
begin
for ligne_geants_recrues in select * FROM geants_recrues_N loop
update geants set devise = ligne_geants_recrues.devise where idgu = ligne_geants_recrues.idgu ;
if not found then
insert into geants values(ligne_geants_recrues.*);
end if;
end loop;
exception
when others then raise notice '% %', SQLERRM, SQLSTATE;
end$$;
-- avec geants_recrues_1 : Durée : 68440,669 ms (01:08,441)
-- avec geants_recrues_2 : Durée : 67484,379 ms (01:07,484)
-- avec geants_recrues_3 : Durée : 69605,639 ms (01:09,606)
Le bloc donnant les résultats les plus constants, entre 1min08s et 1min10s, est celui dans lequel nous tentons systématiquement la mise à jour avant de passer à l'insertion si aucune ligne n'a été affectée.
explain
with
do_update as
(
update geants g
set devise = (select gra.devise FROM geants_recrues_N gra WHERE g.idgu = gra.idgu)
where exists (select 1 from geants_recrues_N grb where (g.idgu = grb.idgu))
RETURNING g.idgu
)
INSERT INTO geants
SELECT *
FROM geants_recrues_N grc
WHERE NOT EXISTS (SELECT 1
FROM do_update
WHERE do_update.idgu = grc.idgu);
QUERY PLAN
---------------------------------------------------------------------------------------------------------
Insert on geants (cost=95340248432.12..95340288407.64 rows=999388 width=60)
CTE do_update
-> Update on geants g (cost=79398.46..95339528773.32 rows=1998776 width=313)
-> Hash Semi Join (cost=79398.46..95339528773.32 rows=1998776 width=313)
Hash Cond: (g.idgu = grb.idgu)
-> Seq Scan on geants g (cost=0.00..213636.85 rows=9999985 width=33)
-> Hash (cost=42701.76..42701.76 rows=1998776 width=22)
-> Seq Scan on geants_recrues_1 grb (cost=0.00..42701.76 rows=1998776 width=22)
SubPlan 1
-> Seq Scan on geants_recrues_1 gra (cost=0.00..47698.70 rows=1 width=33)
Filter: (g.idgu = idgu)
-> Merge Anti Join (cost=719658.80..759634.32 rows=999388 width=60)
Merge Cond: (grc.idgu = do_update.idgu)
-> Sort (cost=402184.52..407181.46 rows=1998776 width=60)
Sort Key: grc.idgu
-> Seq Scan on geants_recrues_1 grc (cost=0.00..42701.76 rows=1998776 width=60)
-> Sort (cost=317474.28..322471.22 rows=1998776 width=16)
Sort Key: do_update.idgu
-> CTE Scan on do_update (cost=0.00..39975.52 rows=1998776 width=16)
with
do_update as
(
update geants g
set devise = (select gra.devise FROM geants_recrues_N gra WHERE g.idgu = gra.idgu)
where exists (select 1 from geants_recrues_N grb where (g.idgu = grb.idgu))
RETURNING g.idgu
)
INSERT INTO geants
SELECT *
FROM geants_recrues_N grc
WHERE NOT EXISTS (SELECT 1
FROM do_update
WHERE do_update.idgu = grc.idgu);
-- avec geants_recrues_1 : Durée : 3327501,852 ms (55:27,502)
-- avec geants_recrues_2 : + de 3 heures
-- avec geants_recrues_3 : + de 3 heures
explain
with
do_update as
(
update geants g
set devise = gra.devise
from geants_recrues_N gra
WHERE g.idgu = gra.idgu
RETURNING g.idgu
)
INSERT INTO geants
SELECT *
FROM geants_recrues_N grb
WHERE NOT EXISTS (SELECT 1
FROM do_update
WHERE do_update.idgu = grb.idgu);
QUERY PLAN
---------------------------------------------------------------------------------------------------------
Insert on geants (cost=1266260.02..1306235.54 rows=999388 width=60)
CTE do_update
-> Update on geants g (cost=87206.46..546601.22 rows=1998776 width=72)
-> Hash Join (cost=87206.46..546601.22 rows=1998776 width=72)
Hash Cond: (g.idgu = gra.idgu)
-> Seq Scan on geants g (cost=0.00..213637.00 rows=10000000 width=33)
-> Hash (cost=42701.76..42701.76 rows=1998776 width=55)
-> Seq Scan on geants_recrues_N gra (cost=0.00..42701.76 rows=1998776 width=55)
-> Merge Anti Join (cost=719658.80..759634.32 rows=999388 width=60)
Merge Cond: (grb.idgu = do_update.idgu)
-> Sort (cost=402184.52..407181.46 rows=1998776 width=60)
Sort Key: grb.idgu
-> Seq Scan on geants_recrues_N grb (cost=0.00..42701.76 rows=1998776 width=60)
-> Sort (cost=317474.28..322471.22 rows=1998776 width=16)
Sort Key: do_update.idgu
-> CTE Scan on do_update (cost=0.00..39975.52 rows=1998776 width=16)
with
do_update as
(
update geants g
set devise = gra.devise
from geants_recrues_N gra
WHERE g.idgu = gra.idgu
RETURNING g.idgu
)
INSERT INTO geants
SELECT *
FROM geants_recrues_N grb
WHERE NOT EXISTS (SELECT 1
FROM do_update
WHERE do_update.idgu = grb.idgu);
-- avec geants_recrues_1 : Durée : 18218,073 ms (00:18,218)
-- avec geants_recrues_2 : Durée : 118903,792 ms (01:58,904)
-- avec geants_recrues_3 : Durée : 183497,938 ms (03:03,498)
Il est possible d'effectuer du DML avec les CTE mais elles n'ont pas pour vocation d'implémenter l'upsert. La performance est très variable selon la manière d'écrire la CTE et la nature des données mais, globalement, c'est correct avec la syntaxe PostgreSQL pour l'update et catastrophique avec la syntaxe standard SQL.
C'est cette incertitude supposée au niveau des plans d'exécution qui conduit parfois à privilégier une boucle dans un programme externe plutôt que de réaliser le travail en SQL.
Ce n'est cependant pas la meilleure approche pour obtenir un traitement vraiment optimal au niveau des performances lorsqu'il existe une syntaxe dédiée. C'est justement le cas pour l'UPSERT depuis PostgreSQL 9.5 :
explain insert into geants select * from geants_recrues_N on conflict(idgu) do update SET devise = EXCLUDED.devise;
QUERY PLAN
---------------------------------------------------------------------------------
Insert on geants (cost=0.00..42701.76 rows=1998776 width=60)
Conflict Resolution: UPDATE
Conflict Arbiter Indexes: geants_pkey
-> Seq Scan on geants_recrues_N (cost=0.00..42701.76 rows=1998776 width=60)
insert into geants select * from geants_recrues_N on conflict(idgu) do update SET devise = EXCLUDED.devise;
-- avec geants_recrues_1 : Durée : 45664,534 ms (00:45,665)
-- avec geants_recrues_2 : Durée : 51066,180 ms (00:51,066)
-- avec geants_recrues_3 : Durée : 52877,171 ms (00:52,877)
Entre 46s et 53s, la syntaxe dédiée est la plus compacte ET la plus efficace. Elle pourrait de plus être utilisée en environnement transactionnel car elle est sûre au niveau de la concurrence entre sessions.