Discussion :

[Coco47]

Bonjour à tous,

Comme je l'ai dit lors de mes précédents postes je débute en sql et si j'ai bien compris un truc c'est que j'ai pas encore la logique du sql et donc mes requêtes sont loin d'être optimisées .

Donc là j'ai une requête qui prende pret de 20min à s'éxécutée et je voudrais avoir si d'après il y aurait des astuces pour l'optimiser ?

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
select ft.id_metier_, pm.id_regleme as pm,b.id_regleme as pa,ft.code_com,ft.utilisation,ft.etat,ft.calc_charg,ft.poi,ta.util,ta.com,c.ref_cable,ap.result_etu
from ftth_site_appui_ft ft
inner join ftth_zone_eligibilite z on st_intersects(z.geom,ft.geom)
inner join ftth_pf b on b.id_metier_=z.id_metier_
left join (select id_metier_, id_regleme
	from ftth_pf pm
	where type_pf ='PMZ' or pm.type_pf = 'PA') pm on pm.id_metier_ like concat('%',b.nom_nro,'/',b.type_pf_pe,'/',cast(b.num_ordre_ as bigint))
left join ftth_cable c on st_dwithin(ft.geom,c.geom,0.2)
left join test_appuis ta on nom_appui=ft.id_metier_ 
left join apcom_poteaux ap on ap.id_geofibre = ft.id_metier_
where c.ref_cable Is Null and z.id_metier_ like '%PA%'
order by ft.id_metier_

Pour décrire un peu ce que j'y fais :
Je récupère des points sur une carte (ft)
Je les joints a des zones géographiques (z) pour récupérer le point principale de la zone (z.id_metier_)
Je joins alors le point principal (z.id_metier_) trouvé avec la liste des points principaux (pm) pour y récupéré certaines infos
Je joins enfin des câbles avec mes points du départ pour trouver les points qui sont dans la zone mais pas sur ces câbles
Je joins enfin une autre table (ta) pour récupérer d'autres infos (ta.util,ta.com)
Je joins encore une autre table (ap) pour récupérer d'autres infos (ap.result_etu)

Voilà je sais pas si vous auriez besoin d'autres infos pour me donner quelques conseils ?

[manzeki]

Pourquoi vous castez num_ordre en bigint pour faire une concaténation
L'usage des like plombe en premier vos requêtes, comme dans votre post précédent, un jocker % en début d'expression exclue l'utilisation d'un index
MySQL risque de scanner toute la table pour trouver tout ce qui ressemble à '%PA%'
Posez des index sur les colonnes concernées par les jointures, conditions et tris (order by) : geom, type_pf, ref_cable, nom_appui, id_metier_

Vous pouvez aussi ajouter un tel index :

Code :

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

1
2
3
CREATE INDEX idx_ftth_pf_concat ON ftth_pf ( 
	concat('%', nom_nro, '/', type_pf_pe, '/', num_ordre_ )
);

Ou encore ajouter des colonnes calculées, exemples :

Code :

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

1
2
UPDATE ftth_pf SET recherche1 = true WHERE id_metier_ LIKE concat('%', b.nom_nro, '/', b.type_pf_pe, '/', cast(b.num_ordre_) )
UPDATE ftth_pf SET recherche2 = 'PA' WHERE id_metier_ like '%PA%';

[CinePhil]

Bonjour,

Commençons par remettre la requête en forme afin de la rendre plus lisible :

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
SELECT ft.id_metier_, 
	pm.id_regleme AS pm,
	b.id_regleme AS pa,
	ft.code_com, ft.utilisation, ft.etat, ft.calc_charg, ft.poi,
	ta.util, ta.com, c.ref_cable, ap.result_etu
FROM ftth_site_appui_ft ft
INNER JOIN ftth_zone_eligibilite z ON ST_INTERSECTS(z.geom, ft.geom)
	INNER JOIN ftth_pf b ON b.id_metier_= z.id_metier_
		LEFT JOIN 
		(
			SELECT id_metier_, id_regleme
			FROM ftth_pf pm
			WHERE pm.type_pf ='PMZ' OR pm.type_pf = 'PA'
		) pm 
			ON pm.id_metier_ LIKE CONCAT('%', b.nom_nro, '/', b.type_pf_pe, '/', CAST(b.num_ordre_ as bigint))
LEFT JOIN ftth_cable c ON ST_DWITHIN(ft.geom, c.geom, 0.2)
LEFT JOIN test_appuis ta ON ta.nom_appui = ft.id_metier_ 
LEFT JOIN apcom_poteaux ap ON ap.id_geofibre = ft.id_metier_
WHERE c.ref_cable IS NULL AND z.id_metier_ LIKE '%PA%'
ORDER BY ft.id_metier_

