Bonjour,

Je cherche des pistes pour optimiser l’exécution d'une requete qui extrait des nœuds xml à l'aide de la fonction xpath

Ma table frm est composé de :
- id, type bigint ( primary key )
- numero, type varchar(12)
- xml, type xml
- dt_creation, type timestamp

Le numero est composé de lettre et de chiffre
Le fichier xml stocké pèse entre 2 et 3ko.
La date de creation est la date d'enregistrement de la ligne en base. ( de 2014 à aujourd'hui )

La table contient 11.5 Millions de lignes.

Le but est de récupérer les valeurs d'une trentaine de noeuds xml, pour toutes les lignes, et de les stocker dans une nouvelle table, avec une colonne par noeud extrait.

Je travaille sur une copie d'une base de production, je peux donc utiliser n'importe quelle instruction et modifier le fichier postgresql.conf si nécessaire.
Ma seule contrainte est le temps de la requête, j'ai environ 10h.

J'ai commencé en tout innocence avec ca :
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
 
DROP TABLE IF EXISTS matable;
CREATE TABLE matable AS (
	SELECT 
array_to_string(xpath('/MY-XML/Branche/Dossier/Lieu/Plafond/P10/text()',xml),'') as P10,
array_to_string(xpath('/MY-XML/Branche/Dossier/Lieu/Plafond/P15/text()',xml),'') as P15,
array_to_string(xpath('/MY-XML/Branche/Dossier/Meuble/Tirroir/T1/text()',xml),'') as T1,
array_to_string(xpath('/MY-XML/Branche/Dossier/Meuble/Tirroir/T2/text()',xml),'') as T2,
array_to_string(xpath('/MY-XML/Branche/Dossier/Meuble/Poignee/PO1/PO1.1/text()',xml),'') as PO11,
array_to_string(xpath('/MY-XML/Branche/Dossier/Meuble/Poigne/PO1/PO1.2/text()',xml),'') as  PO12,
array_to_string(xpath('/MY-XML/Branche/Dossier/Lieu/Plafond/P2/text()',xml),'') as P2,
array_to_string(xpath('/MY-XML/Branche/Dossier/Lieu/Etage/E1/E1.3/text()',xml),'') as E133,
array_to_string(xpath('/MY-XML/Branche/Dossier/Lieu/Etage/E1/E1.4/text()',xml),'') as E134,
array_to_string(xpath('/MY-XML/Branche/Dossier/Lieu/Etage/E1/E1.5/text()',xml),'') as E135,
array_to_string(xpath('/MY-XML/Branche/Dossier/Lieu/Etage/E1/E1.6/text()',xml),'') as E136,
array_to_string(xpath('/MY-XML/Branche/Dossier/Lieu/Etage/E1/E1.7/text()',xml),'') as E137,
array_to_string(xpath('/MY-XML/Branche/Dossier/Lieu/Etage/E1/E1.8/text()',xml),'') as E138,
array_to_string(xpath('/MY-XML/Branche/Dossier/Lieu/Etage/E1/E1.9/text()',xml),'') as E139,
array_to_string(xpath('/MY-XML/Branche/Dossier/Lieu/Etage/E1/E1.10/text()',xml),'') as E110,
array_to_string(xpath('/MY-XML/Branche/Dossier/Lieu/Etage/E1/E1.11/text()',xml),'') as E111,
array_to_string(xpath('/MY-XML/Branche/Dossier/Lieu/Etage/E1/E1.12/text()',xml),'') as E112,
array_to_string(xpath('/MY-XML/Branche/Dossier/Lieu/Etage/E1/E1.13/text()',xml),'') as E113,
array_to_string(xpath('/MY-XML/Branche/Dossier/Lieu/Etage/E1/E1.14/text()',xml),'') as E114,
array_to_string(xpath('/MY-XML/Branche/Dossier/Lieu/Piece/Salle/S3/S31/text()',xml),'') || array_to_string(xpath('/MY-XML/Branche/Dossier/Lieu/Piece/Salle/S3/S32/text()',xml),'') as  S3
array_to_string(xpath('/MY-XML/Branche/Dossier/Lieu/Piece/Salle/S2/text()',xml),'') as S2,
array_to_string(xpath('/MY-XML/Branche/Dossier/Lieu/Piece/Salle/S2/S2.3/text()',xml),'') as S23,
array_to_string(xpath('/MY-XML/Branche/Dossier/Lieu/Piece/Salle/S2/S2.4/text()',xml),'') as S24,
array_to_string(xpath('/MY-XML/Branche/Dossier/Lieu/Piece/Salle/S2/S2.5/text()',xml),'') as S25,
array_to_string(xpath('/MY-XML/Branche/Dossier/Lieu/Piece/Salle/S2/S2.6/text()',xml),'') as S26,
array_to_string(xpath('/MY-XML/Branche/Dossier/Lieu/Piece/Salle/S2/S2.7/text()',xml),'') as S27,
array_to_string(xpath('/MY-XML/Branche/Dossier/Lieu/Piece/Salle/S2/S2.8/text()',xml),'') as S28,
array_to_string(xpath('/MY-XML/Branche/Dossier/Lieu/Piece/Salle/S2/S2.9/text()',xml),'') as S29,
array_to_string(xpath('/MY-XML/Branche/Dossier/Lieu/Piece/Salle/S2/S2.10/text()',xml),'') as S210,
array_to_string(xpath('/MY-XML/Branche/Dossier/Lieu/Piece/Salle/S2/S2.11/text()',xml),'') as S211,
array_to_string(xpath('/MY-XML/Branche/Dossier/Lieu/Piece/Salle/S2/S2.12/text()',xml),'') as S212,
array_to_string(xpath('/MY-XML/Branche/Dossier/Lieu/Piece/Salle/S2/S2.13/text()',xml),'') as S213,
array_to_string(xpath('/MY-XML/Branche/Dossier/Lieu/Piece/Salle/S2/S2.14/text()',xml),'') as S214	
FROM frm;
 
);
J'ai coupé la requete au bout de 15h, elle n'était pas terminée. J'ai filtré sur la date, après l'avoir indexée, pour avoir un quart de la volumétrie attendue : 5h pour 3.2 Millions de liasses.

Je seche un peu sur la manière de l'optimiser.
- Les fonctions array_to_string > xpath sont elles les plus pertinentes pour lire une valeur ?
- Le découpage de la requete d'alimentation en plusieurs requetes serait elle plus efficace ?
--- Plusieurs requetes avec moins de noeud à lire par requete ?
--- Plusieurs requetes avec le meme nombre de noeud mais un filtre sur une colonne ?
- ??

Merci pour votre aide