Temps de réponse et optimisation

HadanMarv · 21/07/2011 11:19:00

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.

gleu · 21/07/2011 11:29:14

Il nous faudrait le résultat d'un EXPLAIN ANALYZE. Il nous faudrait aussi la version de PostgreSQL (le planificateur change pas mal entre différentes versions majeures). Et le DDL des tables est un plus indéniable.

HadanMarv · 21/07/2011 11:55:05

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.

Dernière modification par HadanMarv (21/07/2011 11:55:41)

gleu · 21/07/2011 13:26:58

Je vois un temps d'exécution de 300ms dans le explain analyze. Il est difficile de croire qu'un parcours d'index ferait mieux. Essayez de forcer l'utilisation des index en positionnant enable_seqscan à off (juste dans le cadre de ce test). Quelle est la taille des tables d'ailleurs ?

HadanMarv · 21/07/2011 13:55:26

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.

SQLpro · 21/07/2011 14:17:40

Est-ce que id de A est une clef primaire ?, Est-ce que id de B est une clef primaire ?,
Si oui, pourquoi ne pas les créer à titre de clef primaire ?
Un index n'est pas la même chose qu'une clef car une clef primaire implique l'unicité alors que ce n'est pas le cas d'un index.
D’où votre hachage puisque les ID ne sont pas uniques !

De plus des tables de plus de 20 colonnes, ne représentent généralement pas des tables relationnelles mais une simple transposition de fichiers. Or un SGBRD est fait pour faites des tables et non des fichiers.
Bref, commencez par revoir votre conception de la base en faisant des vrais tables et non des fichiers.
Lisez l'article que j'ai écrit à ce sujet : http://blog.developpez.com/sqlpro/p1007 … s-petites/

En, sus, nommez vos colonnes avec de vrais noms et non pas F1 à F48. Et enfin, dans votre base de données, toutes les colonnes doivent avoir un nom différents, sauf celle qui servent de clef étrangères....

A +

gleu · 21/07/2011 14:18:44

Donc avec le enable_seqscan, il préfère utiliser un parcours d'index, mais ce parcours d'index prend 417 ms. Autrement dit plus long que le parcours séquentiel.

Le 8 secondes de traitement, vous le voyez comment ? à l'affichage sur un outil qui exécute cette requête ? dans les traces de PostgreSQL ?

HadanMarv · 21/07/2011 14:28:30

--> 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.

SQLpro · 21/07/2011 14:34:42

La question est donc, pourquoi avez vous fait un index sur a et sur b en sus de la clef primaire ????
Une clef primaire créé systématiquement un index. Il est donc inutile et dangereux d'en placer un second totalement redondant !

En sus augmenter le nombre de secteur dans l’histogramme statistique d'une clef primaire (donc unique) n'est généralement pas une bonne idée, car il faut lire les 1000 entrées de stats avant de décider du plan.... Une bonne valeur dans ce cas est plus proche de 100...

A +

Dernière modification par SQLpro (21/07/2011 14:45:19)

gleu · 21/07/2011 14:38:56

certes un peu plus long. le temps de 8 seconces m'est retournée par pgAdmin.

Alors la réponse est toute trouvée, les 8 secondes sont dûes au temps nécessaire pour afficher 45000 et quelques lignes dans le tableau d'affichage de l'outil de requêtes. La durée d'exécution de la requête en elle-même n'est que de 300 à 400 ms.

Une clef primaire créé systématiquement un index. Il est donc inutile et dangereux d'en placer un second totalement redondant !

Inutile oui, dangereux non.

SQLpro · 21/07/2011 14:47:00

