IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Requêtes PostgreSQL Discussion :

Optimisation base de données


Sujet :

Requêtes PostgreSQL

Vue hybride

Message précédent Message précédent   Message suivant Message suivant
  1. #1
    Membre averti
    Profil pro
    Inscrit en
    Février 2009
    Messages
    25
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Février 2009
    Messages : 25
    Par défaut Optimisation base de données
    Bonjour,

    Voici la structure de ma table :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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

  2. #2
    Expert éminent
    Avatar de CinePhil
    Homme Profil pro
    Ingénieur d'études en informatique
    Inscrit en
    Août 2006
    Messages
    16 818
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 62
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Ingénieur d'études en informatique
    Secteur : Enseignement

    Informations forums :
    Inscription : Août 2006
    Messages : 16 818
    Billets dans le blog
    14
    Par défaut
    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.
    Philippe Leménager. Ingénieur d'étude à l'École Nationale Supérieure de Formation de l'Enseignement Agricole, en retraite... mais toujours Autoentrepreneur à l'occasion.
    Mon ancien blog sur la conception des BDD, le langage SQL, le PHP... et mon nouveau blog sur les mêmes sujets.
    « Ce que l'on conçoit bien s'énonce clairement, et les mots pour le dire arrivent aisément ». (Nicolas Boileau)
    À la maison comme au bureau, j'utilise la suite Linux Mageïa !

  3. #3
    Membre averti
    Profil pro
    Inscrit en
    Février 2009
    Messages
    25
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Février 2009
    Messages : 25
    Par défaut
    Bonjour,

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

    Voici mes manipulations :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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 ?

  4. #4
    Expert éminent
    Avatar de CinePhil
    Homme Profil pro
    Ingénieur d'études en informatique
    Inscrit en
    Août 2006
    Messages
    16 818
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 62
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Ingénieur d'études en informatique
    Secteur : Enseignement

    Informations forums :
    Inscription : Août 2006
    Messages : 16 818
    Billets dans le blog
    14
    Par défaut
    Je ne suis pas habitué des EXPLAIN de Postgresql mais je vois ceci dans la requête :
    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 ?
    Philippe Leménager. Ingénieur d'étude à l'École Nationale Supérieure de Formation de l'Enseignement Agricole, en retraite... mais toujours Autoentrepreneur à l'occasion.
    Mon ancien blog sur la conception des BDD, le langage SQL, le PHP... et mon nouveau blog sur les mêmes sujets.
    « Ce que l'on conçoit bien s'énonce clairement, et les mots pour le dire arrivent aisément ». (Nicolas Boileau)
    À la maison comme au bureau, j'utilise la suite Linux Mageïa !

  5. #5
    Membre averti
    Profil pro
    Inscrit en
    Février 2009
    Messages
    25
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Février 2009
    Messages : 25
    Par défaut
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    CREATE INDEX "idx_matable_col1" ON "monschema"."matable" (col1 varchar_pattern_ops);
    Voir :
    - http://www.postgresql.org/docs/8.1/s...s-opclass.html

  6. #6
    Membre averti
    Profil pro
    Inscrit en
    Février 2009
    Messages
    25
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Février 2009
    Messages : 25
    Par défaut
    Apparemment pas d'amélioration avec cette indexation...

    Quelqu'un aurait une autre idée ?

  7. #7
    Membre averti
    Profil pro
    Inscrit en
    Février 2009
    Messages
    25
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Février 2009
    Messages : 25
    Par défaut
    Bonjour,

    Tout d'abord merci à tous pour votre aide.

    1. L'augmentation des statistics sur la colonne
      Citation Envoyé par estofilo Voir le message
      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 : Sélectionner tout - Visualiser dans une fenêtre à part
      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 :
      Citation Envoyé par StringBuilder Voir le message
      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 :
      Citation Envoyé par CinePhil Voir le message
      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

  8. #8
    Membre Expert

    Profil pro
    Inscrit en
    Mars 2005
    Messages
    1 683
    Détails du profil
    Informations personnelles :
    Âge : 42
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations forums :
    Inscription : Mars 2005
    Messages : 1 683
    Par défaut
    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.

  9. #9
    Membre averti
    Profil pro
    Inscrit en
    Février 2009
    Messages
    25
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Février 2009
    Messages : 25
    Par défaut
    Bonjour,

    Pour résumer j'ai donc placé des statistiques importantes sur les colonnes les plus utilisées :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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 ?

Discussions similaires

  1. [2008R2] Optimisation Base de données
    Par binohit dans le forum Administration
    Réponses: 22
    Dernier message: 06/04/2015, 10h30
  2. Optimisation base de données SQL Server 2000: Indexation
    Par Mouckson dans le forum MS SQL Server
    Réponses: 4
    Dernier message: 22/05/2012, 01h39
  3. Optimisation base de données
    Par nathann dans le forum PHP & Base de données
    Réponses: 2
    Dernier message: 14/04/2010, 16h47
  4. outils de optimisation base de donnée
    Par simomig29 dans le forum Outils
    Réponses: 5
    Dernier message: 12/02/2008, 08h35
  5. Optimisation base de données?
    Par Guizmo95 dans le forum Requêtes
    Réponses: 3
    Dernier message: 02/11/2006, 15h56

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo