Vitesse d'exécution d'une requête

**Shiva** · 17/07/2015, 19h36

Bonjour,

J'ai une table d'article contenant + de 2300000 lignes.
Quand je fais une une simple requête de ce style :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
 
SELECT id AS id_article, titre, ean, auteur
FROM site.article
WHERE (titre LIKE UPPER('%harrington%') OR auteur LIKE UPPER('%harrington%'))
ORDER BY titre ASC;

Le temps de réponse est de 2 à 3 secondes.
J'ai indexé les champs id, ean et titre et je ne sais pas si c'est vraiment utile pour le champ titre.
Voici la structure de la table article :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
          Colonne           |       Type        |                         Modificateurs
----------------------------+-------------------+---------------------------------------------------------------
 id                         | integer           | non NULL Par défaut, nextval('site.article_id_seq'::regclass)
 ean                        | bigint            |
 isbn                       | character(10)     |
 titre                      | character varying | non NULL
 auteur                     | character varying |
Index :
    "article_pkey" PRIMARY KEY, btree (id)
    "article_ean_key" UNIQUE CONSTRAINT, btree (ean)
    "idx_article_id" btree (id)
    "idx_article_ean" btree (ean)
    "idx_article_titre" btree (titre)

Sinon, quel serait le moyen d’accélérer la recherche ? Si cela est possible biensur.

Merci de vos conseils :-)

**gorgonite** · 17/07/2015, 20h57

tsvector et index gist/gin

Regardes chapitre recherche plein texte de la FAQ

**Shiva** · 17/07/2015, 23h40

ok, merci, je regarde cela.

**Waldar** · 20/07/2015, 11h25

Il faudrait aussi vérifier le plan d'exécution, je ne sais pas si PostgreSQL sait réécrire la requête de cette façon :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
  SELECT id AS id_article, titre, ean, auteur
    FROM site.article
   WHERE titre LIKE UPPER('%harrington%')
   UNION
  SELECT id AS id_article, titre, ean, auteur
    FROM site.article
   WHERE auteur LIKE UPPER('%harrington%')
ORDER BY titre ASC;

Dans cette requête, l'accès se fait par l'index sur titre pour la première partie et par l'index sur auteur sur la seconde.

**escartefigue** · 20/07/2015, 12h14

Non, car il y a à la fois une fonction de colonne sur les valeurs recherchées et un wildcard "%" en début de chaine de caractères
Donc requête non sargable

Seule une recherche plein texte peut satisfaire ce type de besoin

Cela dit, 2 à 3 sec c'est pas si mal pour une requete non sargable qui concerne 2 300 000 lignes parcourues 2 fois puisque "OR"

**Waldar** · 20/07/2015, 12h32

Ce n'est pas en opposition à la requête plain text, c'est en complément.

**Shiva** · 20/07/2015, 21h27

Je viens de tester avec les champs titre et auteur en rajoutant un champ de type TSVECTOR et en le mettant ensuite à jour comme il faut.
La requête suivante me retourne un résultat en 2,393 ms :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT titre, ts_rank(vector_titre_auteur, to_tsquery('harrington')) FROM site.article WHERE vector_titre_auteur @@ to_tsquery('harrington');

Ce qui est nickel.
Par contre autre question :
Je vais devoir inclure dans ma recherche des données provenant de tables différente, par exemple les tables editeur, disponibilite, theme, collection.
Je dois donc être capable de lancer une recherche sur la table article et ces tables supplémentaire.
Est-il possible de faire une recherche FTS sur plusieurs tables en même temps ?

**alassanediakite** · 22/07/2015, 19h17

salut
2,393 ms= deux mille trois cent quatre vingt treize milliseconde (nous somme chez les angalis

), alors

Ce qui est nickel.

Par ailleurs, utiliser du full text pour chercher un nom propre, je n'en vois pas l'utilité.
Je dirais même plus

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Cela dit, 2 à 3 sec c'est pas si mal pour une requete non sargable qui concerne 2 300 000 lignes parcourues 2 fois puisque "OR"

c'est pas mal du tout.
@+

**Waldar** · 22/07/2015, 19h50

