Utiliser une liste de valeurs dans une sous-requête

**sagopa** · 27/04/2012, 12h33

Bonjour,

J'ai créée une procédure stockée qui d'abord sélectionne une liste d'id.
Ensuite en parcourant cette liste je fait des suppressions dans des tables différentes:

SELECT le_id INTO listLeId FROM ... WHERE ...

FOR myId IN SELECT listLeId
LOOP

DELETE FROM A WHERE li_le_id = myId;

DELETE FROM B WHERE lv_le_id = myId;

DELETE FROM C WHERE le_id = myId;

END LOOP;

J'ai stocké le résultat de ma première requête dans une "variable" afin d'éviter de l'exécuter 3 fois.
Cependant, la suppression ligne par ligne prend beaucoup de temps.

Je ne connais pas les différentes méthodes en PL/SQL ; voyez-vous une solution pour me guider svp.

Note: j'utilise PostgreSQL 8.3

Merci par avance.

**punkoff** · 27/04/2012, 13h09

bonjour,

Alors vous utilisez le langage SQL qui est un langage ensembliste.

Là vous faites un traitement itératif => c'est lent !

A moins d'avoir des raisons sépcifique pour faire un tel traitement, revoyez votre procedure pour ne faire que 3 requêtes de type DELETE. (et enlevez cette boucle !)

**sagopa** · 27/04/2012, 13h48

En effet, c'est plus lent.

Mais dans ce cas je vais devoir faire 3 requêtes ayant une sous-requête identique.

N'est il pas possible d'éviter cela en stockant le résultat dans une sorte de variable afin d'avoir quelque chose comme ça:

MyReq = SELECT le_id INTO listLeId FROM ... WHERE ...

DELETE FROM A WHERE li_le_id IN (MyReq);
DELETE FROM B WHERE lv_le_id IN (MyReq);
DELETE FROM C WHERE le_id IN (MyReq);

**punkoff** · 27/04/2012, 14h02

il faudrait stocker ce résultat dans une table temporaire si vous ne voulez pas ré-exécuter la sous-requête.

Toute autre approche impliquera un traitement itératif => lent

Concernant la table temporaire :
- faire un vacuum analyze de la table temporaire avant requêtage.
- positionner des index sur cette table temporaire.

Au final, je suis pas sur que ça en vaille le coup.

Quelle est votre requête select, ainsi que vos requête delete ?

edit: pour aller plus loin, typiquement ce genre de traitement induit un test d’existence. Ceci est optimisé au niveau du sgbd et vouloir le contourner avec un mécanisme autre ne peut être bon au niveau perf

**sagopa** · 27/04/2012, 15h07

L'exécution de ces requêtes prend plus de 30 min!!

Voici le code:

CREATE OR REPLACE FUNCTION clearLogs
(
x_airlockName CHARACTER VARYING,
x_stopDateTime TIMESTAMP
)
RETURNS BOOLEAN AS $$
DECLARE

listEventsID int4[] := '{}';
listEventsGroupsID int4[] := '{}';
eventID log_events.le_id%TYPE;
eventsGroupID log_events.le_leg_id%TYPE;

BEGIN

-- delete images associated to the events to delete
DELETE FROM log_images
WHERE li_le_id IN (
SELECT le_id
FROM airlocks INNER JOIN log_events ON a_id = le_a_id
LEFT OUTER JOIN log_events_groups ON le_leg_id = leg_id
WHERE le_datetime < x_stopDateTime
AND a_name = x_airlockName
AND le_do_not_delete = false
AND (le_leg_id IS NULL
-- we keep the group event if at least 1 event should not be deleted
-- or at least 1 event is too recent according to the stop date time
OR le_leg_id NOT IN (
SELECT DISTINCT le_leg_id FROM log_events
WHERE le_leg_id IS NOT NULL
AND (le_do_not_delete = true OR le_datetime >= x_stopDateTime)
)
)
);

