Forums PostgreSQL.fr

yoyostras

Membre

Requête à optimiser

Bonjour,
j'ai une table qu'on va appeler TEMP avec les champs suivants :

numero |     dat      |       datrecu       | ch1 | ... | champ16

Il y a un index sur (numero,dat) mais pas de clé primaire.

Cette table est utilisée pour servir d'intermédiaire avant de charger les données dans une table équivalente qu'on appellera T et sur laquelle j'ai une PK sur (numero,dat).

Pour résumer je dois insérer des données d'un fichier dans T mais je passe par TEMP car j'ai des doublons (numero,dat).

Parmi ces doublons, je sais que pour l'une des deux lignes j'ai les champs ch5 et ch6 NOT NULL et les autres champs NULL. Je veux supprimer dans TEMP ces lignes-là pour ne plus avoir de doublons et insérer les données dans T.

Je fais donc la requête suivante en testant avec "select *" plutôt qu'avec delete (le but étant de faire un delete des lignes obtenues) :
select * from TEMP
where (ch1 is null and ch2 is null and ch3 is null and ch4 is null and ch5 is not null and ch6 is not null and ch7 is null and ch8 is null and ch9 is null and ch10 is null
and ch11 is null and ch12 is null and ch13 is null and ch14 is null and ch15 is null and ch16 is null)
and (numero,dat) in (select numero,dat from TEMP group by numero,dat having (count(*)>1));

Au bout de 72h, je n'ai toujours rien....

J'ai donc lancé la requête suivante :
select * from (
select * from TEMP where (numero,dat) in (select numero,dat from TEMP group by numero,dat having (count(*)>1)
intersect
(select numero,dat from TEMP where (ch1 is null and ch2 is null and ch3 is null and ch4 is null and ch5 is not null and ch6 is not null
and ch7 is null and ch8 is null and ch9 is null and ch10 is null and ch11 is null and ch12 is null and ch13 is null and ch14 is null and ch15 is null and ch16 is null)))
) as toto
where (ch1 is null and ch2 is null and ch3 is null and ch4 is null and ch5 is not null and ch6 is not null
and ch7 is null and ch8 is null and ch9 is null and ch10 is null and ch11 is null and ch12 is null and ch13 is null and ch14 is null and ch15 is null and ch16 is null);

Celle-ci met 85s et j'obtiens bien ce que je veux.

Question : pourquoi la première requête pédale?

yoyostras

Membre

Re : Requête à optimiser

Une précision :

Je sais que la sous-requête "select numero,dat from TEMP group by numero,dat having (count(*)>1)" renvoie 8149 lignes, donc si je la remplace par la sous-requête
"select numero,dat from TEMP group by numero,dat having (count(*)>1 limit 9000)", là j'ai une réponse au bout de 50,247 secondes...

gleu

Administrateur

Re : Requête à optimiser

Vu qu'il s'agit de jouer aux devinettes, je dirais qu'exécuter X fois "select numero,dat from TEMP group by numero,dat having (count(*)>1)" doit y être pour quelque chose (X étant égal au nombre de lignes dans TEMP).

Quant au deuxième commentaire, j'aurais tendance à penser que le LIMIT facilite l'utilisation d'un index, ce qui améliore les performances de la requête. Et que s'il ne prend pas l'index par défaut, c'est que vos statistiques ne sont soit pas à jour soit pas représentatives.

Mais bon, vu le peu d'informations, je ne fais que jouer aux devinettes. Il aurait été bien sympa de fournir les plans d'exécutions des requêtes.

---

Guillaume.

alassanediakite

Membre

Re : Requête à optimiser

Salut à vous.
gleu, si la sous-requête "select numero,dat from TEMP group by numero,dat having (count(*)>1)" n'est pas corrélée pourquoi est-elle exécutée X fois?
@+

gleu

Administrateur

Re : Requête à optimiser

Pas faux, il est possible qu'elle ne soit exécutée qu'une seule fois. Le seul moyen de le savoir est de récupérer le plan d'exécution.

---

Guillaume.

yoyostras

Membre

Re : Requête à optimiser

Voici donc le plan d'exécution de cette requête :