J'ai du mal à comprendre votre intervention alassanediakite.
Vous conseillez donc à Shiva de supprimer sa solution qui répond en 2 ms pour une solution qui répond en 3 secondes parce que ça vous paraît assez rapide ?

**Shiva** · 23/07/2015, 00h36

Envoyé par alassanediakite

salut
Par ailleurs, utiliser du full text pour chercher un nom propre, je n'en vois pas l'utilité.

Ce n'est qu'un exemple... J'aurai pu faire une recherche sur cela : "Honor Harrington David Weber". Sans recherche FTS je dois rechercher sur les champs titre et auteur. Et aussi comment différencier le titre de l'auteur dans les mots envoyés vu que ceux ci sont saisi dans un seul champ ? Avec FTS pas besoin de se poser la question et c'est rapide :-)

**alassanediakite** · 25/07/2015, 13h20

Salut
Je voudrais comprendre

Envoyé par Shiva

La requête suivante me retourne un résultat en 2,393 ms :

et

Envoyé par Waldar

...solution qui répond en 2 ms pour une solution qui répond en 3 secondes parce que ça vous paraît assez rapide ?

Peut-être que j'ai bien compris le full text. Alors, une question: le full text permet-il d’accélérer des recherches sur des noms propres?
Merci et @+

**escartefigue** · 27/07/2015, 15h54

Envoyé par alassanediakite

Salut
Alors, une question: le full text permet-il d’accélérer des recherches sur des noms propres?
Merci et @+

Where nom like '%martin%' => faire un index full text si recherche fréquente et/ou table de plusieurs millions de lignes
where nom like 'martin%' l'index full text pas n'est pas nécessaire d'un point de vue perfs (mais il a plein d'autres arguments)

**alassanediakite** · 29/07/2015, 16h37

salut
OK, merci
@+

**SQLpro** · 29/07/2015, 17h39

Envoyé par escartefigue

Where nom like '%martin%' => faire un index full text si recherche fréquente et/ou table de plusieurs millions de lignes
where nom like 'martin%' l'index full text pas n'est pas nécessaire d'un point de vue perfs (mais il a plein d'autres arguments)

Il y a une autre solution plus pertinente pour ce cas de figure qui consiste à utiliser des index rotatifs....

A +

**gorgonite** · 29/07/2015, 20h53

Envoyé par SQLpro

Il y a une autre solution plus pertinente pour ce cas de figure qui consiste à utiliser des index rotatifs....

Un lien ? Cela marche sur PostgreSQL ou est-ce une exclusivité SQL Server ?

**SQLpro** · 29/07/2015, 22h59

ça n'est pas implémenté à ma connaissance dans aucun SGBDR, mais dans certains moteurs. Par exemple dans Microsoft exchange pour la recherche des comptes mails.
Je l'ai mis en place dans le cadre d'une application de text mining pour Intellixir sous MS SQL Server.
Mais tu peut faire cela dans n'importe quel SGBDR avec un peu d'astuce !
http://blog.developpez.com/sqlpro/p1...sql_sargable_c
http://sqlpro.developpez.com/cours/quoi-indexer/
http://sqlpro.developpez.com/sqlserv...tion/requetes/

A +

**estofilo** · 05/08/2015, 05h29

Il faut utiliser le module pg_trgm avec un index GIST qui sont faits exactement pour ce type de recherche.

La doc:

http://postgresql.developpez.com/doc...tion/francais/

Citation:

À partir de PostgreSQL™ 9.1, ces types d'index supportent aussi les recherches d'index pour LIKE et ILIKE, par exemple

SELECT * FROM test_trgm WHERE t LIKE '%foo%bar';

**SQLpro** · 05/08/2015, 18h15

Envoyé par estofilo

Il faut utiliser le module pg_trgm avec un index GIST qui sont faits exactement pour ce type de recherche.

La doc:

http://postgresql.developpez.com/doc...tion/francais/

Citation:

Effectivement, c'est une méthode similaire, mais un peu plus couteuses que l'index rotatif.

A +

Vitesse d'exécution d'une requête

Requêtes PostgreSQL

Discussions similaires

Partager

Partager