Forums PostgreSQL.fr

jhashe

Membre

Gros problème de performance avec ts_headline

Bonjour,

Je développe actuellement un nouveau moteur de recherche pour notre site, basé sur les fonctions de recherche plein texte de PostgreSQL (v9.0.1 sur bi quad-Xeon 32Go RAM -RedHat 5 x86_64 bits), mais je rencontre un problème de performance, liée à l'utilisation de ts_headline.

Exemple typique d'une requête que j'effectue:

SELECT
    ir_instrument_recherche.id_ir_instrument_recherche,
    ts_headline('fr', ir_inst_rech_col1,r, 'MaxFragments=4') AS extrait,
    ts_rank_cd(ir_inst_rech_vecteur,r) AS score
FROM
    ir_instrument_recherche, to_tsquery('fr','jerome') AS r
WHERE
    id_ir_instrument IN (3934,3434,3438,4266,4264,4265,4414,4413,4412,3006,3005,607,3005,607,4488,242,243,244,245,246,247,248,249,250,253,251,252,610,581,607,586,589,590,591,579,580,608,585,588,606,582,587,605,1276,1275,1500,1499,910,3427,3424,3470,3483,3484,3481) AND ir_inst_rech_vecteur @@ r ORDER BY id_ir_instrument_recherche LIMIT 5000

=> S'exécute en plus de 10 secondes pour 38 résultats !!!

Explain:
"Limit  (cost=5381.05..5382.07 rows=410 width=655)"
"  ->  Sort  (cost=5381.05..5382.07 rows=410 width=655)"
"        Sort Key: ir_instrument_recherche.id_ir_instrument_recherche"
"        ->  Nested Loop  (cost=3774.81..5363.25 rows=410 width=655)"
"              ->  Function Scan on r  (cost=0.00..0.01 rows=1 width=32)"
"              ->  Bitmap Heap Scan on ir_instrument_recherche  (cost=3774.81..5356.07 rows=410 width=623)"
"                    Recheck Cond: ((ir_instrument_recherche.ir_inst_rech_vecteur @@ r.r) AND (ir_instrument_recherche.id_ir_instrument = ANY ('{3934,3434,3438,4266,4264,4265,4414,4413,4412,3006,3005,607,3005,607,4488,242,243,244,245,246,247,248,249,250,253,251,252,610,581,607,586,589,590,591,579,580,608,585,588,606,582,587,605,1276,1275,1500,1499,910,3427,3424,3470,3483,3484,3481}'::integer[])))"
"                    ->  BitmapAnd  (cost=3774.81..3774.81 rows=410 width=0)"
"                          ->  Bitmap Index Scan on idx_vecteur_rech  (cost=0.00..1785.14 rows=1782 width=0)"
"                                Index Cond: (ir_instrument_recherche.ir_inst_rech_vecteur @@ r.r)"
"                          ->  Bitmap Index Scan on idx_id_ir_instrument_rech  (cost=0.00..1968.82 rows=81999 width=0)"
"                                Index Cond: (ir_instrument_recherche.id_ir_instrument = ANY ('{3934,3434,3438,4266,4264,4265,4414,4413,4412,3006,3005,607,3005,607,4488,242,243,244,245,246,247,248,249,250,253,251,252,610,581,607,586,589,590,591,579,580,608,585,588,606,582,587,605,1276,1275,1500,1499,910,3427,3424,3470,3483,3484,3481}'::integer[]))"

----------------------------------------------------------------------------------------

Variante *SANS* ts_headline:

SELECT
    ir_instrument_recherche.id_ir_instrument_recherche,
    ts_rank_cd(ir_inst_rech_vecteur,r) AS score
FROM
    ir_instrument_recherche, to_tsquery('fr','jerome') AS r
WHERE
    id_ir_instrument IN (3934,3434,3438,4266,4264,4265,4414,4413,4412,3006,3005,607,3005,607,4488,242,243,244,245,246,247,248,249,250,253,251,252,610,581,607,586,589,590,591,579,580,608,585,588,606,582,587,605,1276,1275,1500,1499,910,3427,3424,3470,3483,3484,3481) AND ir_inst_rech_vecteur @@ r ORDER BY id_ir_instrument_recherche LIMIT 5000

=> S'exécute en 33 ms (ouf !!!)

