Optimisation base de données

[denis13]

Bonjour,

Voici la structure de ma table :

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
 
CREATE SEQUENCE "monschema"."seq_matable_id" INCREMENT 1  MINVALUE 1 MAXVALUE 9223372036854775807  START 1 CACHE 1;
 
-- Ajout table : matable
CREATE TABLE "monschema"."matable"
(
	id bigint NOT NULL DEFAULT NEXTVAL('consult.seq_matable_id'),
	col1 character(12) NOT NULL,
	col2 character varying(14) NULL,
	col3 character(12) NULL,
	col4 character varying(120) NOT NULL,
	datecreation character(8) NOT NULL,
	evenements character varying(79) NOT NULL,
	base character(1) NOT NULL,
	contenuxml bytea NOT NULL,
    CONSTRAINT "pk_matable_id" PRIMARY KEY (id),
    CONSTRAINT "uk_matable_col1_datecreation" UNIQUE (col1,datecreation)
)
WITH (
  OIDS=FALSE
);
 
CREATE INDEX "idx_matable_col1" ON "monschema"."matable" USING btree ("col1");
CREATE INDEX "idx_matable_col2" ON "monschema"."matable" USING btree ("col2");
CREATE INDEX "idx_matable_col3" ON "monschema"."matable" USING btree ("col3");
CREATE INDEX "idx_matable_col4" ON "monschema"."matable" USING btree ("col4");

La requête de recherche est :

Code :

1
2
 
SELECT * FROM matable WHERE colX LIKE 'mavaleur%' AND base IN (baselist) ORDER BY datecreation ASC;

où :
- colX = col1 ou col2 ou col3 ou col4.
- mavaleur = un string d'au moins 4 caractères.
- baselist = liste des bases possible 3 cas possible :

+ 'E'

+ 'A'

+ 'E','A'

La table contenant plus de 1,5Millions de ligne.



Mon problème est que lorsque je réalise une requête pour la première fois sur une colonne (ex :col1) où la valeur est de 4 caractères et le nombre d’occurrences de cette valeur dans la table est important (plus 50 000). Ma requête dure plus de 3 min.
Par contre une fois cette requête réalisée le temps de réponse sur la même requête est de moins d'une seconde (merci l'indexation je suppose).

Je souhaiterai donc savoir si ce comportement est normal ?

D'après ce que je crois avoir compris, lors de la première exécution de la requête postgres vérifie si le plan d'exécution existe déjà, évalue s'il est plus rapide de passer par les index.
Comme dans mon cas il n'y a pas eu d'indexation sur cette requête postgres doit se taper toute la table et faire l'indexation (ce qui prendrait du temps je suppose).

Du coup y a-t-il des moyens de gagner du temps sur cette première indexation ?

Peut être ai-je mal compris si oui quelqu'un pourrait-il m'expliquer le mécanisme d'indexation succinctement et m'aider à trouver une solution ?

Merci d'avance,

DD

[CinePhil]

L'indexation est faite lorsque tu crées l'index et elle est mise à jour quand tu fais des ajouts, modifications, suppressions de données.

Lorsque tu lances une requête, le SGBD évalue quel(s) index il est intéressant d'utiliser.

Ce qui fait que ta requête s'exécute plus vite à partir de la deuxième fois où tu la lances, c'est que le SGBD a encore en mémoire la requête et le résultat et regarde juste si un événement a pu modifier le résultat entre deux appels de la requête. Si ce n'est pas le cas, il donne le résultat qu'il a en mémoire sans exécuter une nouvelle fois la requête. Du moins, je crois que c'est comme ça que ça se passe.

Ce qui plombe peut-être l'exécution de ta requête, c'est le ORDER BY sur une colonne non indexée.

[denis13]

Bonjour,

J'ai bien suivi vos conseils mais cela n'a pas amélioré les performances.

Voici mes manipulations :

Code :

1
2
3
4
 
CREATE INDEX "idx_matable_datecreation" ON "monschema"."matable" USING btree ("datecreation");
 
VACUUM FULL ANALYSE monschema.matable;

Puis j'ai réalisé une requête simple qui doit me remonter beaucoup de données (13320) :

Code :

1
2
 
SELECT * FROM monschema.matable WHERE col1 LIKE 'SARL %' AND base IN('E','A') ORDER BY datecreation LIMIT 150;

La requête mets 3min 12s pour renvoyer le résultat !!!

Pour information voici le résultat de mon EXPLAIN :

Code :

