Temps d'affichage très long [Résolu]

[linar009]

Bonjour,

En fait, j'ai effectué une migration de base de données de MySQL vers PostGreSQL pour une application PHP.
Avec MySQL, les pages s'affichaient rapidement et depuis que je suis passé sur PG, le temps d'affichage est prohibitif.
Pourriez-vous me dire pourquoi ? Existe-t-il des solutions pour pallier à ce problème ?

Merci par avance.

[Spoutnik]

hello,

les symptomes sont un peu vagues
Vérifie que tu as tes index, sinon, je vois pas trop ce qui pourrait être aussi net sur les temps

[linar009]

Il faut nécessairement des index ?

Envoyé par Spoutnik

hello,

les symptomes sont un peu vagues
Vérifie que tu as tes index, sinon, je vois pas trop ce qui pourrait être aussi net sur les temps

[linar009]

Ben je viens de mettre un index sur chaque table et tout reste inchangé (temps d'affichage très long)

[Spoutnik]

Envoyé par linar009

Ben je viens de mettre un index sur chaque table et tout reste inchangé (temps d'affichage très long)

Encore faut il qu'il soient correctement positionnés Ca se fait pas en 2 coups de cuillère!
Ca dépend aussi du volume de tes tables, les index vont pas changer grand chose sur une table de 150 lignes.
Et puis surtout, il faut qu'ils soient sur les bonnes colonnes!!

Mais comme je disais, les symtomes sont vagues . A part te donner des généralités, on pourra pas faire grand chose.
Localise quelles requetes te ralentissent, regarde ce qu'il se passe (cf EXPLAIN) et essaye de positionner/modifier les index si besoin est.
Et si ca vient pas de tes requetes, regarde ailleurs (genre un truc dans le code, je ne sais pas).

[linar009]

Ben j'ai des tables qui contiennent 500 000 enr. et j'ai placé mes index sur les clés primaires.
Pourrais-tu donner un peu plus d'explications sur EXPLAIN, merci.
Il existe aussi VACUUM je crois mais j'ai pas très bien compris.

[Spoutnik]

500 000, c'est pas très gros comme volumétrie, mais les index sont quand même nécessaires.
Pour expliquer rapidement, le explain donne le plan d'exécution de ta requête, ce que le moteur va effectuer comme opérations. cd EXPLAIN
(Si tu as des question plus précises, hésites pas )
Le VACUUM sert à libérer les tuples supprimés/MAJ (tout comme lorsque tu supprome un fichier, il n'est pas physiquement supprimé).
Et le VACCUM ANALYSE permet de recalculer les statistiques après les changement de sorte que l'optimiseur soit au plus près de la réalité et qu'il fasse les bons choix d'optimisations.

[linar009]

J'ai fait EXPLAIN de ma requête et j'ai obtenu ça :

Code :

1
2
3
4
5
6
7
8
"Nested Loop  (cost=0.00..6300.83 rows=5 width=353)"
"  ->  Nested Loop  (cost=0.00..6271.06 rows=5 width=264)"
"        ->  Seq Scan on t_planif  (cost=0.00..6240.96 rows=5 width=52)"
"              Filter: ((plan_date >= '2006-08-02'::date) AND (plan_date <= '2006-08-02'::date) AND (plan_status <> 3) AND (id_org ~~ '999'::text))"
"        ->  Index Scan using fki_trait_org_id_trait_org on t_trait_org  (cost=0.00..6.01 rows=1 width=216)"
"              Index Cond: ("OUTER".id_trait_org = t_trait_org.id_trait_org)"
"  ->  Index Scan using fki_traitements_id_trait on t_traitements  (cost=0.00..5.94 rows=1 width=97)"
"        Index Cond: ("OUTER".id_trait = t_traitements.id_trait)"

Ce qui n'est pas très clair à mes yeux

[Spoutnik]

Code :

1
2
3
4
"Nested Loop  (cost=0.00..6300.83 rows=5 width=353)"
"  ->  Nested Loop  (cost=0.00..6271.06 rows=5 width=264)"
"        ->  Seq Scan on t_planif  (cost=0.00..6240.96 rows=5 width=52)"
"              Filter: ((plan_date >= '2006-08-02'::date) AND (plan_date <= '2006-08-02'::date) AND (plan_status <> 3) AND (id_org ~~ '999'::text))"

Ici, tu utilise un "scan sequenciel" sur ta table 't_planif'. c'est à dire en gros qu'il lit chaque ligne pour vérifier la(les) condition(s).

Code :

1
2
"        ->  Index Scan using fki_trait_org_id_trait_org on t_trait_org  (cost=0.00..6.01 rows=1 width=216)"
"              Index Cond: ("OUTER".id_trait_org = t_trait_org.id_trait_org)"

Code :

1
2
"  ->  Index Scan using fki_traitements_id_trait on t_traitements  (cost=0.00..5.94 rows=1 width=97)"
"        Index Cond: ("OUTER".id_trait = t_traitements.id_trait)"

Sur ces 2 la, tu utilise un index. Le résultat sortira donc plus vite sur cette partie la.

Si tu veux un peu plus d'info, donnne les renseignements nécessaires : opérations create table, jeu d'essais, et requetes existantes .

++

[gerald2545]

autre question..le serveur mysql et postgresql sont sur une seule et même machine?

[linar009]

Envoyé par gerald2545

autre question..le serveur mysql et postgresql sont sur une seule et même machine?

Oui, en effet, Apache, MySQL et PostgreSQL tournent sur le même serveur.

[gerald2545]

ouais, et bien voir à bien positionner les index comme dit Spoutnik....désolé peut rien dire de plus.
à moins que tu n'aies pas eu de chance et que le serveur rame et est utilisé à fond depuis que tu es passé sous postgresql, je ne vois pas de quoi ça pourrait venir, problème réseau aussi?

[linar009]

Envoyé par Spoutnik

Si tu veux un peu plus d'info, donnne les renseignements nécessaires : opérations create table, jeu d'essais, et requetes existantes .

Ma principale requête dans ma page PHP :

Code :

1
2
3
4
5
6
7
8
9
10
11
SELECT plan_seq, plan_date AS dateplanif, plan_date_init AS dateplanif_init, 
plan_ord_day, T_planif.id_org, trait_nom, trait_lib, freq_type, feurouge_loc, trait_com, T_planif.id_trait_org, freq_date, 
plan_date, begin_end, T_traitements.id_trait, plan_status, date_validite, plan_histo, T_planif.id_group, is_group, est_compte 
FROM T_planif, T_trait_org, T_traitements 
WHERE T_planif.id_trait_org = T_trait_org.id_trait_org 
AND T_trait_org.id_trait = T_traitements.id_trait 
AND plan_date BETWEEN '$dplan_patern' AND '$dplan_patern2' 
AND plan_status != '3' 
AND T_planif.id_org LIKE '$urssaf%' 
AND freq_type != 'J' 
ORDER BY plan_date, plan_ord_day

Table t_planif :

Code :

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
CREATE TABLE t_planif
(
  plan_seq serial NOT NULL,
  id_org char(3) NOT NULL DEFAULT ''::bpchar,
  id_trait_org int8 NOT NULL DEFAULT 0,
  plan_date date NOT NULL DEFAULT '0001-01-01 BC'::date,
  plan_date_init date NOT NULL DEFAULT '0001-01-01 BC'::date,
  plan_ord_day int4 NOT NULL DEFAULT 0,
  plan_status int4 NOT NULL DEFAULT 0,
  plan_histo char(1),
  id_group int8,
  date_validite date,
  CONSTRAINT t_planif_pkey PRIMARY KEY (plan_seq),
  CONSTRAINT t_planif_plan_histo_chk CHECK (plan_histo = 'P'::bpchar OR plan_histo = 'V'::bpchar OR plan_histo = 'H'::bpchar OR plan_histo = 'M'::bpchar OR plan_histo = ''::bpchar)
) 
WITHOUT OIDS;
ALTER TABLE t_planif OWNER TO postgres;
 
 
-- Index: fki_id_trait_org
 
-- DROP INDEX fki_id_trait_org;
 
CREATE INDEX fki_id_trait_org
  ON t_planif
  USING btree
  (id_trait_org);

Table t_trait_org (contenant 560 000 enr.) :

Code :

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
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
CREATE TABLE t_trait_org
(
  id_trait_org serial NOT NULL,
  id_trait int8 NOT NULL DEFAULT 0,
  id_org char(3) NOT NULL DEFAULT ''::bpchar,
  trait_com varchar(255),
  freq_type varchar(2),
  freqtheo_type char(1),
  freq_date text NOT NULL,
  freq_pat text,
  begin_end char(3) NOT NULL DEFAULT 'CEN'::bpchar,
  plan_ord int4 NOT NULL DEFAULT 0,
  last_planif date,
  nb_param int4 NOT NULL DEFAULT 0,
  feurouge_loc char(3) NOT NULL DEFAULT 'NON'::bpchar,
  est_actif char(3) NOT NULL DEFAULT 'OUI'::bpchar,
  est_compte char(3) NOT NULL DEFAULT 'OUI'::bpchar,
  freq_type2 varchar(4),
  freq_pat2 text,
  CONSTRAINT t_trait_org_pkey PRIMARY KEY (id_trait_org),
  CONSTRAINT id_org FOREIGN KEY (id_org)
      REFERENCES t_orga (id_org) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT t_trait_org_begin_end_check CHECK (begin_end = 'BEG'::bpchar OR begin_end = 'CEN'::bpchar OR begin_end = 'END'::bpchar),
  CONSTRAINT t_trait_org_est_actif_chk CHECK (est_actif = 'OUI'::bpchar OR est_actif = 'NON'::bpchar OR est_actif = ''::bpchar),
  CONSTRAINT t_trait_org_est_compte_chk CHECK (est_compte = 'OUI'::bpchar OR est_compte = 'NON'::bpchar OR est_compte = ''::bpchar),
  CONSTRAINT t_trait_org_feurouge_loc_check CHECK (feurouge_loc = 'OUI'::bpchar OR feurouge_loc = 'NON'::bpchar OR feurouge_loc = 'VER'::bpchar),
  CONSTRAINT t_trait_org_freq_type2_check CHECK (freq_type2::text = 'IJS'::text OR freq_type2::text = 'IJM'::text OR freq_type2::text = 'R'::text OR freq_type2::text = 'RD'::text OR freq_type2::text = 'RARD'::text OR freq_type2::text = 'L'::text OR freq_type2::text = ''::text OR freq_type2::text = ' '::text),
  CONSTRAINT t_trait_org_freq_type_chk CHECK (freq_type::bpchar = 'J'::bpchar OR freq_type::bpchar = 'H'::bpchar OR freq_type::bpchar = 'M'::bpchar OR freq_type::bpchar = 'T'::bpchar OR freq_type::bpchar = 'A'::bpchar OR freq_type::bpchar = 'U'::bpchar OR freq_type::bpchar = 'I'::bpchar OR freq_type::bpchar = 'FM'::bpchar OR freq_type::bpchar = 'FT'::bpchar OR freq_type::bpchar = 'P'::bpchar OR freq_type::bpchar = 'S'::bpchar OR freq_type::bpchar = 'NA'::bpchar OR freq_type::bpchar = ''::bpchar),
  CONSTRAINT t_trait_org_freqtheo_type_chk CHECK (freqtheo_type = 'M'::bpchar OR freqtheo_type = 'T'::bpchar OR freqtheo_type = ''::bpchar)
) 
WITHOUT OIDS;
ALTER TABLE t_trait_org OWNER TO postgres;
 
 
-- Index: fki_trait_org_id_trait_org
 
-- DROP INDEX fki_trait_org_id_trait_org;
 
CREATE INDEX fki_trait_org_id_trait_org
  ON t_trait_org
  USING btree
  (id_trait_org);

Table T_traitements :

Code :

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
CREATE TABLE t_traitements
(
  id_trait bigserial NOT NULL,
  trait_nom varchar(10) NOT NULL DEFAULT ''::character varying,
  trait_lib varchar(100),
  trait_fonc varchar(255),
  trait_typesaisie varchar(10) DEFAULT 'SAI2'::character varying,
  trait_visible varchar(50) NOT NULL DEFAULT 'ALL'::character varying,
  is_group char(3) NOT NULL DEFAULT 'NON'::bpchar,
  trait_application varchar(20)[],
  CONSTRAINT t_traitements_pkey PRIMARY KEY (id_trait),
  CONSTRAINT t_traitements_is_group_check CHECK (is_group = 'OUI'::bpchar OR is_group = 'NON'::bpchar)
) 
WITHOUT OIDS;
ALTER TABLE t_traitements OWNER TO postgres;
 
 
-- Index: fki_traitements_id_trait
 
-- DROP INDEX fki_traitements_id_trait;
 
CREATE INDEX fki_traitements_id_trait
  ON t_traitements
  USING btree
  (id_trait);

[gerald2545]

à première vue, je ferai un index sur T_planif.id_org

[linar009]

On peut mettre plusieurs index sur une même table ?

[gerald2545]

On peut mettre plusieurs index sur une même table ?

tout à fait, même recommandé dans certains cas

EDIT : je ne sais pas si un index sur une colonne date apporte quelquechose, ainsi que sur les clés étrangères....avis aux professionnels du SQL

[Spoutnik]

Envoyé par linar009

On peut mettre plusieurs index sur une même table ?

je regarderai ca quand j aurais un peu plus de temps. Pour répondre à ta question, tu en met AUTANT que tu veut. Reste a bien les choisir pour pas augmenter le volume global de ta base (ca prend de la place de stcker les index, ca ralenti l insertion, etc...)

Tu peux faire des index sur 1 à n colonnes, et/ou utiliser des expressions/functions (par ex des reg ex, etc ...)

Envoyé par gerald2545

EDIT : je ne sais pas si un index sur une colonne date apporte quelquechose, ainsi que sur les clés étrangères....avis aux professionnels du SQL

Bien sur que ca apporte qq chose. Tout dépend de tes requetes. Les dates sont des info comme les autres! Tu ira plus vite si tu sais faire de sorte que ta date soit sélective et qu'elle est indexée si dans ta requete tu as besoin de trier selon la date.

[linar009]

Bien joué gerald2545 ! Le temps d'affichage est quelque peu plus rapide avec un index sur id_org de T_planif!
Malheureusement l'affichage de la page reste prohibitif...

J'ai oublié de te dire que ce n'est pas la seule requête que j'effectue au lancement de la page, mais il y en au moins 6 et je ne veux pas te faire ch*** avec tout ça...

[linar009]

Envoyé par Spoutnik

Pour répondre à ta question, tu en met AUTANT que tu veut. Reste a bien les choisir pour pas augmenter le volume global de ta base (ca prend de la place de stcker les index, ca ralenti l insertion, etc...)

Ok, mais après je ne vois pas vraiment quels sont les critères pour choisir les champs qui deviendront des index...

[gerald2545]

en fait, il faut essayer de positionner les index sur les colonnes contenant du texte qui sont utilisées dans ta clause WHERE, surtout pour les colonnes text sur lesquelles des recherche de type LIKE sont effectuées...enfin c'est mon avis de façon plus ou moins intuitive