Discussion :

[Nevrosl]

Bonjour,

Je ne connais que depuis peu plpgsql et j'essaye d'executer une requête. Le problème est que ma table contient 17 Millions d'entrées donc cela prend énormement de temps.

Mon but est de supprimer toutes données selon la règle suivante :

Si je trouve une données avec resol=5 ou resol=10
Je dois regarder dans toute la table si des données, avec l'attribut resol=2.5 et que la date et time sont égales à la donnée de resol=2.5, existent, alors j'incrémente mon compteur.

J'ai utilisé des curseurs (peut être pas la meilleure solution), et voici donc la requête que je cherche à optimiser :

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
 
CREATE OR REPLACE FUNCTION cleanCOLOR5 () RETURNS integer  
AS $$
DECLARE 
	c1 CURSOR FOR SELECT * FROM "TABLE";
	gid_record_0	"TABLE"%ROWTYPE;
	gid_record_1	"TABLE"%ROWTYPE;
	count           INTEGER := 0;
	uniqueGid	INTEGER := 0;
BEGIN
    OPEN c1;
    LOOP
	FETCH c1 INTO gid_record_0;
	EXIT WHEN NOT FOUND;
	uniqueGid := 0;
	IF gid_record_0.satel = 5 AND (gid_record_0.resol = 5 OR gid_record_0.resol = 10) AND gid_record_0.mode = 'COLOR' THEN 
		SELECT INTO gid_record_1 gid FROM TABLE as a2 WHERE a2.satel = 5 AND a2.resol = 2.5 AND a2.mode = 'COLOR' AND a2.date_acq_f = gid_record_0.date_acq_f AND a2.time_acq = gid_record_0.time_acq;
		IF FOUND THEN
			uniqueGid := 1;
		END IF;
	END IF;
	IF uniqueGid = 0 THEN
		count := count + 1;
		RAISE NOTICE 'count = %',count;
	END IF;
     END LOOP;
     CLOSE c1;
     RETURN(count);
END;
$$ LANGUAGE plpgsql;
 
SELECT cleanCOLOR5();

Cette requête prend 27 minute de temps d'execution sur une table contenant 80000 données (table de test), donc pour ma table à 17M, j'imagine pas le nombre d'heures :/
Je sais pas si j'ai été clair mais j'espere que cette requête pourra être optimisée, merci

[CinePhil]

Mon but est de supprimer toutes données selon la règle suivante :

Si je trouve une données avec resol=5 ou resol=10
Je dois regarder dans toute la table si des données, avec l'attribut resol=2.5 et que la date et time sont égales à la donnée de resol=2.5, existent, alors j'incrémente mon compteur.

Tu veux supprimer et tu incrémentes un compteur. Euh... quel est le rapport entre les deux ?

Appeler une table "TABLE" est une mauvaise idée parce que c'est mot du langage SQL. En plus, sémantiquement, ça n'apporte rien... sauf si cette table contient des informations sur des tables (nombre de pieds, nombre de places, surface, hauteur... ).

On pourrait avoir la structure de la table en question ?

Quelles sont les lignes dont résol est égal à 5 ou à 10 ?

Code :

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

1
2
3
SELECT identifiant 
FROM la_table
WHERE resol IN (5, 10)

Quelles sont les lignes dont résol est égal à 2,5 ?

Code :

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

1
2
3
SELECT identifiant 
FROM la_table
WHERE resol = 2.5

Une jointure entre les deux pour ne garder que les lignes à résol IN (5, 10) dont les colonnes date et time (encore de mauvais noms de colonnes ! ) sont égales à celles des lignes à résol = 2.5 :

Code :

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

1
2
3
4
5
6
7
SELECT t1.identifiant 
FROM la_table AS t1
INNER JOIN la_table AS t2 
  ON t2.la_date = t1.la_date
  AND t2.le_time = t2.le_time
WHERE t1.resol IN (5, 10)
  AND t2.resol = 2.5

Pour que ce soit performant, il faut indexer les colonnes figurant dans la condition de jointure (la_date et le_time) et éventuellement la colonne resol.

Comme je n'ai pas bien compris ce que tu veux faire (quoi supprimer ?), je ne peux en dire plus pour le moment.
En espérant que ça t'aide.

[Nevrosl]

Merci d'avoir répondu,

Pour répondre à tes questions :

