Discussion :

[xian21]

Bonjour,

J'ai une requete trop lourde a execute du coup je suis meme pas sur qu'elle fonctionne.

J'aurais aime savoir si il etait possible d'ameliorer la requete suivante pour la rendre plus rapide.

Merci

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
DELETE FROM activite WHERE id_activite IN(
	SELECT id_activite FROM activite WHERE id_activite NOT IN (
		SELECT idactiv
		FROM activite A
		INNER JOIN liaison_activ_mag L ON L.idactiv = A.id_activite GROUP BY idactiv
	)
)

En gros je veux effacer toutes les activites dont l'id n'est pas present dans une table annexe.

Merci

[SQLpro]

Commencez par virer votre GROUP BY non seulement il est inutile, mais fait perdre du temps car il force un tri !

récrivez votre requête ainsi :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
 
DELETE FROM activite
WHERE  id_activite IN(SELECT id_activite 
                      FROM   activite 
                      WHERE  id_activite NOT IN (SELECT idactiv
                                                 FROM   activite A
                                                        INNER JOIN liaison_activ_mag L 
                                                              ON L.idactiv = A.id_activite))

ajoutez les index suivants (s'ils n'existent pas) :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
 
CREATE INDEX X1 ON liaison_activ_mag (idactiv)
CREATE INDEX X2 ON activite (id_activite)

tentez cette récriture :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
DELETE FROM activite
WHERE  id_activite IN(SELECT id_activite 
                      FROM   activite AA
                      WHERE  NOT EXISTS(SELECT *
                                        FROM   activite A
                                               INNER JOIN liaison_activ_mag L 
                                                     ON L.idactiv = A.id_activite
                                        WHERE  AA.id_activite  = L.idactiv))

Regardez si cette écriture n'est pas équivalente :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
DELETE FROM activite
WHERE  id_activite NOT IN (SELECT idactiv
                           FROM   activite A
                                  INNER JOIN liaison_activ_mag L 
                                        ON L.idactiv = A.id_activite))

A +

[xian21]

Bonjour,

Merci pour la reponse.

Effectivement pour la derniere ecriture c'est equivalent.
Je m'en etais rendu hier soir mais je n'avais pas eu le temps de repasser le dire sur le forum.

Les index existent bien deja sur ces deux clefs


Bien vu pour le group by j'avais pas vu que je l'avais laisse.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
DELETE FROM activite WHERE id_activite NOT IN (
		SELECT id_activite
		FROM activite A
		INNER JOIN liaison_activ_mag L ON L.idactiv = A.id_activite 	)

[VASAPANCH]

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

drop table if exists activite;

qui nécessite parfois :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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.

[scheu]

Question bête : les stats sur tes tables sont-elles à jour ?
Mets-nous le plan d'exécution de ta requête

[xian21]

Salut,

Merci pour votre aide. (desole du delais de reponse les vacances de noel ... )

Pour la requete suivante

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
DELETE FROM activite WHERE id_activite NOT IN (
		SELECT id_activite
		FROM activite A
		INNER JOIN liaison_activ_mag L ON L.idactiv = A.id_activite 
	)

J'ai un temps d'execution

La requête a été exécutée avec succés : 33449 lignes modifiées. La requête a été exécutée en 451094 ms.

J'avais 60000 lignes a l'origine. Soit un nombre de ligne vraiment pas enorme.

id_activite se retrouve comme clef etrangere dans d'autre table. En virant cette clef etrangere je me suis rendu compte que je gagnais beaucoup de temps de traitement (apers a moi de faire une verification pour pouvoir la repasser en temps que clef etrangere)

Sinon l'explain de la requete

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
"Seq Scan on activite  (cost=10803.76..28955964.51 rows=28850 width=6)"
"  Filter: (NOT (subplan))"
"  SubPlan"
"    ->  Materialize  (cost=10803.76..11662.76 rows=57700 width=4)"
"          ->  Hash Join  (cost=2257.25..10464.06 rows=57700 width=4)"
"                Hash Cond: (l.idactiv = a.id_activite)"
"                ->  Seq Scan on liaison_activ_mag l  (cost=0.00..3783.89 rows=206389 width=4)"
"                ->  Hash  (cost=1254.00..1254.00 rows=57700 width=4)"
"                      ->  Seq Scan on activite a  (cost=0.00..1254.00 rows=57700 width=4)"

*************
Maintenant la meme chose avec la requete propose par vasapanch

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
 
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;

La requête a été exécutée avec succés : 33449 lignes modifiées. La requête a été exécutée en 500 ms.

Je suis impressionne par la difference de temps de traitement...

L'explain:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
 
 
"Hash Join  (cost=14167.76..18701.69 rows=48069 width=6)"
"  Hash Cond: (public.activite.id_activite = public.activite.id_activite)"
"  ->  Seq Scan on activite  (cost=0.00..2089.38 rows=96138 width=10)"
"  ->  Hash  (cost=13331.89..13331.89 rows=48069 width=4)"
"        ->  Hash Left Join  (cost=7371.75..13331.89 rows=48069 width=4)"
"              Hash Cond: (public.activite.id_activite = liaison_activ_mag.idactiv)"
"              Filter: (liaison_activ_mag.idactiv IS NULL)"
"              ->  Seq Scan on activite  (cost=0.00..2089.38 rows=96138 width=4)"
"              ->  Hash  (cost=3783.89..3783.89 rows=206389 width=4)"
"                    ->  Seq Scan on liaison_activ_mag  (cost=0.00..3783.89 rows=206389 width=4)"