Vous n'êtes pas identifié(e).
dans l'explain j'ai :
" -> Merge Right Join (cost=59401.53..64132.78 rows=3 width=204)"l'actual row correspond au chiffre après les ... ?
On m'a déjà parlé de cette histoire de statistique. comment savoir si c'est bon ou pas ?
Merci beaucoup pour cette information.
Effectivement j'ai repris ma requête initial qui ne renvoyait même après 30 minutes et en passant le join_collapse_limit à 5 comme conseillé, la requête donne un résultat en 23 secondes.
Quel est donc la valeur par défaut car ma requête contient 7 joins direct, 8 left join direct, 3 joins indirect, 2 left join indirect, et enfin 2 inner join aussi indirect.
Direct lié au from principal.
Indirect join ou left join direct sur des sous-requêtes
D'avance merci
HadanMarv
Bonjour,
Nous avons depuis peu effectué une migration d'une base DB2 vers une base postgres.
Dans l'ensemble nous n'avons pas rencontré de grosses difficultés.
Cependant sur certaines extractions, nous avons pu constater que l'ordre des jointures avait un impact considérable.
La requête en question s'exécute en 10 secondes sur DB2.
La même requête, sur postgres calcul le plan d'exécution en 2 secondes, mais ne retourne jamais rien (même en exécutant la requête directement sur la base), kill du process après 35 minutes d'exécution...
En simplement changeant l'ordre d'une des jointures j'arrive à un résultat au bout de 2 secondes !!!!!!
Comment est-ce possible sachant que dans les deux cas le plan d'exécution était retourné rapidement ?
D'avance merci de vos lumières.
HadanMarv
Très bien merci pour le conseil.
Effectivement je réinstalle la base de données en 9.0 car je n'arrive pas à ré-importer le data 9.0 sous la 9.1.
Normal d'après mes recherches sur google. j'aurai faire un pg_dumpall avant.
Pas grave, c'est en forgeant qu'on devient forgeron.
@+
HadanMarv
Bonjour,
Merci beaucoup pour votre prompt et efficace réponse.
Pas de plus grosse requête que d'habitude mais le fichier xml temporaires à du prendre un coup.
Je l'ai supprimé et au miracle tout fonctionne normalement.
Me reste à réimporter mes bases de données maintenant...
Pour info j'avais réinstallé tout postgres, mais le fichier temporaire était tout de même resté...
HadanMarv
Bonjour,
Suite à une mise en veille prolongée de Windows (merci crosoft), mais pas la première mon pgadmin3 version 1.12.3 refuse de lancer l'éditeur de requête toute l'appli tombe (ne répond pas). J'ai désinstallé postgres 9.0 et ai installé la 9.1. Je lance mon pgadmin (sans avoir réimporter encore mes database), et là toujours pareil l'éditeur de requête fait tout planter.
Avez-vous une idée ?
D'avance merci.
Cordialement,
HadanMarv
<code>LEFT JOIN (
SELECT
PRO_ID
FROM PROFILS
LEFT JOIN UTILISATEURS ON USR_IDPRO=PRO_ID
WHERE PRO_ISUPP = 0 AND USR_ISUPP=0 AND PRO_ID > 0
GROUP BY PRO_ID
) AS T ON OBA_DROITSDISP = 3 <code>
c'est le on que je ne comprends pas
Je reprends actuellement une application en main. Un existant relativement conséquent.
J'ai pas mal d'exemple de requête relativement complexe j'en prend une au hazard car je n'arrive pas à comprendre son fonctionnement.
Vous pourrez constater que les deux lefts joins avec sous requête ont une clause on sur un champ n'existant pas.
c'est surement logique mais là je l'ai pas.
Voici les ddl :
CREATE TABLE objets
(
obj_id integer NOT NULL DEFAULT 0, -- Identifiant unique du objet
obj_module character varying(15) NOT NULL DEFAULT '0'::character varying,
obj_nom character varying(30) NOT NULL DEFAULT '0'::character varying,
obj_type smallint,
obj_actiondisp smallint,
CONSTRAINT objets_pkey PRIMARY KEY (obj_id),
CONSTRAINT obj_module_nom_u UNIQUE (obj_module, obj_nom)
)
CREATE TABLE objets_actions
(
oba_id integer NOT NULL DEFAULT 0, -- Identifiant unique du table
oba_seq character varying(1) NOT NULL DEFAULT '0'::character varying,
oba_idobj integer NOT NULL DEFAULT 0, -- Id objet (Clef etrangere OBJETS.OBJ_ID)
oba_droitsdisp smallint,
oba_emplacement smallint,
CONSTRAINT objets_actions_pkey PRIMARY KEY (oba_id),
CONSTRAINT oba_obj FOREIGN KEY (oba_idobj)
REFERENCES objets (obj_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT oba_seq_idobj_u UNIQUE (oba_seq, oba_idobj)
)
CREATE TABLE profils
(
pro_id integer NOT NULL DEFAULT 0, -- Identifiant unique de la profil
pro_nom character varying(40) NOT NULL DEFAULT NULL::character varying,
pro_comment character varying(255) DEFAULT NULL::character varying,
CONSTRAINT profils_pkey PRIMARY KEY (pro_id),
CONSTRAINT pro_idact_nom_u UNIQUE (pro_nom)
)
CREATE TABLE utilisateurs
(
usr_id integer NOT NULL DEFAULT 0, -- Identifiant unique de la utilisateur
usr_idpro integer, -- Id profil (Clef etrangere PROFILS.PRO_ID)
usr_nomcompte character varying(20) NOT NULL DEFAULT ''::character varying,
usr_pass character varying(32) DEFAULT NULL::character varying,
usr_nom character varying(40) DEFAULT NULL::character varying,
usr_prenom character varying(40) DEFAULT NULL::character varying,
usr_email character varying(60) DEFAULT NULL::character varying,
CONSTRAINT utilisateurs_pkey PRIMARY KEY (usr_id),
CONSTRAINT usr_pro FOREIGN KEY (usr_idpro)
REFERENCES profils (pro_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT usr_nomcompte_u UNIQUE (usr_nomcompte)
)
CREATE TABLE droits
(
dro_idpro integer NOT NULL DEFAULT 0, -- Id profil (Clef etrangere PROFILS.PRO_ID)
dro_idoba integer NOT NULL DEFAULT 0, -- Id object action (Clef etrangere OBJETS_ACTIONS.OBA_ID)
dro_visible smallint,
dro_droits smallint,
dro_popup smallint,
CONSTRAINT droits_pkey PRIMARY KEY (dro_idpro, dro_idoba),
CONSTRAINT dro_oba FOREIGN KEY (dro_idoba)
REFERENCES objets_actions (oba_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT dro_pro FOREIGN KEY (dro_idpro)
REFERENCES profils (pro_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
et la requête
SELECT OBJ_ID, OBA_SEQ, OBJ_MODULE, OBJ_NOM, OBA_DROITSDISP, COALESCE(T1.LENGTH,0) AS LENGTH, COALESCE(DRO_VISIBLE,1) AS DRO_VISIBLE,
CASE WHEN OBJ_TYPE IN (2,3) THEN COALESCE(DRO_DROITS,2) ELSE COALESCE(DRO_DROITS,0) END AS DRO_DROITS,
CASE WHEN OBJ_TYPE IN (2,3) THEN COALESCE(DRO_POPUP,2) ELSE COALESCE(DRO_POPUP,0) END AS DRO_POPUP
FROM OBJETS
LEFT JOIN OBJETS_ACTIONS ON OBA_IDOBJ = OBJ_ID
LEFT JOIN (
SELECT
PRO_ID
FROM PROFILS
LEFT JOIN UTILISATEURS ON USR_IDPRO=PRO_ID
WHERE PRO_ISUPP = 0 AND USR_ISUPP=0 AND PRO_ID > 0
GROUP BY PRO_ID
) AS T ON OBA_DROITSDISP = 3
LEFT JOIN (
SELECT
COUNT(DISTINCT PRO_ID) AS LENGTH
FROM PROFILS
LEFT JOIN UTILISATEURS ON USR_IDPRO=PRO_ID
WHERE PRO_ISUPP = 0 AND USR_ISUPP=0 AND PRO_ID > 0
) AS T1 ON OBA_DROITSDISP = 3
LEFT JOIN DROITS ON DRO_IDOBA = OBA_ID AND DRO_IDPRO = T.PRO_ID
WHERE OBJ_TYPE IN (2,3,4,6,7,9,11) AND OBJ_ACTIONDISP > 0 AND OBA_DROITSDISP > 0
ORDER BY OBJ_ID ASC, OBA_SEQ ASC, T.PRO_ID ASC, LENGTH ASC
Merci d'avande de vos lumières.
Je comprends parfaitement vos commentaires.
En fait en creusant un peu et en récupérant d'autres infos, ils semblent que les utilisateurs de l'application souhaitent fréquemment des extractions pour faire ce qui s'apparente davantage à des statistiques (BO serait à son affaire je pense ou BIRT à voir).
Bref, ils souhaitent avoir un maximum d'informations sur le fonctionnel dans le même fichier quitte à rajouter des informations et encore des informations.
D'où le nombre de jointure croissant. Toutes ces requêtes génèrent des xls.
Je ne vois pas pour le moment de porte de sortie...
En ce qui concerne les coalesce dans les conditions, il s'agit de ramener des informations d'une table x en fonction de la valeur de la clé étrangère d'une table a ou b.
Sachant que la clé étrangère n'est pas systématique en b mais l'est en a.
Certaines des vues font des calculs ( group by, having, disctinct...),ce qui me parait pour le coup difficilement réintégrable dans une seule et même requête.
-> Flo
Certainement, cependant je n'ai pas encore assez de recul sur le schéma pour attaquer.
PS : désolé nos messages se sont encore croisés.
-> Gleu
Très bien merci pour l'info. Y-a-t'il des préconisations en ce qui concerne le champ de l'acceptable nb jointures, nb champs retournés avec postgres ?
Me souvient avoir vu une sorte de documentation dans le style mais pour Oracle.
-> Flo :
Jointures sur des vues dangereux, probablement mais cette vue fait des calculs en plus. et effectivement elle est déjà en jointure de la requête.
Les COALESCE dans les jointures sont utilisés car on fait une jointure sur une table, mais avec une valeur provenant de deux tables jointes précédemment et ayant potentiellement la valeur à NULL.
Pour le calcul potentiel je peux comprendre effectivement.
-> Gleu
Peut-on augmenter cette limite, et si oui de combien et avec quelles conséquences ? Temps de réponse, perturbation possible ?
Merci de vos lumières
le paramétre join_collapse_limit est en commentaire dans mon fichier de conf.
Quel est l'impact ? quel est la valeur par défaut ?
En fait quand je mentionne que la requête est imposante c'est 21 jointures dont 2 sur des vues,
des COALESCE dans les jointures et les champs ramenés par la requête.
Ce que je n'arrive toujours pas saisir c'est pourquoi l'ordre des joins a un impact alors que le explain et l'analyse sont là pour çà ?
la requête étant tellement imposante, le nombre de tables aussi, pouvez-vous me donner des pistes de recherche ?
Sur le même principe j'ai une requête bien plus conséquente.
Dans un cas le plan d'exécution prend presque 500 secondes avant d'arrivée.
En décalant simplement 5 jointures plus bas dans la requête le plan d'exécution est sortie en 20 secondes,
Comment l'expliquer ?
Me suis certainement mal exprimé en fait j'ai bien les id des tables a et b qui sont en clé primaire, et j'ai rajouté un index sur le champ de jointure entre b et a.
J'ai augmenté également les stats sur ce même champ de b
Du coup dans quels autres outils essayés mes requêtes pour avoir des réponses aussi rapide que avec un explain analyse dans pgAdmin ?
--> SQLPro,
Dans mon post de 11h55 j'ai donnée les scripts de création des deux tables, vous pouvez y voir que les id des tables a et b sont les clés primaires ( CONSTRAINT a_pkey PRIMARY KEY (id) )
J'avais bien saisie le fait que les clés primaires et les index étaient deux choses différentes.
Je prends le projet en cours et ne suit donc pas à l'origine de la conception de la base de données.
Je suis tout à fait de votre avis en ce qui concerne la conception des tables.
--> gleu,
certes un peu plus long. le temps de 8 seconces m'est retournée par pgAdmin.
j'exécute la requête directement dedans.
Taille des tables
a -> 1,5 Mo pour 45631 lignes
b -> 3,5 Mo pour 51212 lignes
Nouveau explain analyse avec le set enable seqscan à false :
Merge Join (cost=0.68..9213.60 rows=40653 width=4) (actual time=0.144..385.327 rows=45625 loops=1)
Merge Cond: (a.id = b.ida)
-> Index Scan using "IDX_A" on a (cost=0.00..6053.12 rows=45631 width=4) (actual time=0.082..140.318 rows=45631 loops=1)
-> Index Scan using "IDX_B_A" on b (cost=0.00..2835.43 rows=51212 width=8) (actual time=0.051..85.847 rows=45626 loops=1)
Total runtime: 417.003 ms
pourquoi j'arrive tout de même à 8secondes de temps de traitements ??? j'avoue que je comprends pas trop.
Autant pour moi, voici les éléments :
Explain analyse :
Hash Join (cost=5560.70..8939.42 rows=40653 width=4) (actual time=152.796..350.725 rows=45625 loops=1)
Hash Cond: (b.b_ida = a.id)
-> Seq Scan on b (cost=0.00..2094.12 rows=51212 width=8) (actual time=0.194..77.089 rows=51212 loops=1)
-> Hash (cost=4834.31..4834.31 rows=45631 width=4) (actual time=152.455..152.455 rows=45631 loops=1)
-> Seq Scan on a (cost=0.00..4834.31 rows=45631 width=4) (actual time=0.045..100.977 rows=45631 loops=1)
Total runtime: 381.591 ms
Postgres 8.4 sous debian
DDL table a:
CREATE TABLE a
(
id integer NOT NULL DEFAULT 0,
F1 character varying(20) NOT NULL DEFAULT ''::character varying,
F2 character varying(180) DEFAULT NULL::character varying,
F3 character varying(3) DEFAULT NULL::character varying,
F4 character varying(2) DEFAULT NULL::character varying,
F5 character varying(2) DEFAULT NULL::character varying,
F6 character varying(20) DEFAULT NULL::character varying,
F7 character varying(3) DEFAULT NULL::character varying,
F8 character varying(3) DEFAULT NULL::character varying,
F9 character varying(3) DEFAULT NULL::character varying,
F10 character varying(3) DEFAULT NULL::character varying,
F11 character varying(100) DEFAULT NULL::character varying,
F12 integer,
F13 integer,
F14 integer,
F15 character varying(5000) DEFAULT NULL::character varying,
F16 date,
F17 date,
F18 character varying(3) DEFAULT NULL::character varying,
F19 date,
F20 date,
F21 character varying(5000) DEFAULT NULL::character varying,
F22 character varying(5000) DEFAULT NULL::character varying,
F23 varying(5000) DEFAULT NULL::character varying,
F24 character varying(1500) DEFAULT NULL::character varying,
F25 character varying(1500) DEFAULT NULL::character varying,
F26 character varying(1500) DEFAULT NULL::character varying,
F27 character varying(1500) DEFAULT NULL::character varying,
F28 character varying(1) DEFAULT NULL::character varying,
F29 timestamp without time zone,
F30 timestamp without time zone,
F31 timestamp without time zone,
F32 timestamp without time zone,
F33 smallint,
F34 smallint,
F35 smallint,
F36 smallint,
F37 date,
F38 smallint,
F39 integer,
F40 integer,
F41 character varying(255) DEFAULT NULL::character varying,
F42 character varying(3) DEFAULT NULL::character varying,
F43 character varying(3) DEFAULT NULL::character varying,
F44 character varying(3) DEFAULT NULL::character varying,
F45 character varying(3) DEFAULT NULL::character varying,
F46 integer,
F47 character varying(20) DEFAULT NULL::character varying,
F48 timestamp without time zone,
F49 smallint,
F50 character varying(20) DEFAULT NULL::character varying,
F51 timestamp without time zone,
F52 timestamp without time zone,
CONSTRAINT a_pkey PRIMARY KEY (id),
CONSTRAINT a_F46 FOREIGN KEY (F46)
REFERENCES c (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
)
WITH (
OIDS=FALSE
);
CREATE INDEX "IDX_A"
ON a
USING btree
(id);
DDL table b :
CREATE TABLE b
(
id integer NOT NULL DEFAULT 0,
F1 character varying(12) NOT NULL DEFAULT ''::character varying,
F2 character varying(12) DEFAULT NULL::character varying,
F3 character varying(3) DEFAULT NULL::character varying,
F4 character varying(3) DEFAULT NULL::character varying,
F5 date,
F6 integer,
F7 integer,
F8 integer,
F9_c integer,
F10 integer,
F11 integer,
ida integer,
F12 integer,
F13 integer,
F14 integer,
F15 integer,
F16 integer,
F17 integer,
F18 character varying(3) DEFAULT NULL::character varying,
F19 integer,
F20 integer,
F21 integer,
F22 integer,
F23 integer,
F24 integer,
F25 date,
F26 date,
F27 character varying(1) DEFAULT NULL::character varying,
F28 character varying(1500) DEFAULT NULL::character varying,
F29 character varying(1500) DEFAULT NULL::character varying,
F30 character varying(1500) DEFAULT NULL::character varying,
F31 integer,
F32 character varying(12) DEFAULT NULL::character varying,
F33 integer,
F34 character varying(20) DEFAULT NULL::character varying,
F35 timestamp without time zone,
F36 smallint,
F37 character varying(20) DEFAULT NULL::character varying,
F38 timestamp without time zone, -- Date de la dernière modification
F39 timestamp without time zone, -- Date du dernier accès
CONSTRAINT b_pkey PRIMARY KEY (id),
CONSTRAINT b_a FOREIGN KEY (ida)
REFERENCES a (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
)
WITH (
OIDS=FALSE
);
ALTER TABLE b ALTER COLUMN ida SET STATISTICS 1000;
CREATE INDEX "IDX_B_A"
ON b
USING btree
(ida);
Espérant avoir fourni les éléments nécessaires.
Bonjour,
J'ai deux tables a et b qui contiennent respectivement 51212 et 45631 enregs.
Entre ces deux tables existent une relation (contrainte de clé étrangère).
J'ai fait un VACCUM, un analyse, j'ai augmenté les stats du champ b_ida à 1000
J'ai posé un index btree sur le même champ.
J'ai également posé un index btree sur le champs a_id
j'exécute la requête suivante:
SELECT b_ID FROM a JOIN b ON a_ID=b_IDa;
Temps d'excéction entre 8 secondes....n
Plan d'exécution :
Hash Join (cost=5560.70..8939.42 rows=40653 width=4)
Hash Cond: (b.b_ida = a.a_id)
-> Seq Scan on b (cost=0.00..2094.12 rows=51212 width=8)
-> Hash (cost=4834.31..4834.31 rows=45631 width=4)
-> Seq Scan on a (cost=0.00..4834.31 rows=45631 width=4)
Auriez-vous des idées pour améliorer les temps de réponse ?
D'avance merci de votre aide.