Si c'est aussi dangereux, car cela peut l'induire en erreur. En effet entre un index non unique (donc avec des données supplémentaire de contrôle d'unicité) et un index forcément unique car clef primaire, l'optimiseur peut se tromper aisément et conduire à un plan stupide... Je commencerais par supprimer ces index !

A +

gleu · 21/07/2011 14:48:50

Je supprimerais moi-aussi ces index car ils ralentissent forcément tout ajout/modification/suppression dans la table. Mais le planificateur est assez malin pour faire la distinction entre les deux.

HadanMarv · 21/07/2011 15:10:38

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 ?

gleu · 21/07/2011 15:14:35

Aucun. Il y aura forcément un temps supplémentaire dû à l'affichage des résultats. psql sera certainement le plus rapide mais vous n'annulerez pas le temps d'affichage de toute façon.

Si ce qui vous intéresse est seulement la durée d'exécution de la requête, psql peut vous l'afficher si vous avez exécutez \timing avant votre requête. pgAdmin ne dispose pas d'une telle option.

HadanMarv · 21/07/2011 15:45:22

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 ?

gleu · 21/07/2011 16:01:44

Impossible de donner une explication sans voir le plan d'exécution des requêtes et les requêtes.

HadanMarv · 21/07/2011 16:55:59

la requête étant tellement imposante, le nombre de tables aussi, pouvez-vous me donner des pistes de recherche ?

flo · 21/07/2011 17:11:08

Cela ne va très probablement pas être possible de vous aider sans le texte de la requête, la définition précise des tables, et le plan d'exécution.

Que pourrait-on donner comme piste sans information? Il y a tellement de possibilités. Par expérience (la mienne, donc c'est plus côté développement), les causes principales de problèmes sont :
un mauvais schéma de données,
une requête inutilement complexe, voire fausse,
un développeur qui essaie d'optimiser alors qu'il n'a pas suffisemment d'expérience,
une base beaucoup trop sollicitée,
une requête de type "data mining" que l'on souhaite instantanée...
Je ne pense pas hélas pour vous que ces pistes vous aident. Les "recettes de cuisine" ne marchent généralement pas.

Si la requête est si imposante (mais l'est-elle vraiment?), peut-être est-elle inutilement complexe? (ce sont des choses qui arrivent). Donnez-la tout de même, en expliquant ce qu'elle essaie de faire. On pourra peut-être vous aider? (à la réécrire si c'est possible et souhaitable, ou à l'optimiser)

HadanMarv · 22/07/2011 14:17:18

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 çà ?

gleu · 22/07/2011 14:33:32

Vous vous rendez-compte du nombre de plans possibles avec autant de jointures ? si PostgreSQL devait chercher le meilleur plan d'exécution pour une telle requête, il pourrait passer plus de temps à le chercher qu'à exécuter un plan potentiellement moins bon. Donc, il existe un certain nombre de paramètres et d'algos pour déterminer à partir de quel moment PostgreSQL doit stopper une recherche exhaustive des plans de requêtes, pour en trouver un pas trop mauvais très rapidement.

Là, à mon avis, vous dépassez allègrement la valeur du join_collapse_limit, ce qui fait que PostgreSQL ne va pas chercher le plan d'exécution le meilleur. À priori, GEQO, un algo de recherche style génétique, est utilisé pour trouver un plan plutôt bon et renvoyer rapidement les résultats.

Bref, tout ça pour dire que je ne trouve pas étonnant pour une telle requête que l'ordre des jointures que vous donnez ait une importance.

flo · 22/07/2011 15:05:29

HadanMarv a écrit :

dont 2 sur des vues?

Ca c'est dangereux. Regardez la définition des vues. Faire des jointures sur des vues, c'est s'exposer à joindre inutilement plusieurs fois les mêmes tables (si la vue est elle-même constituée de jointures). Réécrivez la requête pour ne pas avoir de jointures inutiles, si c'est le cas.

HadanMarv a écrit :

des COALESCE dans les jointures

Bigre... ne pouvez-vous vraiment pas faire autrement?
Pourquoi avez-vous besoin de faire des coalesce DANS les jointures? Donnez-nous un exemple avec une explication : il y a peut-être plus simple et plus efficace.

HadanMarv a écrit :

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 çà ?

Gleu a raison, cela n'a rien d'étonnant (faites le calcul du nombre de permutations possibles sur 21 tables...)

HadanMarv · 22/07/2011 15:06:45

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 ?

gleu · 22/07/2011 15:08:09

La valeur par défaut est de 8. Qu'il soit en commentaire indique juste que PostgreSQL utilise cette valeur par défaut.

flo · 22/07/2011 15:16:38

Je vous conseille de travailler d'abord sur la requête avant de vous pencher sur les paramètres. Il y a très probablement des améliorations importantes à faire. Voir ce que j'ai écrit 1 min avant votre question (les messages se sont croisés sans doute)

HadanMarv · 22/07/2011 15:19:40

-> 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

#1 21/07/2011 11:19:00

Temps de réponse et optimisation

#2 21/07/2011 11:29:14

Re : Temps de réponse et optimisation

#3 21/07/2011 11:55:05

Re : Temps de réponse et optimisation

#4 21/07/2011 13:26:58

Re : Temps de réponse et optimisation

#5 21/07/2011 13:55:26

Re : Temps de réponse et optimisation

#6 21/07/2011 14:17:40

Re : Temps de réponse et optimisation

#7 21/07/2011 14:18:44

Re : Temps de réponse et optimisation

#8 21/07/2011 14:28:30

Re : Temps de réponse et optimisation

#9 21/07/2011 14:34:42

Re : Temps de réponse et optimisation

#10 21/07/2011 14:38:56

Re : Temps de réponse et optimisation

#11 21/07/2011 14:47:00

Re : Temps de réponse et optimisation

#12 21/07/2011 14:48:50

Re : Temps de réponse et optimisation

#13 21/07/2011 15:10:38

Re : Temps de réponse et optimisation

#14 21/07/2011 15:14:35

Re : Temps de réponse et optimisation

#15 21/07/2011 15:45:22

Re : Temps de réponse et optimisation

#16 21/07/2011 16:01:44

Re : Temps de réponse et optimisation

#17 21/07/2011 16:55:59

Re : Temps de réponse et optimisation

#18 21/07/2011 17:11:08

Re : Temps de réponse et optimisation

#19 22/07/2011 14:17:18

Re : Temps de réponse et optimisation

#20 22/07/2011 14:33:32

Re : Temps de réponse et optimisation

#21 22/07/2011 15:05:29

Re : Temps de réponse et optimisation

#22 22/07/2011 15:06:45

Re : Temps de réponse et optimisation

#23 22/07/2011 15:08:09

Re : Temps de réponse et optimisation

#24 22/07/2011 15:16:38

Re : Temps de réponse et optimisation

#25 22/07/2011 15:19:40

Re : Temps de réponse et optimisation

Pied de page des forums

À propos

Nous contacter

Groupes Locaux

Réseaux Sociaux