Dans une entreprise lointaine, vous êtes sollicité car une recherche paraissant simple donne une impression désagréable. Cela reste utilisable mais l’utilisateur décrit un manque de réactivité, la résultat n’est pas immédiat. L’application est compatible avec Oracle Database et PostgreSQL et le phénomène est commun aux 2 SGBD.Vous décidez de faire tourner le traqueur alors que l’utilisateur effectue sa recherche :
postgres@xx-x-xxx-xxxxx:~$ ./traqueur96.sh -w "select pid where application_name = 'RECHERCHE'"
traqueur 0.05.08.96beta - outil de diagnostic performance pour PostgreSQL 9.6
INFORMATION, pas d'options de connexions indiquees, utilisation de la base traqueur detectee ...
INFORMATION, preparation de la collecte ...
INFORMATION, execution de la collecte et presentation des resultats ...
busy_pc | distinct_exe | db | pid | pg_user | client_ip | application | query | blocker
---------+--------------+---------+------+----------+-----------+-------------+------------------------------------------------------+---------
32 | 1 / 16 | xxxxxxx | 5546 | xxxxxxxx | | RECHERCHE | select count(*) from t1 where c1::bigint = 593896; |
32 | 1 / 16 | xxxxxxx | 5546 | xxxxxxxx | | RECHERCHE | select * from t1 where c1::bigint = 593896; |
(2 lignes)
Votre exécution du traqueur vous permet de détecter 2 requêtes. Elles se ressemblent beaucoup, la première consistant à compter le nombre de lignes que ramènera la seconde. C’est une première information à remonter aux développeurs. Ce genre de code est souvent utilisé pour prévenir les utilisateurs que leur recherche va ramener beaucoup de lignes. Cependant il y a des moyens plus efficaces de procéder avec UNE requête, quel que soit l’outil de développement.
C’est une remarque générale mais cela n’explique pas le sentiment de lenteur confirmé par le traqueur. Vos 16 détections de chaque requête donne une idée approximative de leur durée. Chaque requête doit durer au moins 1s6, vous avez en effet laissé les paramètres par défaut pour la fréquence d’interrogation de pg_stat_activity qui a lieu chaque dixième de seconde.
Il est temps de s’intéresser à la table t1, ses colonnes et son indexation :
select version();
version
---------------------------------------------------------------------------------------------------
PostgreSQL 9.6.1 on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.2.0-10) 6.2.0 20161027, 64-bit
(1 ligne)
xxxxx=# \d t1
Table " xxxxxxxxx.t1 "
Colonne | Type | Modificateurs
----------------------+-----------------------------+---------------
c1 | character varying(12) | non NULL
...
Index :
"t1_pkey" PRIMARY KEY, btree (c1)
xxxxxx=# explain analyze select * from t1 where c1::bigint = 593896;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------
Seq Scan on t1 (cost=0.00..175224.31 rows=13859 width=5048) (actual time=0.195..1621.101 rows=1 loops=1)
Filter: ((c1)::bigint = 593896)
Rows Removed by Filter: 2771788
Planning time: 0.519 ms
Execution time: 1621.241 ms
(5 lignes)
Temps : 1622,875 ms
xxxxx=# select * from t1 where c1 = '593896';
Temps : 1,469 ms
xxxxx=# explain analyze select * from t1 where c1 = '593896';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Index Scan using t1_pkey on t1 (cost=0.43..8.45 rows=1 width=5048) (actual time=0.050..0.052 rows=1 loops=1)
Index Cond: ((c1)::text = '593896'::text)
Planning time: 0.487 ms
Execution time: 0.192 ms
(4 lignes)
Temps : 1,885 ms
La colonne c1 est de type character varying et constitue la clé primaire de t1. Une requête via la clé primaire devrait être très rapide mais la conversion vers un bigint effectuée sur la colonne c1 empêche l’utilisation de l’index. Un balayage complet (seq scan) de t1 est réalisé, la requête dure ainsi 1622ms au lieu de moins de 2ms.
Sous Oracle, la conversion était implicite et le problème un peu plus difficile à cerner. Il aurait dû être résolu naturellement lors de l’adaptation du code à PostgreSQL en utilisant une variable de type chaîne de caractère au lieu d’une valeur numérique. Vous interrogez le développeur qui vous confirme qu’il a obtenu une erreur lors de la première exécution de la recherche sous PostgreSQL. Il a choisi la mauvaise solution pour résoudre le problème. Plutôt que de corriger le bug tout en améliorant les performances, il a appliqué la conversion sur la colonne au lieu de choisir le bon type pour le critère de recherche. Une petite modification du programme confirme le diagnostic, les performances sont améliorées à la fois sous Oracle et PostgreSQL. La recherche donne alors un résultat quasi immédiat, mission réussie.