Appeler une table "TABLE" est une mauvaise idée parce que c'est mot du langage SQL. En plus, sémantiquement, ça n'apporte rien... sauf si cette table contient des informations sur des tables (nombre de pieds, nombre de places, surface, hauteur... ).

J'ai appelé ici "TABLE" pour signifier MaTable, bien sûr dans ma base elle ne s'appelle pas ainsi, c'était pour l'example

Tu veux supprimer et tu incrémentes un compteur. Euh... quel est le rapport entre les deux ?

L'idée à terme est la suppression en effet, j'ai mis un compteur pour l'instant pour l'exemple encore une fois, quand ma requête sera optimisée, je remplacerais cette incrémentation par un DELETE dans la table ou un INSERT (dans une autre table peut être moins couteux).

C'est vrai que la règle n'était pas claire, donc l'idée était de supprimer une donnée dont resol=5 si et seulement s'il n'ya pas de données à des dates et time équivalents et dont resol=2.5 dans la même table. S'il n'existe pas une telle donnée, je ne la supprime pas, si je trouve une donnée avec resol=2.5 et des times et date équivents à ma donnée dont resol=5 ALORS je la supprime.


La jointure ici ne convient pas. Les jointures sont fort peu adaptées aux tables avec autant de lignes, sur des tables avec 1000 entrées, il n'y a pas de soucis, essaye une jointure sur une table avec 17 millions d'entrées, tu auras le temps d'en prendre des cafés avant d'obtenir le résultat

Doù l'idée de l'utilisation d'un curseur qui me permet de parcourir une seule fois toute ma table et de regarder pour chaque élément si je trouve des données avec resol=2.5 (le SELECT), je n'ai pas trouvé mieux pour l'instant .

L'indexation sur la résolution me parait une bonne idée pour optimiser le SELECT

[Nevrosl]

L'indexation (+ analyse + vacuum) sur la resol et la date m'a permis de gagner en temps, je suis passé de 25 min à 2 min.

Merci beaucoup

Mission accomplie

[CinePhil]

La jointure ici ne convient pas. Les jointures sont fort peu adaptées aux tables avec autant de lignes, sur des tables avec 1000 entrées, il n'y a pas de soucis, essaye une jointure sur une table avec 17 millions d'entrées, tu auras le temps d'en prendre des cafés avant d'obtenir le résultat

Les SGBD sont particulièrement optimisés pour faire des jointures : c'est leur principal boulot !
J'ai travaillé à l'INRA avec une table de 65 millions de lignes jointe à plusieurs autres tables dont au moins une avait plusieurs dizaines de milliers de lignes et je n'avais pas le temps d'aller boire un café avant d'obtenir le résultat de la requête.
Le préalable est évidemment que la BDD soit correctement modélisée, que le type et la taille des colonnes soit bien choisi, que les tables soient bien indexées... bref, que la BDD ait été correctement conçue.

Doù l'idée de l'utilisation d'un curseur qui me permet de parcourir une seule fois toute ma table et de regarder pour chaque élément si je trouve des données avec resol=2.5 (le SELECT), je n'ai pas trouvé mieux pour l'instant .

Jamais utilisé de curseur mais SQLPro a écrit un article intitulé "Éviter les curseurs".

L'indexation sur la résolution me parait une bonne idée pour optimiser le SELECT

L'indexation est la première chose à vérifier quand une requête rame !
Une bonne indexation peut transformer un SGBD escargot en formule 1.
Voir à ce sujet les articles de SQLPro.
Quoi indexer ?
Maintenance des index dans les VLDB

[Nevrosl]

Les SGBD sont particulièrement optimisés pour faire des jointures : c'est leur principal boulot !
J'ai travaillé à l'INRA avec une table de 65 millions de lignes jointe à plusieurs autres tables dont au moins une avait plusieurs dizaines de milliers de lignes et je n'avais pas le temps d'aller boire un café avant d'obtenir le résultat de la requête.
Le préalable est évidemment que la BDD soit correctement modélisée, que le type et la taille des colonnes soit bien choisi, que les tables soient bien indexées... bref, que la BDD ait été correctement conçue.

Oui tu dois avoir plus d'expérience là dessus que moi. Ca fait que 2 jours que je découvre le langage, j'ai du mal faire la jointure ou justement en y repensant du coup, il aurait fallu créer des index sur les paramètres de jointure (doù probablement la longueur de la mienne). meaculpa

merci pour les articles,j'y jeterais un coup d'oeil