Development #75594: Performances: requête lente sur wcs_all_forms - w.c.s. - Redmine Entr’ouvert

Development #75594

Performances: requête lente sur wcs_all_forms

Added by Pierre Ducroquet 9 months ago. Updated 8 months ago.

Status:

Fermé

Priority:

Normal

Assignee:

Frédéric Péters

Target version:

Start date:

20 March 2023

Due date:

% Done:

Estimated time:

Patch proposed:

Planning:

Description

La requête suivante (simplifiée pour ce ticket) est particulièrement lente sur l'instance de toulouse:

SELECT * FROM wcs_all_forms
WHERE status != 'draft' AND ((fts @@ plainto_tsquery('0612345678')) or (fts @@ plainto_tsquery('+33612345678'))) AND anonymised IS NULL
ORDER BY receipt_time DESC LIMIT 20;

Le problème est simple : l'optimiseur ne peut pas savoir à quel point le critère sur fts va filtrer ou non le contenu de la table. Il estime donc qu'en la parcourant en suivant l'index sur receipt_time il rencontrera assez vite les 20 enregistrements requis pour répondre à la requête.
=> Perdu.

La requête prend donc 8 secondes au lieu de moins de 3 millisecondes, et fait environ 5000 fois plus d'IOs.
J'ai plusieurs solutions dans ma besace, je fais pas mal de tests pour identifier ce qui sera le mieux.

Files

fts_match_optimization.diff (3.29 KB) fts_match_optimization.diff

Benjamin Dauvergne, 20 March 2023 12:11 PM

Related issues

Associated revisions

Revision e682f42f (diff)
Added by Frédéric Péters 9 months ago

sql: revert looking up both raw and normalized values in FTS search (#75594)

Revision 00222aae (diff)
Added by Frédéric Péters 9 months ago

sql: do not consider numbers not starting with 0 as phone numbers (#75594)

History

Updated by Pierre Ducroquet 9 months ago

Prod.

Le problème est déclenché par le OR sur le fts.
Il estime que ça va doubler le nombre de lignes renvoyées par la requête, ce qui justifie alors l'utilisation de l'index sur receipt_time.

Réécrire la requête pour transformer le OR en tsquery ne résoud pas le problème, il utilise le tsquery pour évaluer le nombre de lignes et donc on tombe sur le même problème.
Par contre un UNION permet de contourner le problème, ou éventuellement ajouter une opération vierge (coalesce, +0, ...) sur l'order by pour forcer l'utilisation du FTS...

Updated by Pierre Ducroquet 9 months ago

Related to Bug #75302: Une recherche sur un numéro dans le listing d'une démarche ne retourne plus rien added

Updated by Frédéric Péters 9 months ago

Il y a aussi possibilité de se dire qu'on peut juste chercher une valeur; annuler #75302 et le reprendre différemment.

Typiquement il est écrit pour reproduire entrer "974230200934596" et ça pourrait se gérer en incluant uniquement les numéros commençant par un 0 dans la normalisation téléphone.

Updated by Benjamin Dauvergne 9 months ago

Il faut reverter jusqu'à #72773 dans ce cas, pour ne pas échanger un bug contre un souci de performance.

Updated by Benjamin Dauvergne 9 months ago

J'ai testé set enable_indexscan=false, pour ce cas particulier ça marche, mais ça me parait difficilement généralisable.

Updated by Pierre Ducroquet 9 months ago

Benjamin Dauvergne a écrit :

J'ai testé set enable_indexscan=false, pour ce cas particulier ça marche, mais ça me parait difficilement généralisable.

Pour ce cas mieux vaut écrire la requête avec un UNION ou avec un ORDER BY receipt_time + interval '0 second'...

Updated by Benjamin Dauvergne 9 months ago

File fts_match_optimization.diff fts_match_optimization.diff added

Un truc comme ça ? La pente me parait glissante.

#10

Updated by Frédéric Péters 9 months ago

Assignee changed from Pierre Ducroquet to Frédéric Péters

Stop svp.

Je vais "annuler #75302 et le reprendre différemment".

#11

Updated by Benjamin Dauvergne 9 months ago

Frédéric Péters a écrit :

Je vais "annuler #75302 et le reprendre différemment".

C'est bon on a trouvé une solution en bossant uniquement coté pg avec les statistiques.

#12

Updated by Pierre Ducroquet 9 months ago

Ouais enfin, une solution... c'est pas non plus magique, avec l'évolution des données dans le temps je ne sais pas quand on atteindra un nouveau seuil, et là j'ai mis les stats au maximum viable, ça nous a déjà fait x10 sur le temps de planification, mettre plus ça devient trop onéreux.
Au moins maintenant on n'a plus les requêtes lentes sur la prod, mais j'approuve l'idée de Fred de reprendre différemment le correctif.

#13

Updated by Benjamin Dauvergne 9 months ago

Ça fera ça à chaque fois qu'on a un OR dans la condition ou est-ce que c'est spécifique à la recherche sur une colonne ts_vector ?

#14

Updated by Pierre Ducroquet 9 months ago

Benjamin Dauvergne a écrit :

Ça fera ça à chaque fois qu'on a un OR dans la condition ou est-ce que c'est spécifique à la recherche sur une colonne ts_vector ?

Un OR ça change radicalement un plan d'exécution, et il est courant de voir un OR plusieurs fois plus lent que l'UNION équivalent (et c'est dommage que l'optimiseur ne sache pas aller dans cette direction). Après la colonne ts_vector est un facteur supplémentaire ici puisqu'elle casse encore plus les prédictions, et nous a fait tomber dans le piège de l'order by... Les joies de l'optimisation sur des stats...

#15