1
2
3
4
5
6
7
 
 LIMIT  (cost=16.70..16.71 rows=3 width=1492)
   ->  Sort  (cost=16.70..16.71 rows=3 width=1492)
         Sort KEY: datecreation
         ->  INDEX Scan USING idx_matable_col1 ON matable  (cost=0.00..16.67 rows=3 width=1492)
               INDEX Cond: (((col1)::text >= 'SARL'::character varying) AND ((col1)::
               Filter: (((col1)::text ~~ 'SARL%'::text) AND (base = ANY ('{E,A}'::bpchar[])))

C'est la galère snif...

D'autres idées ou raisons sur cette lenteur ?

[CinePhil]

Je ne suis pas habitué des EXPLAIN de Postgresql mais je vois ceci dans la requête :

Code :

LIKE 'LES %'

et cela dans l'EXPLAIN :

Filter: (((col1)::text ~~ 'SARL%'::text)

J'ai l'impression que l'EXPLAIN ne correspond pas à la requête !

Ton résultat escargot de 3mn12 est obtenu en lançant la requête directement sur le serveur ou via un programme applicatif ?

[denis13]

En fait j'ai pas copié la bonne requête, c'est juste que j'avais réalisé une autre requête avec "LES %".

L'EXPLAIN est bon.

Je suis en train de plancher sur une modification de mes index du type :

Code :

CREATE INDEX "idx_matable_col1" ON "monschema"."matable" (col1 varchar_pattern_ops);

Voir :
- http://www.postgresql.org/docs/8.1/s...s-opclass.html

[denis13]

Apparemment pas d'amélioration avec cette indexation...

Quelqu'un aurait une autre idée ?

[vmolines]

EXPLAIN ANALYZE au lieu d'un simple EXPLAIN pour avoir plus d'informations.

[denis13]

Ci-dessous les deux cas de requêtes que j'ai testé. Mais aucune amélioration dans les 2 cas.

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Avec les index simple :

Code :

1
2
3
4
5
CREATE INDEX "idx_matable_col1" ON "monschema"."matable" (USING btree ("col1");
CREATE INDEX "idx_matable_col2" ON "monschema"."matable" (USING btree ("col2");
CREATE INDEX "idx_matable_col3" ON "monschema"."matable" (USING btree ("col3");
CREATE INDEX "idx_matable_col4" ON "monschema"."matable" (USING btree ("col4");
CREATE INDEX "idx_matable_datecreation" ON "monschema"."matable" (USING btree ("datecreation");

Voici le résultat :

Code :

1
2
3
4
5
6
7
8
9
10
11
EXPLAIN ANALYZE SELECT * FROM monschema.matable WHERE col1 LIKE 'SARL%' AND base IN('E','A') ORDER BY datecreation DESC LIMIT 150;
                                                                       QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------
 LIMIT  (cost=16.60..16.61 rows=3 width=1492) (actual time=268326.403..268326.556 rows=150 loops=1)
   ->  Sort  (cost=16.60..16.61 rows=3 width=1492) (actual time=268326.401..268326.502 rows=150 loops=1)
         Sort KEY: datecreation
         ->  INDEX Scan USING idx_matable_col1 ON matable  (cost=0.00..16.57 rows=3 width=1492) (actual time=6.974..267016.362 rows=24570 loops=1)
               INDEX Cond: (((col1)::text >= 'SARL'::character varying) AND ((col1)::text < 'SARM'::character varying))
               Filter: (((col1)::text ~~ 'SARL%'::text) AND (base = ANY ('{E,A}'::bpchar[])))
 Total runtime: 269202.586 ms
(7 rows)

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Avec les index suivant :

Code :

1
2
3
4
5
CREATE INDEX "idx_matable_col1" ON "monschema"."matable" (col1 varchar_pattern_ops);
CREATE INDEX "idx_matable_col2" ON "monschema"."matable" (col2 bpchar_pattern_ops);
CREATE INDEX "idx_matable_col3" ON "monschema"."matable" (col3 varchar_pattern_ops);
CREATE INDEX "idx_matable_col4" ON "monschema"."matable" (col4 bpchar_pattern_ops);
CREATE INDEX "idx_matable_datecreation" ON "monschema"."matable" (datecreation bpchar_pattern_ops);

Voici le résultat :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
EXPLAIN ANALYZE SELECT * FROM monschema.matable WHERE col1 LIKE 'SARL%' AND base IN('E','A') ORDER BY datecreation DESC LIMIT 150;
 
                                                                        QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------
 LIMIT  (cost=16.61..16.62 rows=3 width=1491) (actual time=242243.637..242243.796 rows=150 loops=1)
   ->  Sort  (cost=16.61..16.62 rows=3 width=1491) (actual time=242243.634..242243.742 rows=150 loops=1)
         Sort KEY: datecreation
         ->  INDEX Scan USING idx_matable_col1 ON matable  (cost=0.00..16.59 rows=3 width=1491) (actual time=24.563..240824.383 rows=24570 loops=1)
               INDEX Cond: (((col1)::text ~>=~ 'SARL'::character varying) AND ((col1)::text ~<~ 'SARM'::character varying))
               Filter: (((col1)::text ~~ 'SARL%'::text) AND (base = ANY ('{E,A}'::bpchar[])))
 Total runtime: 243106.740 ms
(7 rows)

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Des suggestions ?

[estofilo]

D'après cette ligne:

Code :

INDEX Scan USING idx_matable_col1 ON matable  (cost=0.00..16.59 rows=3 width=1491) (actual time=24.563..240824.383 rows=24570 loops=1)

il semble que l'optimiseur s'attend à ce que 3 lignes remplissent la condition alors qu'en fait il y en a 24570. Peut-être qu'avec une meilleure estimation il choisirait un plan d'exécution meilleur.

Le temps 24.563...2480824 veut dire que le 1er résultat est obtenu au bout de 24s et le dernier au bout de 240824s, donc l'essentiel du temps d'exécution est passé dans cette opération.

Pour améliorer les stats, tu peux essayer d'augmenter la taille de l'échantillon avec ALTER TABLE .. ALTER COLUMN.. SET STATISTICS 1000
sur les colonnes col1 et base.

1000 est une valeur élevée, par défaut c'est 10 avec les versions assez anciennes de postgres et je crois 100 avec des versions relativement récentes.

Ensuite faire un ANALYZE pour que ces stats soient recalculées (ne pas faire de VACUUM FULL qui est plutôt contre-productif pour les index sauf si on fait un REINDEX derrière).

[denis13]

Ok merci merci pour ces conseils éclairés, je m'en vais tout de suite tester.

Je vous tiens au courant.

[StringBuilder]

Pourquoi ne pas rajouter "base" en première position de chacun des quatre index ?

Si les valeurs de base sont exclusivement E ou A, au pire, dans le cas où il est égale à "E, A" alors 100% de l'index sera lu pour rechercher le like. Mais quand il sera E ou A, virtuellement, la moitié de l'index ne sera pas lu : c'est tout de même une bonne optimisation.

Si le champ peut contenir d'autres valeurs, alors tu devrais gagner encore plus.

Aussi, l'index sur datecreation ne sert à rien selon moi : vu qu'il utilise un autre index pour la recherche, il ne va pas utiliser un second index pour trier le résultat, qui n'est plus une image de ce qu'il y a dans la table.

A nouveau, il faudrait rajouter datecreation à la fin dans tes quatres premiers index.
=> Ceci dit, je ne pense pas que ça change grand chosE.

[denis13]

Bonjour,

Tout d'abord merci à tous pour votre aide.

1. L'augmentation des statistics sur la colonne
   

   Envoyé par estofilo

   D'après cette ligne:
   Pour améliorer les stats, tu peux essayer d'augmenter la taille de l'échantillon avec ALTER TABLE .. ALTER COLUMN.. SET STATISTICS 1000
   sur les colonnes col1 et base.
   
   1000 est une valeur élevée, par défaut c'est 10 avec les versions assez anciennes de postgres et je crois 100 avec des versions relativement récentes.
   
   Ensuite faire un ANALYZE pour que ces stats soient recalculées (ne pas faire de VACUUM FULL qui est plutôt contre-productif pour les index sauf si on fait un REINDEX derrière).

   Ça a bien marché, je suis passé de 3min de requête à 1min !!
   
   En effet voici l'analyse :
   
   Code :

   1
   2
   3
   4
   5
   6
   7
   8
   9
   10
   11
   12
   13
    
   EXPLAIN ANALYZE SELECT * FROM monschema.matable WHERE col1 LIKE 'SARL%' AND base IN('E','A') ORDER BY datecreation DESC LIMIT 150;
                                                                          QUERY PLAN
   --------------------------------------------------------------------------------------------------------------------------------------------------------
    LIMIT  (cost=106979.34..106979.72 rows=150 width=1491) (actual time=1653.743..1653.892 rows=150 loops=1)
      ->  Sort  (cost=106979.34..107039.26 rows=23969 width=1491) (actual time=1653.740..1653.838 rows=150 loops=1)
            Sort KEY: datecreation
            ->  Bitmap Heap Scan ON matable  (cost=657.05..73938.73 rows=23969 width=1491) (actual time=37.393..326.432 rows=24570 loops=1)
                  Filter: (((col1)::text ~~ 'SARL%'::text) AND (base = ANY ('{E,A}'::bpchar[])))
                  ->  Bitmap INDEX Scan ON idx_matable_col1  (cost=0.00..651.06 rows=23453 width=0) (actual time=33.843..33.843 rows=24570 loops=1)
                        INDEX Cond: (((col1)::text ~>=~ 'SARL'::character varying) AND ((col1)::text ~<~ 'SARM'::character varying))
    Total runtime: 2166.705 ms
   (8 rows)

2. La colonne datecreation :
   

   Envoyé par StringBuilder

   Aussi, l'index sur datecreation ne sert à rien selon moi : vu qu'il utilise un autre index pour la recherche, il ne va pas utiliser un second index pour trier le résultat, qui n'est plus une image de ce qu'il y a dans la table.
   
   A nouveau, il faudrait rajouter datecreation à la fin dans tes quatres premiers index.
   => Ceci dit, je ne pense pas que ça change grand chosE.

   En fait je l'ai rajouté suite au post :
   

   Envoyé par CinePhil

   Ce qui plombe peut-être l'exécution de ta requête, c'est le ORDER BY sur une colonne non indexée.

   Je vais réaliser quelques tests... je vous tiens au courant

[vmolines]

Vous avez encore changé des choses dans la structure de vos tables. On a maintenant une colonne "liasse" ainsi qu'un index dont on a pas la définition.

Vous dites que la requête met 1mn à s'exécuter alors que l'explain indique 2s.

Merci d'être plus précis dans les éléments que vous fournissez.

Pour ce qui est des colonnes à indexer, je ne pense pas que mettre la colonne base en premier aide les performances. Cette colonne de doit pas être la plus discriminante et la dénomination semble une meilleure candidate.

Pour ce qui est de la colonne date création, idem, je ne pense pas que l'inclusion à l'index aide les performances car il ne sera pas utilisable directement vu les filtres sur les autres colonnes. A tester.

[denis13]

Bonjour,

Pour résumer j'ai donc placé des statistiques importantes sur les colonnes les plus utilisées :

Code :

1
2
3
4
5
6
ALTER TABLE monschema.matable ALTER COLUMN col1 SET STATISTICS 1000;
ALTER TABLE monschema.matable ALTER COLUMN col2 SET STATISTICS 100;
ALTER TABLE monschema.matable ALTER COLUMN col3 SET STATISTICS 100;
ALTER TABLE monschema.matable ALTER COLUMN col4 SET STATISTICS 100;
ALTER TABLE monschema.matable ALTER COLUMN codesite SET STATISTICS 1000;
ALTER TABLE monschema.matable ALTER COLUMN base SET STATISTICS 1000;

Il faut ajouter à ceci une contrainte d'unicité uk_matable_col2_datecreation sur (col2,datecreation).

Puis j'ai mis mes index comme suit :

Code :

1
2
3
4
5
6
 
CREATE INDEX "idx_matable_col1" ON "monschema"."matable" USING btree ("col1");
CREATE INDEX "idx_matable_col1_complexe" ON "monschema"."matable" USING btree ("col1", "codesite", "base");
CREATE INDEX "idx_matable_col2_complexe" ON "monschema"."matable" USING btree ("col2", "codesite", "base");
CREATE INDEX "idx_matable_col3_complexe" ON "monschema"."matable" USING btree ("col3", "codesite", "base");
CREATE INDEX "idx_matable_col4_complexe" ON "monschema"."matable" USING btree ("col4", "codesite", "base");

Pour deux types de requêtes utilisées :
- requête pour la recherche :

Code :

SELECT * FROM monschema.matable WHERE colx LIKE 'unevaleur%' AND codesite IN ('004') AND base IN ('E', 'A') ORDER BY datecreation DESC LIMIT 150

où colx peut être : col1, col2, col3 ou col4
où base peut être : ('E') ou ('A') ou ('E', 'A')
où codesite peut être une liste ou pas d'une dizaine de valeur de type 'XXX'
Sachant que la valeur la plus complexe à rechercher est col1.

- requête pour afficher un résultat :

Code :

SELECT * FROM monschema.matable WHERE col2='mavaleur' AND datecreation='20060401'

car col2 et datecreation est une clé unique

J'ai de bonne performances : je suis passé à environ 8s sur la première recherche de termes complexes.

Cela vous parait-il normal ?