Explain:
"Limit  (cost=5380.02..5381.05 rows=410 width=514)"
"  ->  Sort  (cost=5380.02..5381.05 rows=410 width=514)"
"        Sort Key: ir_instrument_recherche.id_ir_instrument_recherche"
"        ->  Nested Loop  (cost=3774.81..5362.23 rows=410 width=514)"
"              ->  Function Scan on r  (cost=0.00..0.01 rows=1 width=32)"
"              ->  Bitmap Heap Scan on ir_instrument_recherche  (cost=3774.81..5356.07 rows=410 width=482)"
"                    Recheck Cond: ((ir_instrument_recherche.ir_inst_rech_vecteur @@ r.r) AND (ir_instrument_recherche.id_ir_instrument = ANY ('{3934,3434,3438,4266,4264,4265,4414,4413,4412,3006,3005,607,3005,607,4488,242,243,244,245,246,247,248,249,250,253,251,252,610,581,607,586,589,590,591,579,580,608,585,588,606,582,587,605,1276,1275,1500,1499,910,3427,3424,3470,3483,3484,3481}'::integer[])))"
"                    ->  BitmapAnd  (cost=3774.81..3774.81 rows=410 width=0)"
"                          ->  Bitmap Index Scan on idx_vecteur_rech  (cost=0.00..1785.14 rows=1782 width=0)"
"                                Index Cond: (ir_instrument_recherche.ir_inst_rech_vecteur @@ r.r)"
"                          ->  Bitmap Index Scan on idx_id_ir_instrument_rech  (cost=0.00..1968.82 rows=81999 width=0)"
"                                Index Cond: (ir_instrument_recherche.id_ir_instrument = ANY ('{3934,3434,3438,4266,4264,4265,4414,4413,4412,3006,3005,607,3005,607,4488,242,243,244,245,246,247,248,249,250,253,251,252,610,581,607,586,589,590,591,579,580,608,585,588,606,582,587,605,1276,1275,1500,1499,910,3427,3424,3470,3483,3484,3481}'::integer[]))"

Malheureusement, j'ai pourtant besoin de cet extrait...

Avez-vous une piste à me suggérer car là, je sèche...

Par avance, merci

Marc Cousin

Membre

Re : Gros problème de performance avec ts_headline

À priori, la raison c'est que ts_headline doit aller chercher le contenu du champ ir_inst_rech_col1. Ou alors que ça augmente dramatiquement le temps de tri. Mais pour l'instant c'est de la spéculation.

Pouvez vous me donner :
- La version exacte de postgres ?
- Un EXPLAIN ANALYZE au lieu d'un EXPLAIN SIMPLE (pour le cas en 10s bien sûr)

---

Marc.

jhashe

Membre

Re : Gros problème de performance avec ts_headline

Merci pour votre réponse.

La version de Postgres utilisée est:
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   

La sortie de Explain Analyse est:

"Limit  (cost=5381.05..5382.07 rows=410 width=655) (actual time=10208.591..10208.624 rows=38 loops=1)"
"  ->  Sort  (cost=5381.05..5382.07 rows=410 width=655) (actual time=10208.587..10208.600 rows=38 loops=1)"
"        Sort Key: ir_instrument_recherche.id_ir_instrument_recherche"
"        Sort Method:  quicksort  Memory: 45kB"
"        ->  Nested Loop  (cost=3774.81..5363.25 rows=410 width=655) (actual time=681.273..10208.392 rows=38 loops=1)"
"              ->  Function Scan on r  (cost=0.00..0.01 rows=1 width=32) (actual time=0.011..0.013 rows=1 loops=1)"
"              ->  Bitmap Heap Scan on ir_instrument_recherche  (cost=3774.81..5356.07 rows=410 width=623) (actual time=16.675..17.273 rows=38 loops=1)"
"                    Recheck Cond: ((ir_instrument_recherche.ir_inst_rech_vecteur @@ r.r) AND (ir_instrument_recherche.id_ir_instrument = ANY ('{3934,3434,3438,4266,4264,4265,4414,4413,4412,3006,3005,607,3005,607,4488,242,243,244,245,246,247,248,249,250,253,251,252,610,581,607,586,589,590,591,579,580,608,585,588,606,582,587,605,1276,1275,1500,1499,910,3427,3424,3470,3483,3484,3481}'::integer[])))"
"                    ->  BitmapAnd  (cost=3774.81..3774.81 rows=410 width=0) (actual time=16.643..16.643 rows=0 loops=1)"
"                          ->  Bitmap Index Scan on idx_vecteur_rech  (cost=0.00..1785.14 rows=1782 width=0) (actual time=0.391..0.391 rows=485 loops=1)"
"                                Index Cond: (ir_instrument_recherche.ir_inst_rech_vecteur @@ r.r)"
"                          ->  Bitmap Index Scan on idx_id_ir_instrument_rech  (cost=0.00..1968.82 rows=81999 width=0) (actual time=16.093..16.093 rows=92620 loops=1)"
"                                Index Cond: (ir_instrument_recherche.id_ir_instrument = ANY ('{3934,3434,3438,4266,4264,4265,4414,4413,4412,3006,3005,607,3005,607,4488,242,243,244,245,246,247,248,249,250,253,251,252,610,581,607,586,589,590,591,579,580,608,585,588,606,582,587,605,1276,1275,1500,1499,910,3427,3424,3470,3483,3484,3481}'::integer[]))"
"Total runtime: 10208.763 ms"