-- then delete videos associated to the events to delete
DELETE FROM log_videos
WHERE lv_le_id IN (
SELECT le_id
FROM airlocks INNER JOIN log_events ON a_id = le_a_id
LEFT OUTER JOIN log_events_groups ON le_leg_id = leg_id
WHERE le_datetime < x_stopDateTime
AND a_name = x_airlockName
AND le_do_not_delete = false
AND (le_leg_id IS NULL
-- we keep the group event if at least 1 event should not be deleted
-- or at least 1 event is too recent according to the stop date time
OR le_leg_id NOT IN (
SELECT DISTINCT le_leg_id FROM log_events
WHERE le_leg_id IS NOT NULL
AND (le_do_not_delete = true OR le_datetime >= x_stopDateTime)
)
)
);

-- then delete the events
DELETE FROM log_events
WHERE le_id IN (
SELECT le_id
FROM airlocks INNER JOIN log_events ON a_id = le_a_id
LEFT OUTER JOIN log_events_groups ON le_leg_id = leg_id
WHERE le_datetime < x_stopDateTime
AND a_name = x_airlockName
AND le_do_not_delete = false
AND (le_leg_id IS NULL
-- we keep the group event if at least 1 event should not be deleted
-- or at least 1 event is too recent according to the stop date time
OR le_leg_id NOT IN (
SELECT DISTINCT le_leg_id FROM log_events
WHERE le_leg_id IS NOT NULL
AND (le_do_not_delete = true OR le_datetime >= x_stopDateTime)
)
)
);

-- finally delete the groups events
DELETE FROM log_events_groups
WHERE leg_id IN (
SELECT le_leg_id
FROM airlocks INNER JOIN log_events ON a_id = le_a_id
LEFT OUTER JOIN log_events_groups ON le_leg_id = leg_id
WHERE le_datetime < x_stopDateTime
AND a_name = x_airlockName
AND le_do_not_delete = false
AND (le_leg_id IS NULL
-- we keep the group event if at least 1 event should not be deleted
-- or at least 1 event is too recent according to the stop date time
OR le_leg_id NOT IN (
SELECT DISTINCT le_leg_id FROM log_events
WHERE le_leg_id IS NOT NULL
AND (le_do_not_delete = true OR le_datetime >= x_stopDateTime)
)
)
);

RETURN true;

END;
$$ LANGUAGE plpgsql;

**punkoff** · 27/04/2012, 15h12

