Discussion :

[noarno]

Bonjour,
Voila je recherche des infos, conseils car je dois effectuer une jointure
entre deux tables A et B d'environ 3 millions d'enregistrement. Cette jointure porte sur 3 champs et le résultat que je souhaite doit contenir au minimum les enregistrements de la table A.
J'ai donc opté pour une vue qui a la définition suivante :

Code :

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

1
2
3
SELECT A.gid, A.code_dep, A.nom_com, A.num_parc, A.com_abs, B.surface, B.natdroitprop, B.groupeB.proprio, A.the_geom
   FROM A
   LEFT JOIN BON (A.code_dep::text || A.code_com::text) = B.commune AND A.com_abs::text = B.pref_section AND A.num_parc::text = B.num_parc;

J'effectue une concaténation également dans ma clause where.
Le résultat n'ai pas fameux : impossible de lire le résultat de cette vue. Même en affichant les 100 premiers enregistrement seulement.
Je sollicite tout conseil qui pourrait me permettre de faire cette jointure : la faire en dur en créant une nouvelle table et non en dur (mais c domage car je croyaias que les vues permettaient déviter ça), essayer de faire la jointure sur un seul champ et non sur trois (ce qui pourrait s'envisager mais m'obligerait à repenser le MCD), supprimer la concaténation... En fait je n'arrive pas à savoir ce qui demande de la ressource pour le calcul et ce qui n'en demande pas.
Gd merci d'avance.

[scheu]

Il n'est jamais bon que la jointure porte sur une concaténation de plusieurs champs (surtout quand les tables ont des grosses volumétries), cela plombe bien souvent les performances du SGBD (pas que pour Postgresql). Cela veut généralement dire que le modèle de données est mal fait (si si ...).

En gros sans voir tes données je suppose que ton champ B.commune doit être une concaténation du code postal et du nom de la ville c'est bien ça ? Si oui, cela aurait dû être séparé dans 2 colonnes différentes dans la table B

Calcules-tu régulièrement des statistiques sur ces 2 tables (commandes ANALYZE et VACUUM) ? Mets-nous le plan d'exécution de ta requête (résultat de "EXPLAIN + ta requête"). Il manque ces informations pour espérer pouvoir t'aider.

[noarno]

J'ai fait des vaccum après mes commandes copy mais ces deux tables ne seront jamais que consultées (aucun insert,update,delete ne sera fait sur A ou B).
Pour la concaténation, je m'en doutais donc je vvais la supprimer en fusionnant les deux champs de la table A définitivement ce qui ne perturbe pas le reste...).
Sinon voici le résultat du explain...auquel je ne comprend..rien :-)

Code :

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

1
2
3
4
5
6
7
8
9
 
Merge Left Join  (cost=3046183.99..3110416.00 rows=2293394 width=332)
  Merge Cond: (("outer"."?column8?" = "inner".num_parc) AND ("outer"."?column9?" = "inner".pref_section) AND ("outer"."?column10?" = "inner".commune))
 ->  Sort  (cost=2199751.15..2205484.64 rows=2293394 width=290)
        Sort Key: (parcelle.num_parc)::text, (parcelle.com_abs)::text, ((parcelle.code_dep)::text || (parcelle.code_com)::text)
        ->  Seq Scan on parcelle  (cost=0.00..126944.94 rows=2293394 width=290)
  ->  Sort  (cost=846432.83..852628.42 rows=2478235 width=76)
        Sort Key: meddi.num_parc, meddi.pref_section, meddi.commune
        ->  Seq Scan on meddi  (cost=0.00..79488.35 rows=2478235 width=76)

Mais qu'est ce que ça veut dire....

[scheu]

"Seq scan" ça veut dire que l'optimiseur Postgresql va parcourir la table entièrement pour trouver les correspondances dans ta jointure, ce qui peut être dramatique surtout si le résultat ne comporte que peu de lignes au final. As-tu une idée de combien de lignes doit retourner la jointure ? Y a-t-il une correspondance 1 pour 1 entre tes 2 tables ?

Quelques pistes d'amélioration :
- créer des indexes sur les colonnes servant à la jointure, et regarder le nouveau plan d'exécution pour voir s'ils sont utilisés ou pas (ce devrait être le cas si le résultat retourne peu de lignes)
- séparer la colonne B.commune en 2 colonnes distinctes dans la table B pour éviter d'avoir une concaténation dans la condition de jointure
- refaire le modèle (qui est souvent la cause des problèmes de performances, ta requête en est un bon exemple)

[noarno]

Merci pour ta réponse.
Le résultat de ma requete doit retourner 100% des tuples de A et je pense 90% des tuples de B (d'ou le left join). Et il y a systématiquement une correspondance 1 pour 1 entre A et B.
Au sujet des index, j'en utilise déjà pas mal pour d'autres champs de la table A. Y a t'il une limite à l'indexation des champs d'une table? (A priori j'ai de la place sur mon disque).
Pour la concaténation je vais faire ce que tu me conseilles.
Enfin au sujet du modèle j'ai bien sur pensé à fusionner A et B. Je pensais qu'une vue maurait permis d'éviter ça car j'ai des mises à jour de A et B à différentes périodes dans le temps (2 ou 3 dans l'année...).

[scheu]

Les indexes sont essentiellement utiles quand ils sont souvent utilisés comme condition de jointure ou de filtre (et encore, seulement pour envoyer une faible proportion de lignes par rapport aux volumétries de tes tables)
Une vue ne résout jamais les problèmes de performances (le select sera toujours exécuté), elle offre juste plus de souplesse et de simplicité à l'utilisateur pour lire les données

[noarno]

Les indexes ont énormément amélioré les perf. de ma vue.
Merci encore!

[scheu]

Bonne nouvelle alors