Je ne comprends pas a priori en quoi l'ajout du ts_headline impacterait le tri, puisqu'il est le même dans les 2 requêtes données en exemple (ORDER BY id_ir_instrument_recherche)

Je me demande plutôt si la fonction ts_headline n'est pas appliquée sur toutes les lignes, et non sur "seulement" les 38 lignes de résultats ?

gleu

Administrateur

Re : Gros problème de performance avec ts_headline

Il n'y a pas de différence entre les deux plans d'exécution. La partie qui vous coûte cher correspond à l'exécution de la fonction ts_headline, qui est connue pour être lente. Comme il y a peu de lignes à traiter, j'aurais tendance à penser que le problème vient de la taille des valeurs de la colonne ir_inst_rech_col1.

---

Guillaume.

Marc Cousin

Membre

Re : Gros problème de performance avec ts_headline

C'est vraiment étrange comme problème. Je ne pense pas que la fonction soit appliquée sur toutes les lignes. Nous pouvons le vérifier, puisque vous êtes en 9.0 et que nous avons donc plein de nouvelles options pour explain. Faisons nous plaisir… Remplacez explain analyze par EXPLAIN (analyze true, costs true, buffers true, verbose true)

Tant qu'on y est, faites le pour les deux requêtes (avec et sans le ts_headline)

---

Marc.

gleu

Administrateur

Re : Gros problème de performance avec ts_headline

J'oubliais la dernière question. La procédure stockée n'est exécutée que sur le sous-ensemble. Pour vous en persuader, il vous suffit de créer une petite procédure stockée qui remplacera ts_headline pour ajouter un message dans les journaux de transactions. Quelque chose du type :

CREATE OR REPLACE FUNCTION dummy()
 RETURNS boolean
 LANGUAGE plpgsql
AS $function$
BEGIN
RAISE NOTICE 'appel de la fonction'; 
RETURN true;
END
$function$

SELECT
    ir_instrument_recherche.id_ir_instrument_recherche,
    dummy,
    ts_headline('fr', ir_inst_rech_col1,r, 'MaxFragments=4') AS extrait,
    ts_rank_cd(ir_inst_rech_vecteur,r) AS score
FROM
    ir_instrument_recherche, to_tsquery('fr','jerome') AS r
WHERE
    id_ir_instrument IN (3934,3434,3438,4266,4264,4265,4414,4413,4412,3006,3005,607,3005,607,4488,242,243,244,245,246,247,248,249,250,253,251,252,610,581,607,586,589,590,591,579,580,608,585,588,606,582,587,605,1276,1275,1500,1499,910,3427,3424,3470,3483,3484,3481) AND ir_inst_rech_vecteur @@ r ORDER BY id_ir_instrument_recherche LIMIT 5000

Les traces indiqueront le nombre de fois où la procédure a été lancée.

---

Guillaume.

Marc Cousin

Membre

Re : Gros problème de performance avec ts_headline

Je pense comme gleu. Mais on peut tout de même le vérifier… Normalement, avec les options supplémentaires de explain, on verra à quel niveau la colonne est ajoutée. Mais pour obtenir 10 secondes d'exécution supplémentaires pour 38 enregistrements, il faudrait vraiment que la colonne ir_inst_rech_col1 soit énorme.

---

Marc.

jhashe

Membre

Re : Gros problème de performance avec ts_headline

Effectivement, la colonne peut être grosse; elle contient le contenu de fichiers bureautiques qui peuvent aller jusqu'à plusieurs centaines de pages !

Et je confirme ce que vous dites: la fonction n'est bien appliquée QUE sur les enregistrements retournés.

Du coup, je m'en suis tiré avec une "pirouette" consistant:

1- A appeler la rechercher sans le ts_headline, afin de pouvoir afficher les résultats immédiatement
2- Appel de la fonction ts_headline en asynchrone (via Ajax) pour remplir dans un 2ème temps les extraits des documents.

Le code est plus lourd, génère plus de requetes HTTP et est moins satisfaisant pour l'utilisateur, mais c'est un compromis permettant un affichage des résultats rapide.

Merci pour votre aide.

Ne reste plus qu'à espérer qu'une prochaine version améliorera les performances de cette fonction (qui, dans l'état actuel, sont, il faut bien le reconnaitre- assez médiocres)