Optimiser une requete xpath

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:

---

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

Malheureusement PostgreSQL ne propose pas de méthode d'indexation des chemins des données XML, contrairement à d'autres SGBDR comme SQL Server qui permet d'indexer par VALUE, PATH ou PROPERTY l'ensemble des éléments des documents XML contenus dans une colonne de la table de type XML.
À lire :https://wiki.postgresql.org/wiki/XML_Indexing
La seule chose à faire est de vous débarrasser de ce XML qui n'aurait jamais dû rentrer dans votre base de données, puis que apparemment c'est de la données structurée...

A +