Vous n'êtes pas identifié(e).
Pages : 1
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
Hors ligne
À 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.
Hors ligne
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 ?
Hors ligne
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.
Hors ligne
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.
Hors ligne
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 5000Les traces indiqueront le nombre de fois où la procédure a été lancée.
Guillaume.
Hors ligne
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.
Hors ligne
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)
Hors ligne
Pages : 1