1) Avez-vous le pouvoir de modifier la structure de la base de données ?
Il semble en effet que des colonnes dont le nom commence par "id_" ne soient pas, comme d'habitude, des identifiants de type entier mais des chaînes de texte. Comme c'est sur cette colonne id_metier_ (pourquoi le dernier _ ?) que les jointures sont faites, pas étonnant que vous ayez des performances dégradées !

2) Peut-on avoir quelques exemples de données pour mieux nous rendre compte de ce que doit donner votre requête ?

3) Peut-on avoir la composition des tables pour voir si on peut faire les jointures autrement ?

4) Puisque vous avez une condition de restriction z.id_metier_ LIKE '%PA%' est-il utile que dans la sous-requête vous conserviez aussi les PMZ ?
Si oui, vous pouvez remplacer la restriction avec OR par une condition avec IN : WHERE type_pf IN ('PMZ', 'PA') .

5) Puisque vous avez la bonne pratique d'utiliser des alias pour les tables, utilisez-les partout dans la requête ! J'en ai ajouté deux qui manquaient. L'absence d'alias devant le nom d'une colonne pourrait déclencher une ambiguïté.

[Coco47]

Merci à tous les deux pour vos réponses.

Pourquoi vous castez num_ordre en bigint pour faire une concaténation

En fait là le cast sert surtout à virer la virgule et les 0 qui suivent. Dans ma table le num_ordre est un numeric et ressemble à ça "39268.00000000000" mais dans ma concaténation je n'ai besoin que du nombre avant la virgule.

L'usage des like plombe en premier vos requêtes, comme dans votre post précédent, un jocker % en début d'expression exclue l'utilisation d'un index
MySQL risque de scanner toute la table pour trouver tout ce qui ressemble à '%PA%'

Oui je me souviens de cette remarque mon soucis c'est que justement je ne connais pas le début de l'id du PM que je cherche. L'idée c'est que je trouve des PA, c'est PA ont comme parents un PM et pour trouver le PM je récupère nom_nro, type_pf_pe et num_ordre_. Au départ je pensais récupérer également un champ qui s'appelle code_com mais ca ne marche pas dans tous les cas.
Exemple :

PA	code_com	nom_nro	type_pf_pe	num_ordre_	concat complete	concat partielle	PM réel
69027/BG4/PA/29037	69027	BG4	PMZ	24211	69027/BG4/PMZ/24211	BG4/PMZ/24211	69027/BG4/PMZ/24211
69043/SGL/PA/26740	69043	SGL	PMZ	24283	69043/SGL/PMZ/24283	SGL/PMZ/24283	69204/SGL/PMZ/24283

Donc des coups ca marche, des coups non. Donc du coup je n'ai pas de moyens de connaitre les 5 premiers caractères de ma recherche.

Pour ce qui est des index je pense comprendre la logique, mon problème c'est que je préfèrerais éviter de rajouter des champs a mes tables existantes car après ca complique les imports et il faut que tout reste simple car l'outil va être utiliser par de "simple" opérateur.

1) Avez-vous le pouvoir de modifier la structure de la base de données ?
Il semble en effet que des colonnes dont le nom commence par "id_" ne soient pas, comme d'habitude, des identifiants de type entier mais des chaînes de texte. Comme c'est sur cette colonne id_metier_ (pourquoi le dernier _ ?) que les jointures sont faites, pas étonnant que vous ayez des performances dégradées !

Alors oui et non. En théorie j'en ai le pouvoir vu que c'est une base exprès pour cet outil et qu'elle concatene des données venant d'autres bases. Mais en pratique comme dit au dessus, elle est constitué à partir de fichiers shape et csv provenant d'au moins 3 autres bases différentes auquel on a pas accès en plus. Les opérateurs ont accès à ces bases via des interfaces et peuvent faire des extracts pré-configurer sans rien "personnaliser". Ensuite moi j'importe ces extracts, j'ai donc gardé les noms des champs, les stuctures, etc pour garder un import simple pour le jour ou les opérateur devront le faire eux sans aucune connaissance en sql, en pgadmin ou en qgis.
Mais oui je suis d'accord que les noms ne sont pas clair d'autant plus que les mêmes champs se retrouvent dans plusieurs tables et ne contiennent pas toujours la même chose.

2) Peut-on avoir quelques exemples de données pour mieux nous rendre compte de ce que doit donner votre requête ?

Euh vous voudriez ça sous quelle forme ? Des simples imprims écrans ? Des fichiers csv direct ?

3) Peut-on avoir la composition des tables pour voir si on peut faire les jointures autrement ?

