Forums PostgreSQL.fr

mafia49

Membre

Optimisation d'une requete SQL

Bonjour à tous.

Je vous écrit car je suis confronté a une requête plutôt longue dans le temps d'exécution.

C'est une requete qui cherche dans une colonne de type tsvector, et qui doit renvoyer les 48 premières lignes contenant 'coca'

Il y a un index sur la colonne tsv.

SELECT *
FROM PRODUCT
WHERE  product.tsv @@ plainto_tsquery('coca')
LIMIT 48 OFFSET 0

Temps d'éxécution : 245Ms, donc plutot bon.

Mais la ou cela se complique, c'est lorsque j'essaie de trier par num_product desc:

C'est un champ de type bigint, ou il y a un index mis en place

SELECT *
FROM PRODUCT
WHERE  product.tsv @@ plainto_tsquery('coca')
ORDER BY num_product DESC
LIMIT 48 OFFSET 0

Temps d'éxecution : 2500Ms, et la cela devient trop long pour l'utilisateur..

avez vous des idées d'optimisation possible?

Cordialement

Marc Cousin

Membre

Re : Optimisation d'une requete SQL

Malheureusement, l'index pour les recherches plein-texte n'est pas un btree, mais un gin ou un gist. Ils ne sont pas «triés», et vous ne pouvez donc pas les utiliser pour optimiser ce genre d'accès. Le moteur est bien obligé d'aller chercher tous les mots contenant coca, récupérer le num_product qui va avec, et trier. Ou bien prendre les enregistrements de la table product par num_product descendant, et vérifier pour chaque enregistrement la condition sur le fulltext.

En tout cas, en ce qui me concerne, pas d'idée pour faire mieux là dessus.

---

Marc.

mafia49

Membre

Re : Optimisation d'une requete SQL

Bonjour,

Merci pour votre réponse.

Celle-ci me parait étrange car ce sur quoi je trie est bien un entier, et non du full text?

Marc Cousin

Membre

Re : Optimisation d'une requete SQL

Vous triez sur un entier. Mais vous filtrez sur un full text. Donc soit vous triez puis vous filtrez (et donc vous devez récupérer trop d'enregistrements parce que dans vos valeurs triées, il y aura des enregistrements ne correspondant pas à votre filtre), soit vous filtrez puis triez (mais dans ce cas, vous devez trier par un sort, et non par un parcours d'index, et vous devez avoir TOUS les enregistrements avant de les passer).

Pour trier et filtrer en même temps, on peut y arriver avec un index composé. Mais ça ne marche qu'en btree. Et le full text n'est pas indexable en btree.

---

Marc.

mafia49

Membre

Re : Optimisation d'une requete SQL

OK.

Merci

mafia49

Membre

Re : Optimisation d'une requete SQL

Vous me croirez ou non, la meme recherche full text sur SQL SERVER 2008 prends entre 6 et 50 ms grace à l'utilisation de la clause CONTAINS().

autrement dit, pour de la recherche full text, postgreSQL à encore du boulot à faire.

CDT.

Marc Cousin

Membre

Re : Optimisation d'une requete SQL

Je vous crois. Mais sans comparaison, sans plus d'information, etc… c'est difficile. Si il met 50ms pour faire ça, très bien, mais j'aimerais savoir comment, pas me contenter de si peu.

---

Marc.

mafia49

Membre

Re : Optimisation d'une requete SQL

En fait, sous Postgres, j'avais un champ de type tsvector, qui était la concaténation de 3 autres champs de la même table.
Sous SQL Server, ce champ était indéxé en Recherche de texte intégral (Menu Création..).

Une même requête avec jointure,  filtre sur un mot, puis tri pour obtenir les 48 premiers, prend 2500Ms sous PG, et entre 6 et 50ms sous SQL Server (meme nombre de ligne, meme schema de base).

Je ne saurais me l'expliquer à moi même ..

Marc Cousin

Membre

Re : Optimisation d'une requete SQL

Les 48 premiers sur quel critère ? C'est toute la question…

Si c'est sur la proximité entre le texte recherché et le texte du document, on peut faire pas trop mal avec PostgreSQL (surtout avec la 9.1 qui arrive) aussi.

