Forums PostgreSQL.fr

jhashe

Membre

Gros problème de non-utilisation d'index sur recherche plein texte

Bonjour,

Je dispose d'une table (contenant environ 90 000 lignes) créée comme suit:

CREATE TABLE presse_pdf_contenu
(
  id_presse_pdf_contenu serial NOT NULL,
  pres_pdf_page integer,
  pres_pdf_contenu text,
  pres_pdf_contenu_vecteur tsvector,
  id_presse_pdf integer
)

La colonne  pres_pdf_contenu_vecteur est indexée (gin)

CREATE INDEX idx_presse_vecteur
  ON presse_pdf_contenu
  USING gin
  (pres_pdf_contenu_vecteur);

Mais, si j'exécute une requête telle que:

SELECT *
FROM
    presse_pdf_contenu
WHERE
    pres_pdf_contenu_vecteur @@ to_tsquery('fr','seance')

l'index n'est pas utilisé, d'où des temps catastrophiques (environ 5 minutes pour la présente requête)

Sortie de l'explain:

"Seq Scan on public.presse_pdf_contenu  (cost=0.00..3665.51 rows=28122 width=49)"
"  Output: id_presse_pdf_contenu, pres_pdf_page, pres_pdf_contenu, pres_pdf_contenu_vecteur, id_presse_pdf"
"  Filter: (presse_pdf_contenu.pres_pdf_contenu_vecteur @@ '''seanc'''::tsquery)"

Avez-vous une idée pouvant m'aider à sortir de ce mauvais pas ?

Cordialement

Marc Cousin

Membre

Re : Gros problème de non-utilisation d'index sur recherche plein texte

Bonjour,

Je viens de suivre exactement la même procédure, à part que j'ai bien sûr du mettre des données différentes dans la table. Et ici ça marche…

On pourrait voir la même requête avec un explain analyze au lieu d'un explain ? Et quelle taille fait la table presse_pdf_contenu (nombre d'enregistrements) ?

---

Marc.

jhashe

Membre

Re : Gros problème de non-utilisation d'index sur recherche plein texte

Merci pour cette réponse extrêmement rapide.

La table compte environ 90 000 lignes. Elle "pèse" 7Mo, et l'index 1.8Go. A côté, j'ai aussi l'indication "Taille de la table TOAST: 21Go", mais je ne sais pas ce que c'est ;-)

Enfin, le explain analyse retourne:

"Seq Scan on presse_pdf_contenu  (cost=0.00..3665.51 rows=28122 width=49) (actual time=0.111..2344.661 rows=27890 loops=1)"
"  Filter: (pres_pdf_contenu_vecteur @@ '''seanc'''::tsquery)"
"Total runtime: 2352.961 ms"

Encore merci pour la réponse

Marc Cousin

Membre

Re : Gros problème de non-utilisation d'index sur recherche plein texte

Ok. Il estime donc que le mot seance est contenu dans 28000 des 90000 enregistrements. C'est bien le cas ? Si oui, il est effectivement plus intelligent pour lui de parcourir tout séquentiellement.

La table toast, c'est une table d'extension, qui contient les gros champs qu'il n'est pas forcément intéressant d'avoir dans la table principale. Ici, en l'occurrence, vos deux champs texte.

---

Marc.

jhashe

Membre

Re : Gros problème de non-utilisation d'index sur recherche plein texte

Effectivement, la requête retourne environ 28000 lignes.
Et si j'execute par exemple

SELECT count(*)
FROM
    presse_pdf_contenu
WHERE
    pres_pdf_contenu_vecteur @@ to_tsquery('fr','seance')

le résultat est BEAUCOUP plus rapide (32ms)

J'en déduis donc, que ce n'est pas la requête elle-même qui prend du temps, mais la lecture sur disque des résultats, exact ?

Marc Cousin

Membre

Re : Gros problème de non-utilisation d'index sur recherche plein texte

Oui.

Quand vous faites select count(*), il n'a pas à accéder à la table toast qui contient à priori le champs pres_pdf_contenu, qui est très gros.

Je suis quand même un peu surpris que ça soit aussi rapide. J'aurais pensé que le champ pres_pdf_contenu serait aussi en toast, et aussi très gros. Et lui, on a besoin d'y accéder pour vérifier le critère @@

C'est quoi le plan pour le select count(*) ? Et on est dans quelle version de postgres ?

---

Marc.

jhashe

Membre

Re : Gros problème de non-utilisation d'index sur recherche plein texte

Le résultat d'Explain analyse est le suivant:

"Aggregate  (cost=3735.82..3735.83 rows=1 width=0) (actual time=3460.170..3460.171 rows=1 loops=1)"
"  ->  Seq Scan on presse_pdf_contenu  (cost=0.00..3665.51 rows=28122 width=0) (actual time=0.377..3446.645 rows=27890 loops=1)"
"        Filter: (pres_pdf_contenu_vecteur @@ '''seanc'''::tsquery)"
"Total runtime: 3460.248 ms"

La base est sous PostgreSQL 9.0.1 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-48), 64-bit   

Et votre analyse était la bonne; en effet, si je change la 1ère requête pour une recherche plus restrictive, alors l'index est bien utilisé:

Exemple:

SELECT *
FROM
    presse_pdf_contenu
WHERE
    pres_pdf_contenu_vecteur @@ to_tsquery('fr','france & allemagne & algerie')

"Bitmap Heap Scan on presse_pdf_contenu  (cost=29.10..37.03 rows=2 width=49) (actual time=19.460..20.743 rows=778 loops=1)"
"  Recheck Cond: (pres_pdf_contenu_vecteur @@ '''franc'' & ''allemagn'' & ''alger'''::tsquery)"
"  ->  Bitmap Index Scan on idx_presse_vecteur  (cost=0.00..29.10 rows=2 width=0) (actual time=19.381..19.381 rows=778 loops=1)"
"        Index Cond: (pres_pdf_contenu_vecteur @@ '''franc'' & ''allemagn'' & ''alger'''::tsquery)"
"Total runtime: 20.966 ms"

SELECT *
FROM
    presse_pdf_contenu
WHERE
    pres_pdf_contenu_vecteur @@ to_tsquery('fr','france & allemagne')

"Seq Scan on presse_pdf_contenu  (cost=0.00..3665.51 rows=412 width=49) (actual time=0.051..2404.342 rows=6639 loops=1)"
"  Filter: (pres_pdf_contenu_vecteur @@ '''franc'' & ''allemagn'''::tsquery)"
"Total runtime: 2406.538 ms"

Merci pour cette information. J'ai encore beaucoup de choses à apprendre ... ;-)