Pareil qu'au dessus je veux bien mais sous quelle forme les voulez-vous ?

4) Puisque vous avez une condition de restriction z.id_metier_ LIKE '%PA%' est-il utile que dans la sous-requête vous conserviez aussi les PMZ ?
Si oui, vous pouvez remplacer la restriction avec OR par une condition avec IN : WHERE type_pf IN ('PMZ', 'PA') .

En fait parce qu'en fait on parle de choses différentes.
Le z.id_metier_ LIKE '%PA%', en fait la table z est une table de zone qui contient les zones géographique associées a différents points les PA, les PB, les PMZ, les NRO, etc. Donc je met cett restriction pour dire que pour un ft donné je ne veux récupérer que le PA dans lequel il est inclus dans sa zone.
Ensuite ce PA possède un autre point "père" qui peut être un PMZ ou un PA d'où le WHERE type_pf IN ('PMZ', 'PA').

5) Puisque vous avez la bonne pratique d'utiliser des alias pour les tables, utilisez-les partout dans la requête ! J'en ai ajouté deux qui manquaient. L'absence d'alias devant le nom d'une colonne pourrait déclencher une ambiguïté.

Effectivement je l'avais vu aussi après avoir mis on message ici .

[SQLpro]

...En fait là le cast sert surtout à virer la virgule et les 0 qui suivent. Dans ma table le num_ordre est un numeric et ressemble à ça "39268.00000000000" mais dans ma concaténation je n'ai besoin que du nombre avant la virgule.

C'est donc tout à fait inutile et parfaitement stupide...

• inutile car 123.0000 = 123 et 123.00 = 123.00000. Révisez vos mathématiques !
• parfaitement stupide si vous voulez des performances ! En effet toute transformation des données (CAST par exemple) empêche l'utilisation d'index. or les index sont là pour vous faire gagner de la performance.

À propos du LIKE '%123...'

....L'idée c'est que je trouve des PA, c'est PA ont comme parents un PM et pour trouver le PM je récupère nom_nro, type_pf_pe et num_ordre_. Au départ je pensais récupérer également un champ qui s'appelle code_com mais ca ne marche pas dans tous les cas....