Sinon, je ne vois vraiment pas comment sql server pourrait faire (et j'aimerais bien le savoir…).

Dans les derniers points que je n'ai pas creusé côté PostgreSQL: sans le LIMIT, il y a combien d'enregistrements retournés ? Et l'index sur le texte, c'est du GIST ou du GIN ?

---

Marc.

mafia49

Membre

Re : Optimisation d'une requete SQL

Que ce soit les 48, ou la totalités, le temps d'exec est quasiment le même.

Les 48, c'est un besoin de pagination seulement, ce pourrait etre les 1000, 500 etc.. Et c'est sur un critere de date (les 48 contenant le texte, ordonnés par date DESC).

C'est une recherche qui varie, sur 2 millions de lignes au total, il y a en moyenne 500 a 1000 retours positifs sur le filtre, et je n'affiche que les premiers.

L'index est un BTREE

Marc Cousin

Membre

Re : Optimisation d'une requete SQL

Les tsvectors ne sont pas indexables par btree. Je crois que c'est là qu'on a un problème…

C'est quoi la définition de l'index sur la colonne tsv exactement ?

---

Marc.

mafia49

Membre

Re : Optimisation d'une requete SQL

CREATE INDEX product_tsv_idx
  ON product
  USING btree
  (tsv);

Marc Cousin

Membre

Re : Optimisation d'une requete SQL

Je ne pensais même pas que c'était possible (j'aurais du essayer une fois…)

Essayez de supprimer cet index, puis recréez le avec:

CREATE INDEX product_tsv_idx
ON product
USING gin
(tsv);

Refaites un ANALYZE, puis retestez.

L'index btree n'est pas utilisable pour l'opérateur @@… donc à l'heure actuelle, vous lisez toute la table à chaque requête.

Dernière modification par Marc Cousin (23/05/2011 19:24:40)

---

Marc.

mafia49

Membre

Re : Optimisation d'une requete SQL

Les temps de réponse restent similaires a l'index BTREE..

Marc Cousin

Membre

Re : Optimisation d'une requete SQL

Très étrange. On peut avoir le plan ? (même requête, mais avec un EXPLAIN ANALYZE devant)

---

Marc.

mafia49

Membre

Re : Optimisation d'une requete SQL

"Limit  (cost=161978.97..161978.99 rows=10 width=59) (actual time=7892.944..7892.945 rows=10 loops=1)"
"  ->  Sort  (cost=161978.97..161979.68 rows=284 width=59) (actual time=7892.942..7892.943 rows=10 loops=1)"
"        Sort Key: (min(price.price))"
"        Sort Method:  top-N heapsort  Memory: 18kB"
"        ->  HashAggregate  (cost=161969.28..161972.83 rows=284 width=59) (actual time=7892.705..7892.808 rows=414 loops=1)"
"              ->  Nested Loop  (cost=0.00..161647.46 rows=42910 width=59) (actual time=42.308..7881.205 rows=24334 loops=1)"
"                    ->  Seq Scan on product  (cost=0.00..21700.56 rows=284 width=55) (actual time=42.281..7222.542 rows=489 loops=1)"
"                          Filter: (search @@ plainto_tsquery('coca'::text))"
"                    ->  Index Scan using price_num_product_idx on price  (cost=0.00..490.88 rows=151 width=12) (actual time=0.199..1.336 rows=50 loops=489)"
"                          Index Cond: (price.num_product = product.num_product)"
"Total runtime: 7893.049 ms"

Marc Cousin

Membre

Re : Optimisation d'une requete SQL

Bon, on a donc un scan séquentiel sur la table product, qui représente toujours le plus gros du travail. Et c'est évidemment là que les bactéries se déposent. Il devrait évidemment utiliser l'index. Il doit donc y avoir un truc pas net quelque part. Vous devriez avoir quelque chose comme ça pour la recherche du mot:

EXPLAIN ANALYZE SELECT * from test where to_tsquery('mot_a_rechercher')@@tsv;
                                                   QUERY PLAN                                                   
----------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on test  (cost=4.26..8.28 rows=1 width=32) (actual time=0.042..0.042 rows=0 loops=1)
   Recheck Cond: (to_tsquery('mot_a_rechercher'::text) @@ tsv)
   ->  Bitmap Index Scan on tst_tsv  (cost=0.00..4.26 rows=1 width=0) (actual time=0.037..0.037 rows=0 loops=1)
         Index Cond: (to_tsquery('mot_a_rechercher'::text) @@ tsv)
Total runtime: 0.072 ms

Plusieurs points à voir :

- Qu'est ce que l'explain analyze donne si vous exécutez:
«SET enable_seqscan TO off;» dans la session avant de l'exécuter ? => C'est pour savoir si il ne veut pas utiliser l'index, ou si c'est qu'il ne peut pas
- Est-ce que vous pouvez poster la définition des tables (\d nom_table dans psql)
- Est-ce que vous me confirmez que ce n'est pas la même requête que celle que vous avez posté ? Le champ a l'air de s'appeller search et plus tsv, et vous avez 2 tables jointes et plus une ? Dans ce cas, donnez moi le code de la nouvelle requête
- Quelle version de PG ? J'aurais du vous la poser il y a un certain temps

---

Marc.

Marc Cousin

Membre

Re : Optimisation d'une requete SQL

Plus de nouvelles ?

---

Marc.