Pour des problèmes d'optimisations, il faut :
- la structure des tables + index
- que vous expliquiez le plus simplement possible votre besoin (afin que l'on puisse vérifier que la syntaxe des requêtes sql que vous utilisez est la bonne)
- le plan d'execution de vos requête (explain analyze, et ceci il le faudrait sur la requete select uniqueument et la requete entiere avec le delete)

edit : sinon un exemple d'utilisation des tables temporaires :

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
 
do $$
declare 
val text;
curs1 cursor for explain analyze select * from t_temp where id between 2 and 1000;
 
begin
create temporary table t_temp (id int) on commit drop;
 
insert into t_temp (select generate_series(1, 10000));
create index idx_t on t_temp (id);
 
 
analyze t_temp;
 
for val in curs1 loop
raise notice '%', val;
end loop;
 
end $$;

**sagopa** · 27/04/2012, 15h24

Voici la structure des tables:

* clé primaire
# clé étrangère
@ index

airlocks(*a_id, a_name)

log_events(*le_id, #le_a_id, #le_leg_id, @le_datetime, le_do_not_delete, ...)

log_images(*li_id, #li_le_id, ...)

log_videos(*lv_id, #lv_le_id, ...)

log_events_groups(*leg_id, #leg_a_id, leg_start_time, leg_stop_time, ...)

Besoin:
Ces tables contiennent des groupes d'événements avec des images/vidéos. L'idée est de supprimer pour un nom de "airlock" donné, tous les évènements et leur groupes datant d'avant la date donnée.
Une autre contrainte est de ne pas supprimer ceux qui sont spécifiés avec le_do_not_delete = true.
De plus, on ne supprime pas les groupes qui possèdent au moins un évènement ne devant jamais être supprimé.

J'espère que j'ai assez été clair. Merci pour votre aide.

**punkoff** · 27/04/2012, 16h15

est-ce que vos foreign key sont indéxées ?

(et manquera les explain analyze)

sinon concernant la sous-requete en elle-même :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
SELECT le_id
FROM airlocks INNER JOIN log_events ON a_id = le_a_id
LEFT OUTER JOIN log_events_groups ON le_leg_id = leg_id
WHERE le_datetime < x_stopDateTime
AND a_name = x_airlockName
AND le_do_not_delete = false
AND (le_leg_id IS NULL
-- we keep the group event if at least 1 event should not be deleted
-- or at least 1 event is too recent according to the stop date time
OR le_leg_id NOT IN (
SELECT DISTINCT le_leg_id FROM log_events
WHERE le_leg_id IS NOT NULL
AND (le_do_not_delete = true OR le_datetime >= x_stopDateTime)))

- le left outer join est inutil, vous n'utilisez pas de colonne sur log_events_groups.
- est-ce qu'un log_event peut être créé sasn être associé à un log_events_groups ? si, non, le test is null est de trop.
- avez-vous essayez de remplacer le not in par un not exists ? (ça dégagerai le distinct et pg devrait utiliser une anti-jointure pour traiter le problème)

**sagopa** · 27/04/2012, 16h20

Je pensais que les clés étrangères sont indexées par défaut, voici comment je définis une clé étrangère:

Code :

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

1
2
3
4
ALTER TABLE LOG_EVENTS ADD 
     CONSTRAINT LOG_EVENTS_LE_A_ID_FKEY
          FOREIGN KEY (LE_A_ID)
               REFERENCES AIRLOCKS (A_ID);

Les "Explain Analyze" prend beaucoup de temps (ce n'est pas encore terminé)
Mais je pourrais envoyer le "explain" qui prend largement moins de temps...

**punkoff** · 27/04/2012, 16h35

nope, pas d'index créé automatiquement sur les fk.

Créez les, ca devrait déjà bien booster les perfs.

Après on verra si ca ne suffit pas.

**sagopa** · 27/04/2012, 17h37

Je n'ai malheureusement pas de changements en ajoutant les index sur les clés étrangères.

Voici une requête (que j'ai simplifié en plus) qui après 30min d'exécution ne se termine toujours pas:

Code :

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

1
2
3
4
5
6
7
DELETE FROM log_images
        USING log_events, airlocks
        WHERE le_datetime < '2011-10-25 14:29:30'
        AND le_id = li_le_id
        AND a_id = le_a_id
	AND a_name = 'blabla'
        AND le_do_not_delete = false

Voici les index que j'ai ajouté:

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
CREATE INDEX IDX_LE_DATETIME
	ON LOG_EVENTS (LE_DATETIME DESC NULLS LAST)
	;
 
CREATE INDEX IDX_FK_LE_A_ID
	ON LOG_EVENTS (LE_A_ID DESC NULLS LAST)
	;
 
CREATE INDEX IDX_FK_LE_LEG_A_ID
	ON LOG_EVENTS (LE_LEG_ID DESC NULLS LAST)
	;
 
CREATE INDEX IDX_FK_LI_LE_ID
	ON LOG_IMAGES (LI_LE_ID DESC NULLS LAST)
	;
 
CREATE INDEX IDX_FK_LV_LE_ID
	ON LOG_VIDEOS (LV_LE_ID DESC NULLS LAST)
	;

Voici le résultat du explain (sans analyze car prends beaucoup de temps):

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
 
Nested Loop  (cost=0.00..104934766268.65 rows=2496 width=92)
 
  Join Filter: (public.log_events.le_a_id = airlocks.a_id)
 
  ->  Seq Scan on airlocks  (cost=0.00..1.01 rows=1 width=4)
 
        Filter: ((a_name)::text = 'Oberthur Sittard'::text)
 
  ->  Nested Loop  (cost=0.00..104934766236.44 rows=2496 width=96)
 
        ->  Nested Loop  (cost=0.00..104934748319.11 rows=2496 width=12)
 
              ->  Seq Scan on log_images  (cost=0.00..104934681745.71 rows=10275 width=4)
 
                    Filter: ((subplan) = 1::bigint)
 
                    SubPlan
 
                      ->  Aggregate  (cost=51060.74..51060.75 rows=1 width=0)
 
                            ->  Seq Scan on log_images  (cost=0.00..51060.68 rows=23 width=0)
 
                                  Filter: (li_le_id = $0)
 
              ->  Index Scan using log_events_pkey on log_events  (cost=0.00..6.47 rows=1 width=8)
 
                    Index Cond: (public.log_events.le_id = public.log_images.li_le_id)
 
                    Filter: ((NOT public.log_events.le_do_not_delete) AND (public.log_events.le_datetime < '2011-10-25 14:29:30'::timestamp without time zone))
 
        ->  Index Scan using log_events_pkey on log_events  (cost=0.00..7.17 rows=1 width=92)
 
              Index Cond: (public.log_events.le_id = public.log_images.li_le_id)
 
Hash Join  (cost=35357.19..133009.60 rows=499180 width=6)
 
  Hash Cond: (log_images.li_le_id = log_events.le_id)
 
  ->  Seq Scan on log_images  (cost=0.00..45922.94 rows=2055094 width=10)
 
  ->  Hash  (cost=31247.84..31247.84 rows=250428 width=4)
 
        ->  Nested Loop  (cost=0.00..31247.84 rows=250428 width=4)
 
              Join Filter: (log_events.le_a_id = airlocks.a_id)
 
              ->  Seq Scan on airlocks  (cost=0.00..1.01 rows=1 width=4)
 
                    Filter: ((a_name)::text = 'Oberthur Sittard'::text)
 
              ->  Seq Scan on log_events  (cost=0.00..28116.47 rows=250428 width=8)
 
                    Filter: ((NOT log_events.le_do_not_delete) AND (log_events.le_datetime < '2011-10-25 14:29:30'::timestamp without time zone))

Je peux voir que le coût total est de 104934766268 ; les index sur les clés étrangères n'ont l'air de pas influer (ou alors j'ai fait une erreur lors de leur création)

**punkoff** · 27/04/2012, 22h08

Pourriez-vous tester ceci dans votre proc ?

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
 
begin
create temporary table t_temp_event as (
SELECT le_id
FROM airlocks a
INNER JOIN log_events le ON a.a_id = le.le_a_id
WHERE le.le_datetime < x_stopDateTime AND a.a_name = x_airlockName AND le.le_do_not_delete = false
AND le.le_leg_id IS NULL
-- we keep the group event if at least 1 event should not be deleted
-- or at least 1 event is too recent according to the stop date time
OR not exists (
SELECT null 
FROM log_events le_b
WHERE le.le_leg_id = le_b.le_leg_id AND (le_b.le_do_not_delete = true OR le_b.le_datetime >= x_stopDateTime))) on commit drop;
 
create index idx_temp_event_1 on t_temp_event (le_id);
analyze t_temp_event;
 
delete from log_images a 
where exists (select null from t_temp_event b where a.li_le_id = b.le_id);
 
end;

vérifiez avant que vous avez un index sur log_image.li_le_id et log_events.le_leg_id, je ne suis pas sur qu'il les utilise si vous deleter trop d'enregistrement d'un coup ceci dit. (c'est pas forcément un mal)

**sagopa** · 30/04/2012, 11h08

Merci beaucoup c'est résolu. J'avais mal fait les index sur les clés étrangères!

C'est impressionnant la différence au niveau de la vitesse.

Du coup je n'ai pas testé la table temporaire.

Merci encore!

Utiliser une liste de valeurs dans une sous-requête

Requêtes PostgreSQL

Discussions similaires

Partager

Partager