explain analyze select * from TEMP
where (ch1 is null and ch2 is null and ch3 is null and ch4 is null and ch5 is not null and ch6 is not null and ch7 is null and ch8 is null and ch9 is null and ch10 is null
and ch11 is null and ch12 is null and ch13 is null and ch14 is null and ch15 is null and ch16 is null)
and (numero,dat) in (select numero,dat from TEMP group by numero,dat having (count(*)>1));
------------------------------------------------------------------------------------------------------
Nested Loop  (cost=0.00..1925561.92 rows=1 width=123) (actual time=34754.943..966952296.369 rows=7277 loops=1)
   Join Filter: ((TEMP.numero = TEMP.numero) AND (TEMP.dat = TEMP.dat))
   ->  Seq Scan on TEMP  (cost=0.00..482731.76 rows=1 width=123) (actual time=60.574..40028.057 rows=20979 loops=1)
         Filter: ((ch1 IS NULL) AND (ch2 IS NULL) AND (ch3 IS NULL) AND (ch4 IS NULL) AND (ch5 IS NOT NULL) AND (ch6 IS NOT NULL) AND (ch7 IS NULL) AND (ch8 IS NULL) AND (ch9 IS NULL) AND (ch10 IS NULL) AND (ch11 IS NULL) AND (ch12 IS NULL) AND (ch13 IS NULL) AND (ch14 IS NULL) AND (ch15 IS NULL) AND (ch16 IS NULL))
   ->  GroupAggregate  (cost=0.00..1398772.46 rows=1762308 width=14) (actual time=12.024..46079.740 rows=8326 loops=20979)
         Filter: (count(*) > 1)
         ->  Index Scan using idx_temp on TEMP  (cost=0.00..1244570.54 rows=17623076 width=14) (actual time=3.980..36479.630 rows=17623075 loops=20979)
Total runtime: 966955605.121 ms
(8 lignes)

Temps : 966955826,462 ms

Marc Cousin

Membre

Re : Requête à optimiser

Bonjour,

Si temp est bien une table temporaire (CREATE TEMP TABLE), elle n'a aucune stat. Ce qui expliquerait qu'il estime que vos conditions sur les null ramène 1 enregistrement, alors qu'il en ramène 21000. Essayez un ANALYZE temp; puis relancez l'explain analyze.

Sinon, le fait qu'il y ait des tests sur 16 colonnes ne va pas aider non plus, ça devient très dur pour Postgres d'avoir une estimation correcte du nombre d'enregistrement vérifiant tous ces tests.

---

Marc.

yoyostras

Membre

Re : Requête à optimiser

TEMP n'est pas une table temporaire, je l'ai nomméé ici comme cela pour plus de simplicité ; je pense que ce sont les tests sur les 16 colonnes qui ralentissent ; mais à ce point-là je pensais pas...

Marc Cousin

Membre

Re : Requête à optimiser

Non, ce ne sont pas les tests sur les colonnes qui ralentissent. C'est la présence de tant de tests différents qui fait que postgres pense que ça ne ramènera qu'un seul enregistrement de la table test, alors que ça en ramène 20000. Ce qui fait qu'il choisit un mauvais plan d'exécution.

---

Marc.

SQLpro

Membre

Re : Requête à optimiser

Vous avez visiblement un problème de modélisation.
1) vous n'avez pas de PK
2) Vous ne respectez pas la première forme normale par extension (répétition de groupe de colonnes).

Si vous aviez mis une PK à votre table, comme ceci :

ALTER TABLE TEMP ADD PKI SERIAL NOT NULL;
ALTER TABLE TEMP ADD CONSTRAINT PK_TEMP PRIMARY KEY (PKI);

et mis vos 16 colonnes Ch1 à Ch16 dans une seule table fille de cette manière :

CREATE TABLE TEMP_COL
(PKI             INT NOT NULL ,
 NUM_COL         SMALLINT NOT NULL,
 VAL_COL         INT,
 CONSTRAINT PK_TEMP_COL PRIMARY KEY (TEMP_COL, NUM_COL));

Alors vous auriez eu un modèle normalisé, c'est à dire une vraie base de données et vos temps de réponse auraient été instantanés, grâce à cette requête :

SELECT *
FROM   TEMP AS T
WHERE  NOT EXISTS(SELECT *
                  FROM   TEMP_COL AS TC
                  WHERE  T.PKI = TC.PKI
                    AND  NUM_COL BETWEEN 1 AND 16);

En sus le volume globale de votre base aurait été moindre ! En effet, stocker du NULL coute !!!

A +

---

Frédéric Brouard, alias SQLpro,  ARCHITECTE DE DONNÉES,  Expert langage SQL
Le site sur les SGBD relationnel et langage SQL   : http://sqlpro.developpez.com/
Modélisation de données, conseil, expertise, audit, optimisation, tuning, formation
* * * * *  Enseignant CNAM PACA, ISEN Toulon,  CESI Aix en Provence  * * * * *