Forums PostgreSQL.fr

dangan

Membre

Optimisation recherche plein texte like et vector

Bonjour à toutes et tous !

Après plusieurs recherche sur internet, je n'arrive pas à trouver de solution pour faire une recherche dans un texte, qui soit rapide et qui me rende des données sans approximation syntaxique.

Voici ma requête :

SELECT ir_inst_rech_entree FROM ir_instrument_document_recherche WHERE ir_inst_rech_doc_col2 like '% nouveau pont %' AND ir_inst_rech_doc_vecteur @@ to_tsquery('nouveau&pont')

Et la structure de ma table :

CREATE TABLE ir_instrument_document_recherche(
  id_ir_instrument_document_recherche integer NOT NULL DEFAULT nextval('ir_instrument_document_recher_id_ir_instrument_document_rec_seq'::regclass),
  ir_inst_rech_entree integer,
  ir_inst_rech_doc_col1 text,
  ir_inst_rech_doc_col2 text,
  ir_inst_rech_doc_vecteur tsvector,
  CONSTRAINT pk_ir_instrument_document_recherche PRIMARY KEY (id_ir_instrument_document_recherche)
)
WITH (OIDS=FALSE);

ALTER TABLE ir_instrument_document_recherche
OWNER TO postgres;

CREATE INDEX idx_inst_rech_doc_col2
  ON ir_instrument_document_recherche
  USING gin
  (ir_inst_rech_doc_col2 COLLATE pg_catalog."default" gin_trgm_ops);

CREATE INDEX idx_ir_instrument_document_recherche_vect
  ON ir_instrument_document_recherche
  USING gin
  (ir_inst_rech_doc_vecteur);

CREATE TRIGGER tg_inst_doc_recherche
  BEFORE INSERT OR UPDATE
  ON ir_instrument_document_recherche
  FOR EACH ROW
  EXECUTE PROCEDURE ir_update_recherche_document_vecteur();

Si j'enlève le like, ma requête se fait en 55ms, avec le like, plus de 60 secondes.
Sans le like je retrouve tout un tas d'approximation syntaxique, chose que je ne souhaite pas.

Comme montré dans la table, j'ai mis un index sur mon texte, mais lors de la procédure SQL l'index n'est pas du tout utilisé..

En vous remerciant d'avance de votre aide.

rjuju

Administrateur

Re : Optimisation recherche plein texte like et vector

Bonjour,

Un index simple ne peut pas être utilisé dans ce cas (un % sur la gauche de l'expression). Vous pouvez utiliser l'extension pg_trgm qui permet de passer par un index. Exemple :

[rjuju]=# create extension pg_trgm ;
CREATE EXTENSION
Time: 287.783 ms
[rjuju]=# create index ON document using gin(contenu gin_trgm_ops);
CREATE INDEX
Time: 539.292 ms
[rjuju]=# explain select * from document where contenu like '%123%';
                                      QUERY PLAN                                      
--------------------------------------------------------------------------------------
 Bitmap Heap Scan on document  (cost=13.57..110.09 rows=202 width=41)
   Recheck Cond: (contenu ~~ '%123%'::text)
   ->  Bitmap Index Scan on document_contenu_idx  (cost=0.00..13.52 rows=202 width=0)
         Index Cond: (contenu ~~ '%123%'::text)
(4 rows)

Il est également possible de créer un tel index mais de type gist, à vous de voir ce qui vous conviendra le mieux (entre volumétrie, temps de recherche et surcoût sur la mise à jour).

---

Julien.
https://rjuju.github.io/

dangan

Membre

Re : Optimisation recherche plein texte like et vector

D'accord, je comprends pour le % à gauche de l'expression.

Par contre la commande "create extension pg_trgm" me retourne le message suivant :
"la fonction « set_limit » existe déjà avec des types d'arguments identiques"

De plus, mon index est déjà en GIN. Il avait mis 15 minutes à créer l'index.

Est ce que j'ai fait ou oublié quelque chose ?

rjuju

Administrateur

Re : Optimisation recherche plein texte like et vector

Par contre la commande "create extension pg_trgm" me retourne le message suivant :
"la fonction « set_limit » existe déjà avec des types d'arguments identiques"
De plus, mon index est déjà en GIN. Il avait mis 15 minutes à créer l'index.
Est ce que j'ai fait ou oublié quelque chose ?

Exact, je n'avais pas remarqué que votre index était déjà avec la classe d'opérateur gin_trgm_ops. Pour le problème de création d'extension, cela doit être lié à l'historique de votre base, ce n'est pas gênant (cela a du être installé avant que le système d'extension existe, en 9.1).

Avez-vous vérifié que l'index était utilisé pour une requête plus simple (par exemple uniquement un like avec un % à gauche, qui filtre énormément de données) ?

---

Julien.
https://rjuju.github.io/

dangan

Membre

Re : Optimisation recherche plein texte like et vector

Voici la requête et le schéma, il n'y a pas d'index du tout..

Dans la maintenance de la table, j'ai juste fait un Analyse, pas de Vacuum car ça va être trop long et j'ai peur de faire tomber ma base, qui est en prod.

gleu

Administrateur

Re : Optimisation recherche plein texte like et vector

Il utilise un index. L'icône est celle d'un parcours d'index. Bon, évidemment, avec la version texte, ce serait tellement plus clair que ce n'est pas rigolo

---

Guillaume.

rjuju

Administrateur

Re : Optimisation recherche plein texte like et vector

De plus, vous avez changé la clause LIKE.

---

Julien.
https://rjuju.github.io/

dangan

Membre

Re : Optimisation recherche plein texte like et vector

J'ai changé la clause LIKE comme m'a suggéré Rjuju mais ça ne change rien.

Donc il n'y a pas de solution pour mon problème ?

J'ai l'impression que c'est l'une des limites de la bdd, avec de gros volume de texte.

Je suis en train d'essayer de faire un tsquery puis un like dans le résultat que me retourne tsquery.. j'arrive à des résultats intéressant mais je peaufine encore.

gleu

Administrateur

Re : Optimisation recherche plein texte like et vector

J'avoue que j'ai du mal à voir votre problème. Vous indiquez qu'il n'utilise pas l'index, mais la copie d'écran que vous donnez montre un parcours d'index.

Ce qui serait bien, ce serait d'avoir la requête et le plan d'exécution (en texte, pas un graphique qui ne donne aucune information, voire les cache, les icônes n'étant pas très parlantes) avec un EXPLAIN ANALYZE (et BUFFERS si possible) pour le cas avec LIKE et sans LIKE.

---

Guillaume.