NOT IN, IN et DELETE massif
Salut,
indexes et group by étant déjà soulevés, je vois 2 petites choses qui peuvent pénaliser la perf en forte volumétrie :
1- les NOT IN et IN : préférer des joins avec des tests de jointure
2- les DELETE massif (ou UPDATE ou INSERT) : en effet, PG gère les modifs de tables de manière particulière, les lignes ne sont pas physiquement supprimées (ou insérées ou updatées), en fait les lignes concernées sont 'taguées' comme n'appartenant plus à la table (ou du genre, je ne suis pas spé dans le domaine) et de nouvelles lignes sont créées. On ne récupère pas la mémoire avant un VACUUM et d'autres subtilités que je ne comprends pas totalement mais qui pénalisent beaucoup. Je bosse sur des données en très hautre volumétrie : des dizaines de tables de plus 8 millions d'enregistrements avec données spatiales et indexes et touti quanti... Tout ceci n'est qu'un avis, ne sont que des propositions, je ne garantis rien et ne suis pas spécialiste du domaine.
L'objectif si j'ai compris est :
reduire la table 'activite' aux elements dont 'id_activite'
trouve une reference 'idactiv' dans la table 'liaison'
Si tel est le cas :
Code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
|
--Pour tester :
drop table if exists activite;
create table activite
(
id_activite bigint
);
insert into activite values(1),(3),(5),(7);
insert into activite values(2),(4),(6),(8);
insert into activite values(1),(3),(5),(7);
insert into activite values(2),(4),(6),(8);
select * from activite;
drop table if exists liaison_activ_mag;
create table liaison_activ_mag
(
idactiv bigint
);
insert into liaison_activ_mag values(1),(3),(5),(7);
insert into liaison_activ_mag values(1),(3),(5),(7);
select * from liaison_activ_mag;
--Pour résoudre le probleme des NOT IN et IN
--Les NOT IN et IN sont tres couteux, il est preferable d'utiliser
--des tournures avec join et tester la realisation du join
--Recuperation rapide des id a dropper
select foo_activite.id_activite from
(select * from activite) as foo_activite
left join
(select idactiv from liaison_activ_mag) as foo_liaison
on (foo_activite.id_activite = foo_liaison.idactiv)
where foo_liaison.idactiv is null;
--Tournure pour le drop (j'ai pas mieux):
delete from activite using
(
select foo_activite.id_activite from
(select * from activite) as foo_activite
left join
(select idactiv from liaison_activ_mag) as foo_liaison
on (foo_activite.id_activite = foo_liaison.idactiv)
where foo_liaison.idactiv is null
) as to_drop
where activite.id_activite=to_drop.id_activite;
select * from activite;
--Pour résoudre le probleme des delete massif
drop table if exists activite_bis;
create table activite_bis as
select foo_activite.* from
(select * from activite) as foo_activite
left join
(select distinct idactiv from liaison_activ_mag) as foo_liaison
on (foo_activite.id_activite = foo_liaison.idactiv)
where foo_liaison.idactiv is not null;
drop table if exists activite;
create table activite as
select * from activite_bis;
drop table if exists activite_bis;
select * from activite;
/* NOTE IMPORTANTE */
--Il faut remettre pk, fk, unique, indexes |
A propos de la note importante :
on peut se dire qu'il est couteux de recréer une table sur laquelle on va devoir poser des indexes, pk, fk, unique.
En fait c'est une opération moins couteuse que d'insérer des masses de données dans une table soumise à ces indexes, pk, etc.
Le vrai petit hic est :
Code:
drop table if exists activite;
qui nécessite parfois :
Code:
drop table if exists activite CASCADE;
et là c'est déjà plus embettant.
Encore une fois, je ne suis pas spécialiste et ceci est à prendre avec des pincettes et à remettre en question. Toutefois, je suis très intéressé par un retour de votre part. Parmis ces méthodes, lesquelles sont les plus performantes.
De plus, différentes choses intéressante à savoir pour aller plus loin :
Quelle est la volumétrie de vos tables ?
Quelles sont les structures de vos tables ?
Quels sont les contraintes sur ces tables ?
La table 'activite' est-elle référencée par une autre table ?
Cordialement,
PS : Nous donner un retour sur ces différents points et les perfs observées serait super sympa ! Mieux vaut tard que jamais pour découvrir de nouvelles méthodes plus performantes.
