jbdesbas
commented
5 years ago Après avoir testé rapidement ta requête : un cost divisé par 10 (sur un faible nombre de données) ! Merci !
Closed jbrieuclp closed 4 years ago
jbdesbas
commented
5 years ago Après avoir testé rapidement ta requête : un cost divisé par 10 (sur un faible nombre de données) ! Merci !
jbrieuclp
commented
5 years ago Merci pour le retour parce que je me suis aperçu entre temps que pour la partie ajout des communes c'était pas top... Je modifie dans le post initial mais voici une correction pour encore réduire le cost par 10..
[Reprise du premier post avec correction] En ajoutant les commune(s) qui intersecte(nt) la geométrie (attention au id_type communal et au group by) :
SELECT
[...]
string_agg(DISTINCT ((commune.area_name::text || ' ('::text) || "left"(commune.area_code::text, 2)) || ')'::text, ', '::text) AS communes,
FROM [...]
LEFT JOIN (
gn_synthese.cor_area_synthese cor_s_commune ON s.id_synthese = cor_s_commune.id_synthese
INNER JOIN ref_geo.l_areas comm_area ON cor_s_commune.id_area = comm_area.id_area AND comm_area.id_type = 25
) commune ON s.id_synthese = commune.id_synthese
GROUP BY [tous les champs]La première version faisait un LEFT JOIN avec toutes les données gn_synthese.cor_area_synthese ce qui renvoyait un grand nombre de ligne, ligne invisible du fait du GROUP BY. Le LEFT JOIN (INNER JOIN) ne retourne que les cor_area_synthèse propres aux communes.
amandine-sahl
commented
5 years ago Pour extraire les données sur les aires géographique de façon générique il y a également une technique un peu bourine :
-- Exemple sur les zonages parcs
SELECT
s.id_synthese,
a.jname ->> 'COM' AS commune,
COALESCE(a.jcode ->> 'ZC', a.jcode ->> 'AA', a.jcode ->> 'PEC', 'Hors parc') AS zone_pnc,
a.jname ->> 'MASSIF' AS massif,
a.jname ->> 'ZB' AS zone_bioge,
FROM gn_synthese.synthese s
JOIN LATERAL (
SELECT d_1.id_synthese,
json_object_agg(d_1.type_code, d_1.o_name) AS jname,
json_object_agg(d_1.type_code, d_1.o_code) AS jcode
FROM (
SELECT sa.id_synthese,
ta.type_code,
string_agg(DISTINCT a_1.area_name::text, ','::text) AS o_name,
string_agg(DISTINCT a_1.area_code::text, ','::text) AS o_code
FROM gn_synthese.cor_area_synthese sa
JOIN ref_geo.l_areas a_1 ON sa.id_area = a_1.id_area
JOIN ref_geo.bib_areas_types ta ON ta.id_type = a_1.id_type
WHERE sa.id_synthese = s.id_synthese
GROUP BY sa.id_synthese, ta.type_code
) d_1
GROUP BY d_1.id_synthese
) a ON trueTu arrives à réduire le cost de la requête avec ça ??
camillemonchicourt
commented
4 years ago Le premier sujet du with et des jointures a été revu depuis un moment, merci pour le retour.
On vient de reprendre complètement la vue d'export dans https://github.com/PnX-SI/GeoNature/pull/1124 (en cours de finalisation dans develop et à cette occasion on a vu avec @amandine-sahl pour y ajouter les communes.
Pour uniquement ajouter les communes, sans devoir faire un GROUP BY avec tous les champs de la vue, @amandine-sahl a proposé :
SELECT
s.id_synthese,
communes
FROM gn_synthese.synthese s
LEFT OUTER JOIN (
SELECT id_synthese , string_agg(DISTINCT area_name, ', ') AS communes
FROM gn_synthese.cor_area_synthese cas
LEFT OUTER JOIN ref_geo.l_areas a_1 ON cas.id_area = a_1.id_area
JOIN ref_geo.bib_areas_types ta ON ta.id_type = a_1.id_type AND ta.type_code ='COM'
GROUP BY id_synthese
) sa ON sa.id_synthese = s.id_synthesehttps://explain.depesz.com/s/2SYb
Par contre si on veut plusieurs types de zonages, elle suggère :
WITH areas AS (
SELECT ta.id_type, type_code, id_area, a_1.area_code, a_1.area_name
FROM ref_geo.bib_areas_types ta
JOIN ref_geo.l_areas a_1 ON ta.id_type = a_1.id_type
WHERE ta.type_code in ('DEP', 'COM', 'M1')
)
SELECT
s.id_synthese ,
a.jname ->> 'DEP' AS "nomDepartement",
a.jcode ->> 'DEP' AS "codeDepartement",
a.jname ->> 'COM' AS "nomCommune",
a.jcode ->> 'COM' AS "codeCommune",
a.jcode ->> 'M10' AS "codeMaille"
FROM gn_synthese.synthese s
LEFT JOIN LATERAL (
SELECT
d_1.id_synthese,
json_object_agg(d_1.type_code, d_1.o_name) AS jname,
json_object_agg(d_1.type_code, d_1.o_code) AS jcode
FROM (
SELECT
sa.id_synthese,
ta.type_code,
string_agg(ta.area_name, '|') AS o_name,
string_agg(ta.area_code, '|') AS o_code
FROM gn_synthese.cor_area_synthese sa
JOIN areas ta ON ta.id_area = sa.id_area
WHERE sa.id_synthese = s.id_synthese
GROUP BY sa.id_synthese, ta.type_code
) d_1
GROUP BY d_1.id_synthese
) a ON TRUE;https://explain.depesz.com/s/T2W5
C'est cette deuxième solution qui a été retenue pour le nouvel export DEE en cours d'ajout dans les vues du module Export (https://github.com/PnX-SI/gn_module_export/commit/7ef0bf0ee8550efce00d13ec8e21a49c153d81a4)
Salut, C'est juste pour signaler que le WITH de la vue gn_synthese.v_synthese_for_export n'a pas lieu d'être. Au contraire il flingue les capacité de la base lors de l'exécution de cette requête. Si les jointures sont réalisées directement dans la requête (donc sans WITH) le cost lié à cette requête est 4.5 fois moindre. Je signale ça après avoir eu un out of memory pour une extraction départementale, extraction faite sous pgadmin, pas depuis l'appli.
La requête modifiée :
Après on peut pimper un peu plus cette requête en intégrant le statut de validation :
En ajoutant les commune(s) qui intersecte(nt) la geométrie (attention au id_type communal et au group by) :