Vous ne devriez pas faire cela si vous n'aviez pas violé l'un des principe fondamentaux des bases de données relationnelles appelé forme normale n°1 (il y en a une dizaine d'autres...). Le viol de la forme normale n° 1 empêche l'application de toutes les autres formes normales et de là découlent tout vos problèmes de perf. Les formes normales servent à vérifier si le contenu et l'articulation de vos données entre-elles respecte des règles de modélisation des bases de données relationnelles. Si ce n'est pas le cas, cela ne marchera jamais que très poussivement (à la manière du COBOL des années 60 !). En effet un SGBD relationnel est là pour manipuler à très grande vitesse des données relationnelles et rien d’autre. Tout ce qui s'en éloigne sera catastrophique en terme de performances. Imaginez devoir concourir sur un circuit de formule 1 avec un tracteur ?
la forme normale n° 1 dit que toute information dans une base de données relationnelles doit être atomique (c'est à dire scalaire, unitaire, non composée....). or en mélangeant comme vous les faites différentes informations dans une même colonne (on ne parle pas de champs dans un SGBDR) vous pourrissez d'entrée les performances !
https://fr.wikipedia.org/wiki/Forme_..._forme_normale








A +

[Coco47]

C'est donc tout à fait inutile et parfaitement stupide...
inutile car 123.0000 = 123 et 123.00 = 123.00000. Révisez vos mathématiques !
parfaitement stupide si vous voulez des performances ! En effet toute transformation des données (CAST par exemple) empêche l'utilisation d'index. or les index sont là pour vous faire gagner de la performance.

Alors c'est peut être complètement stupide mais ce n'est pas moi qui ai créé sql. Avez-vous seulement lu l'explication du pourquoi je fais ce cast ?
Et merci pour le Révisez vos mathématique, je suis débutant mais pas idiots non plus.
Quoiqu'il en soit ce que vous dites ne marche pas les deux nombre on beau être égaux lorsqu'ils sont concaténer avec d'autres caractères ça ne donne pas la même chose.
Du coup dans un cas j'obtient "%CRA/PMZ/55127" et dans l'autre j'obtient "%CRA/PMZ/55127.00000000000" et je ne peux pas faire ma recherche avec ce "." et tout ces "0" ca ne marchera pas.
Après je veux bien entendre qu'il existe peut être une autre méthode plus efficace par exemple en supprimant, après concaténation, les 12 derniers caractères.

Vous ne devriez pas faire cela si vous n'aviez pas violé l'un des principe fondamentaux des bases de données relationnelles appelé forme normale n°1 (il y en a une dizaine d'autres...). Le viol de la forme normale n° 1 empêche l'application de toutes les autres formes normales et de là découlent tout vos problèmes de perf. Les formes normales servent à vérifier si le contenu et l'articulation de vos données entre-elles respecte des règles de modélisation des bases de données relationnelles. Si ce n'est pas le cas, cela ne marchera jamais que très poussivement (à la manière du COBOL des années 60 !). En effet un SGBD relationnel est là pour manipuler à très grande vitesse des données relationnelles et rien d’autre. Tout ce qui s'en éloigne sera catastrophique en terme de performances. Imaginez devoir concourir sur un circuit de formule 1 avec un tracteur ?
la forme normale n° 1 dit que toute information dans une base de données relationnelles doit être atomique (c'est à dire scalaire, unitaire, non composée....). or en mélangeant comme vous les faites différentes informations dans une même colonne (on ne parle pas de champs dans un SGBDR) vous pourrissez d'entrée les performances !

Encore une fois je ne fais que m'adapter a ce que j'ai. La base je ne l'ai pas définie je suis arrivé sur le projet elle existait déjà, moi j'ai été appelé pour faire du vba et au final je dois également faire du sql alors que mon expérience de ce langage se limite à 4h en cours d'école d'ingé il y a plus de 10 ans. Donc je fais avec ce que j'ai c'est peut être pas parfait mais j'ai pas vraiment le choix.
Mais ça ne m'empêche pas d'essayer de comprendre si je peux optimiser les choses.
Bref par rapport à ce que vous dites vous avez surement raison mais perso j'y comprends pas grand chose.

[CinePhil]

Alors oui et non. En théorie j'en ai le pouvoir vu que c'est une base exprès pour cet outil et qu'elle concatene des données venant d'autres bases. Mais en pratique comme dit au dessus, elle est constitué à partir de fichiers shape et csv provenant d'au moins 3 autres bases différentes auquel on a pas accès en plus. Les opérateurs ont accès à ces bases via des interfaces et peuvent faire des extracts pré-configurer sans rien "personnaliser". Ensuite moi j'importe ces extracts, j'ai donc gardé les noms des champs, les stuctures, etc pour garder un import simple pour le jour ou les opérateur devront le faire eux sans aucune connaissance en sql, en pgadmin ou en qgis.

Alors peut-être que c'est à vous de construire :
1) la base de données correctement structurée pour votre outil ;
2) de développer le ou les programmes d'import adéquats qui vont mettre les données importées dans les bonnes tables ; outil qui sera ensuite facile d'utilisation pour les opérateurs non informaticiens qui seraient probablement perdus et/ou qui risqueraient de faire de grosses bêtises dans la BDD en jouant avec PGAdmin.
PGAdmin est l'outil du DBA PostgreSQL, sûrement pas un logiciel à mettre entre les mains des profanes.

Pour le point 2, l'idée serait donc d'importer les données brutes dans un schéma dédié dans PostgreSQL et de développer une procédure SQL qui ferait le boulot de répartir ces données brutes. Et avec le schéma correctement structuré et indexé du point 1, vous pourrez ensuite mitonner des requêtes, même complexes, qui s'exécuteront rapidement.

[manzeki]

C'est donc tout à fait inutile et parfaitement stupide...

• inutile car 123.0000 = 123 et 123.00 = 123.00000. Révisez vos mathématiques !

Non, il veut juste enlever la partie décimale pour faire sa concatenation, il ne cherche pas à faire une comparaison mathématique.
En l'insultant, vous rendez vos contributions inutiles, êtes-vous né Expert en SQL ?

[manzeki]

Pour ce qui est des index je pense comprendre la logique, mon problème c'est que je préfèrerais éviter de rajouter des champs a mes tables existantes car après ca complique les imports et il faut que tout reste simple car l'outil va être utiliser par de "simple" opérateur.

Vous pouvez gardez vos tables d'import, mais créez des tables normalisées supplémentaires, sinon des vues matérialisées, idéalement dans un schéma à part.
Vous faites l'essentiel de vos jointure sur id_metier_ alors que c'est un champ composé, il n'est pas atomique.
c'est votre problème principal. D'où vos LIKE et vos concaténations.
Cela plombe vos requêtes et les rend complexes pour rien.

[manzeki]

(1) Pourriez vous nous faire un EXPLAIN sur votre requête comme ceci ?

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
 
EXPLAIN SELECT ft.id_metier_, 
	pm.id_regleme AS pm,
	b.id_regleme AS pa,
	ft.code_com, ft.utilisation, ft.etat, ft.calc_charg, ft.poi,
	ta.util, ta.com, c.ref_cable, ap.result_etu
FROM ftth_site_appui_ft ft
INNER JOIN ftth_zone_eligibilite z ON ST_INTERSECTS(z.geom, ft.geom)
 
SELECT b.id_metier_, 
	pm.id_regleme AS pm,
	b.id_regleme AS pa,
 
	INNER JOIN ftth_pf b ON b.id_metier_= z.id_metier_
		LEFT JOIN 
		(
			SELECT id_metier_, id_regleme
			FROM ftth_pf pm
			WHERE pm.type_pf ='PMZ' OR pm.type_pf = 'PA'
		) pm 
			ON pm.id_metier_ LIKE CONCAT('%', b.nom_nro, '/', b.type_pf_pe, '/', CAST(b.num_ordre_ as bigint))
 
LEFT JOIN ftth_cable c ON ST_DWITHIN(ft.geom, c.geom, 0.2)
LEFT JOIN test_appuis ta ON ta.nom_appui = ft.id_metier_ 
LEFT JOIN apcom_poteaux ap ON ap.id_geofibre = ft.id_metier_
 
WHERE c.ref_cable IS NULL AND z.id_metier_ LIKE '%PA%'
ORDER BY ft.id_metier_

(2) Normalisation des tables :

Pour les tables : ftth_pf, ftth_zone_eligibilite, ftth_immeuble, sireo_immeuble
Quelle est la structure de ces tables ?
Pourriez vous nous montrer un échantillon de vos données tel qu'elles se présentent dans la bd
L'essentiel de vos jointures sont appliquées sur des parties de colonnes (chaînes de caractères), voilà pourquoi vous faites des likes et des concaténations

Cela signifie que vos tables ne respectent pas les différentes formes de normalisation(1FN, 2FN, 3FN...)
N'allez pas plus loin si cette étape n'est pas franchie.

Une donnée doit être atomique, c'est la Première forme Normale 1
La 2FN, exige que la 1FN soit d'abord respectée
La 3FN exige que la 2FN soit d'abord respectée.

Considérez que vos tables actuelles sont des tables facilitant l'import
Créez quelques tables supplémentaires correctement normalisées
Écrivez vos tables de manière lisibles

L'autre alternative serait de passer par des Vues Matérialisées

Exemple :
id_metier_ :
(69027/BG4/PMZ/24211'), cette colonne qui a une forme parfaitement structurée n'est pas atomique, vue qu'elle est la composition de quatre colonnes, elle viole la 1FN
Elle semble être votre clé primaire dans les tables suivantes : ftth_pf, ftth_zone_eligibilite, ftth_immeuble, test_appuis, apcom_poteaux
Décomposez la en plusieurs colonnes

Suggestion :
Importer vos données dans un schema séparé exemple import, archives
Créer toutes ou quelques tables normalisées dans un autre schema : public ou autre, avec des cléfs primaires numériques
Faites un script qui importe les données vers archive, puis formate et remplit ces tables normalisées :

Code :

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

INSERT INTO poteaux (poteau_id, ...) SELECT id, papa, maman FROM archives.ftth_pf.... WHERE ....

(3) Optimisation de vos requêtes
Posez des index sur les colonnes concernée par les jointures, tri
Vos jointures doivent être majoritairement appliquées sur des clés primaires numériques
COMMAND WITH : Utilisez les CTE pour rendre vos requêtes concises, voir mon exemple dans votre post précédent

[manzeki]

--
-- Si je comprends bien, un poteau A peut avoir pour parent un poteau B et B pour parent C
-- Autrement dit, une structure en arbre ?

table: clé primaire
ftth_pf: id_metier_
test_appuis: nom_appui
ftth_immeuble: id_metier_,
apcom_poteaux: id_geofibre,
sireo_immeuble: code_imb,
ftth_site_appui_ft : id_metier_
ftth_zone_eligibilite: id_metier_

A voir vos jointures : id_metier_ = nom_appui = id_geofibre = code_imb
Sachant que id_metier_ est une concatenation des champs '..../nom_nro/type_pf_pe/num_ordre_' ou parfois d'autre chose
Vous traitez id_metier_ comme une clée primaire dans toutes vos tables, en plus de faire des jointures dessus ?
Ce n'est plus du SQL

--
-- Voici une piste qui pourrait faire le lien entre un poteau et son parent

Code :

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

1
2
3
4
5
CREATE TABLE poteaux_parents (
	id_poteau integer,
	id_poteau_parent integer,
	primary key (id_poteau, id_poteau_parent)
);

--

Code :

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

1
2
3
4
INSERT INTO poteaux_parents ( id_poteau, id_poteau_parent )(
SELECT a.id_poteau, p.id_poteau
FROM ftth_pf a, ftth_pf m
WHERE m.type_pf IN ('PMZ', 'PA') AND m.id_metier_ ILIKE CONCAT('%', a.nom_nro, '/', a.type_pf_pe, '/', CAST(a.num_ordre_ as bigint))

[Coco47]

Bonjour manzeki,

Et merci pour vos réponses.

Alors voici le explain demandé :

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
"Sort  (cost=161681991.24..161681991.24 rows=1 width=184)"
"  Sort Key: ft.id_metier_"
"  ->  Nested Loop Left Join  (cost=0.00..161681991.23 rows=1 width=184)"
"        Join Filter: ((ap.id_geofibre)::text = (ft.id_metier_)::text)"
"        ->  Nested Loop Left Join  (cost=0.00..161676414.13 rows=1 width=172)"
"              Join Filter: ((ta.nom_appui)::text = (ft.id_metier_)::text)"
"              ->  Nested Loop Left Join  (cost=0.00..161676197.68 rows=1 width=147)"
"                    Join Filter: ((pm.id_metier_)::text ~~ concat('%', b.nom_nro, '/', b.type_pf_pe, '/', (b.num_ordre_)::bigint))"
"                    ->  Nested Loop  (cost=0.00..161672178.34 rows=1 width=146)"
"                          Join Filter: ((z.id_metier_)::text = (b.id_metier_)::text)"
"                          ->  Nested Loop  (cost=0.00..161668041.30 rows=1 width=138)"
"                                Join Filter: ((z.geom && ft.geom) AND _st_intersects(z.geom, ft.geom))"
"                                ->  Nested Loop Left Join  (cost=0.00..161664575.73 rows=1 width=151)"
"                                      Join Filter: ((ft.geom && st_expand(c.geom, 0.2::double precision)) AND (c.geom && st_expand(ft.geom, 0.2::double precision)) AND _st_dwithin(ft.geom, c.geom, 0.2::double precision))"
"                                      Filter: (c.ref_cable IS NULL)"
"                                      ->  Seq Scan on ftth_site_appui_ft ft  (cost=0.00..2584.56 rows=26656 width=130)"
"                                      ->  Materialize  (cost=0.00..3328.62 rows=17841 width=527)"
"                                            ->  Seq Scan on ftth_cable c  (cost=0.00..2036.41 rows=17841 width=527)"
"                                ->  Seq Scan on ftth_zone_eligibilite z  (cost=0.00..2420.29 rows=3982 width=453)"
"                                      Filter: ((id_metier_)::text ~~ '%PA%'::text)"
"                          ->  Seq Scan on ftth_pf b  (cost=0.00..3874.24 rows=21024 width=46)"
"                    ->  Seq Scan on ftth_pf pm  (cost=0.00..3979.36 rows=2285 width=33)"
"                          Filter: (((type_pf)::text = 'PMZ'::text) OR ((type_pf)::text = 'PA'::text))"
"              ->  Seq Scan on test_appuis ta  (cost=0.00..141.20 rows=6020 width=38)"
"        ->  Seq Scan on apcom_poteaux ap  (cost=0.00..4694.82 rows=70582 width=26)"

Pour ce qui est de mes tables je vous mets leurs formes ci dessous :
Table ftth_site_appui_ft :


Table ftth_zone_eligibilite z :


Table ftth_pf b/pm :


Table ftth_cable c :


Table test_appuis ta :


Table apcom_poteaux ap :



Et sinon pour vous décrire plus mon projet. Je travail sur la structure d'un réseau de fibre optique. Un tel réseau est structuré comme ci :

Au départ on a un PMZ avec sa zone d'éligibilité, accroché a ce PMZ on a des PA avec leur zone d'éligibilité, ensuite à ces PA sont reliés des PB et parfois d'autres PA. Enfin on a des poteaux sur lesquels reposent les câbles qui relient ces PMZ, PA, PB et des poteaux sur lesquels reposent d'autres câbles.

Ces PMZ, PA et PB se retrouvent dans la table ftth_pf, leurs zones d'éligibilité dans la table ftth_zone_eligibilite , la cables dans la table ftth_cable et les poteaux sont dans 3 tables ftth_site_appui_ft, test_appuis, apcom_poteaux avec des infos différentes a chaque fois.

Le but de ma requête c'est donc de récupéré tout les poteaux qui ne sont pas parcourus par un cable dans la zone d'éligibilité d'un PA donné.
Pour cela je commence par faire une jointure géométrique entre tous mes poteaux et mes zones d'éligibilités en filitrant pour ne garder que les zones d'éligibilité des PA.

Code :

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

1
2
inner join ftth_zone_eligibilite z on st_intersects(z.geom,ft.geom)
where and z.id_metier_ like '%PA%'

A ce moment j'ai trouvé tous les poteaux dans la zone d'éligibilité d'un PA. Je joint donc géométriquement ces poteaux avec les cables pour déterminer les poteaux qui ne soutiennent pas de cables.

Code :

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

1
2
left join ftth_cable c on st_dwithin(ft.geom,c.geom,0.2)
where c.ref_cable Is Null

Une fois les poteaux trouvés je parcours également les tables test_appuis et apcom_poteaux pour obtenir plus d'information sur l'état de mon poteau.

Code :

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

1
2
left join test_appuis ta on ta.nom_appui=ft.id_metier_ 
left join apcom_poteaux ap on ap.id_geofibre = ft.id_metier_

Avec la première jointure j'ai récupère uniquement le nom du PA (id_metier_) je le joint donc avec la table ftth_pf pour récupérer les autres infos.

Code :

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

inner join ftth_pf b on b.id_metier_=z.id_metier_

Ensuite pour récupérer le PMZ au dessus du PA, n'ayant pas de lien direct, je suis obligé de concaténer plusieurs champs du PA et de faire une jointure avec la table ftth_pf

Code :

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

1
2
3
left join (select id_metier_, id_regleme
	from ftth_pf pm
	where pm.type_pf ='PMZ' or pm.type_pf = 'PA') pm on pm.id_metier_ like concat('%',b.nom_nro,'/',b.type_pf_pe,'/',cast(b.num_ordre_ as bigint))

Voilà j'espère que c'est plus clair pour vous ?

[SQLpro]

Typiquement ce genre de données devrait être créée par un SGBD doté de deux types d'objets : des objets géographique (SIG) et des tables de graphe. Or PostGreSQL n'implémente pas de table de nœuds ni d’arête pour gérer des graphes.

À ma connaissance, seuls Oracle et SQL Server permettent les deux. Dans oracle c'est un module payant supplémentaire pour les tables de graphe alors que dans SQL Server tout est inclus, même dans la version gratuite Express...
Donc si vos tailles de DB ne dépassent pas 10 Go, SQL Server Express 2019 pourrait le faire, sinon optez pour un mode SAAS avec la version Web de SQL Server qui est très peu chère en mode locatif.

Pour information, je travaille avec des boîtes comme smartgis pour l'implantation de la fibre et ils sont abandonnés PG au profit de SQL Server il y a pas mal de temps déjà !

A +

[manzeki]

La commande Explain montre bien qu'aucun index n'est utilisé. Soit parce que vous n'avez pas posé d'index soit parce que vous demandez trop de données.

Avez-vous au moins poser un index sur id_metier_, geom ?
On ne peut pas vous aidez avec une liste de colonnes seulement, sans connaître le type de chaque colonne.
A moins que vous n'ayez compris que le type des colonnes, les indexes, et la normalisation déterminent la performance de votre base de données et qu'avec une telle volumétrie et des requêtes peu optimisées, vous risquez de faire planter votre système.

Votre base de données est mal modélisée, avec toutes ces tables qui font 40 colonnes ou plus.
Exemple :
Pourquoi séparer latitude, longitude en deux colonnes au lieu d'un seule colonne de type point
Des colonnes comme nom_voie, code_voie, num_voie doivent être dans une table voies par exemple.
opérateurs , deployeur dans une autre table

Est-que le gid est la clé primaire ? Est-que la colonne gid a les même valeurs dans toutes les tables, sa valeur est de quel type ?
Pour une structure en arbre ou graph pour vos poteaux, cela se fait bien dans une table.
Une seule table pour tous les immeubles, et des tables supplémentaires pour caractériser les immeubles qui ont des propriétés particulières.
Etc...

[escartefigue]

Bonjour,

Avant d'optimiser les requêtes, il faut construire un modèle de données propice aux performances en commençant par respecter les formes normales.
Je doute fort que ce soit le cas, vu le nombre d'attributs dans chaque table (tables "obèses") et aussi le nombre d'identifiants par table, votre modèle de données ressemble plus à un modèle à plat qu'à un modèle relationnel.

La répétition des identifiants dans une même table (ex : id_metier et id_metier1 dans ftth_table) est elle aussi fortement suspecte.

Avec un tel modèle, les performances ne peuvent pas être bonnes. Notamment sur le chapitre des accès concurrents.

[SQLpro]

... Avez-vous au moins poser un index sur id_metier_, geom ?

En tant qu'Administrateur (j'espère que c'est pas DBA...), vous devriez savoir qu'un index spatial doit être créé en dehors de toute autre colonne relationnelle. Proposer un tel index est juste impossible !

...Votre base de données est mal modélisée, avec toutes ces tables qui font 40 colonnes ou plus.
Exemple :
Pourquoi séparer latitude, longitude en deux colonnes au lieu d'un seule colonne de type point
Des colonnes comme nom_voie, code_voie, num_voie doivent être dans une table voies par exemple.
opérateurs , deployeur dans une autre table

la je vous rejoins à 100%... La problématique des tables obèses ultra contre performante que j'indique depuis des décennies et dans laquelle tous les développeurs sombre parce qu'ils n'étudient pas les bases de données....
https://blog.developpez.com/sqlpro/p...mances_petites

A +

[Coco47]

Merci à tous pour vos réponses.

D'un point de vue globale et comme je le disais plus haut je ne suis pas du tout administrateur base de données et mes connaissances sont plus que limitées. Du coup je pense avoir compris ce que vous dites dans les grandes lignes mais c'est à peu près tout.
Et comme je le disais aussi moi je suis arrivé sur le projet en cours et la base était déjà existante du coup la base de travail était ce quelle est et moi je ne peux que m'y adapter. Les tables obèses viennent d'outils de chez Orange et donc pareil ce n'est nous qui avons choisi cette obésité . De plus le temps du projet étant très court je n'ai pas le temps de révolutionner toute la structure de ma base de travail. C'est pour ça que je cherchais à optimiser un peu mes requêtes pour gagner un peu temps d'éxécution, mais au final le client préfère passer 1h a attendre que toutes ma macro avec requêtes s'éxécute de temps en temps plutôt que de passer plusieurs jours à remettre complètement à plat la base.

Bref de ce que je comprends de vos réponses ma requête ne pourra pas être optimisé plus que ça au vu de la structure de ma base. Encore une fois merci a vous pour vos réponses.


@SQLpro Smartgis je ne connais pas, si ça vous parle, ici on utilise une base postgres en parallèle avec QGIS.

[manzeki]

Vous remandez des optimisations, mais vous semblez ne jamais en tenir compte.
Vous pouvez quand même gagnez en performance sans remettre tout à plat
Pourriez-vous tester la création de vues matérialisées ?

---
'PMZ', 'PA': Si ces filtres de recherche ne changent pas d'un appel de requête à un autre
mettez votre requête ou vos sous-requêtes dans des vues matérialisées

Pourriez-vous tester ceci ?

Code :

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

1
2
3
4
5
6
7
8
9
 
CREATE MATERIALIZED VIEW poteaux AS
SELECT b.id_metier, b.id_regleme AS pa, m.id_regleme AS pm
FROM ftth_pf b
LEFT JOIN (
		SELECT  id_metier_, id_regleme
		FROM ftth_pf m
		WHERE IN type_pf ('PMZ', 'PA')
	) m ON m.id_metier_ LIKE concat('%', b.nom_nro,'/',  b.type_pf_pe, '/', cast(b.num_ordre_ AS bigint))

---

Code :

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

1
2
3
4
5
 
CREATE MATERIALIZED VIEW zones AS 
	SELECT id_metier, geom 
	FROM ftth_zone_eligibilite 
	WHERE id_metier_ LIKE '%PA%'

Vous pouvez aussi ajouter des indexes

Code :

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

CREATE UNIQUE INDEX idx_zones_id_metier_ ON zones(id_metier_)

Code :

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

CREATE UNIQUE INDEX idx_poteaux_id_metier_ ON poteaux(id_metier_)

---

Code :

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

1
2
3
4
5
6
7
8
9
 
SELECT f.id_metier_, f.cote_com, f.utilisation, f.etat, f.calc_charg, f.poi, b.pm, b.pa, t.util, t.com, a.result_etu
FROM ftth_site_appui_ft f
	INNER JOIN zones z ON st_intersects(z.geom, f.geom)
		INNER JOIN poteaux b ON b.id_metier_ = z.id_metier_
	LEFT JOIN test_appuis t ON t.nom_appui = f.id_metier_ 
	LEFT JOIN apcom_poteaux a ON a.id_geofibre = f.id_metier_
WHERE NOT EXISTS (SELECT gid FROM ftth_cable WHERE st_dwithin(f.geom, geom, 0.2))
ORDER BY f.id_metier_;

[Coco47]

Bonjour,


Désolé pour le délai de réponse j'ai été bien occupé, puis vacances, etc.

En tous cas j'ai effectivement fait avec des vues matérialisées et ça a réduit un peu mes temps de traitements donc merci pour le coup de main.