Dans le cadre d'un long article à paraître dont le sujet est l'étude de la portabilité de bases Microsoft SQL Server vers PostGreSQL, j'ai été amené à effectuer des tests comparatifs.

Je publie ici un exemple concernant d'importantes différences de performances concernant les manipulation de chaines de caractères et notamment dans le cadre de recherches avec insensibilité à la casse ou aux accents.

ATTENTION : dans certains cas il existe des solutions de contournement, mais elles imposent une restructuration des tables (notamment l'ajout de colonnes) et la récriture des requêtes.
Dans tous les cas, ces solutions ont un coût extrémement important, qui, en pratique pourront être un frein, voir un arrêt de mort concernant un tel projet de portage...

Dans un projet portage on essaye de minimiser l'impact de modification, sinon, le portage risque de coûter plus cher que l'économie que l'on tente de réaliser...

Voici les tests que j'ai mené sur un portable HP doté comme suit :
  • CPU Core i7 6500U cadencés à 2,5 Ghz (4 coeurs)
  • RAM 32 Go
  • Disques SSD


Avant de publier cette série d'article, je vais faire des tests complémentaires sur une machine plus costaude (48 coeurs, 128 Go de RAM, disques SAS et SSD)

* * * * * * *

[T-0050] Test mettant en évidence les contre-performances de PostGreSQL pour des recherches insensibles à la casse

Test pour SQL Server :
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
CREATE TABLE T_CASSE (ID      INT IDENTITY PRIMARY KEY,
                      DATUM   VARCHAR(256) COLLATE French_CI_AS);
GO
 
INSERT INTO T_CASSE VALUES ('Paris ! Paris outragé ! Paris brisé ! Paris martyrisé ! Mais Paris libéré !');
GO 7 --> permet de lancer la requête 7 fois
 
INSERT INTO T_CASSE
SELECT T1.DATUM
FROM   T_CASSE AS T1
       CROSS JOIN T_CASSE AS T2;
GO 3 --> permet de lancer la requête 3 fois
 
--> à ce stade nous avons 10 192 056 lignes dans la table
 
INSERT INTO T_CASSE VALUES ('Méli-Mélo');
GO
 
SET STATISTICS TIME ON;
GO
SELECT *
FROM   T_CASSE
WHERE  DATUM = 'méli-mélo';
--> Temps UC = 3109 ms, temps écoulé = 1857 ms.
 
CREATE INDEX X_CASSE ON T_CASSE (DATUM); 
GO
 
SELECT *
FROM   T_CASSE
WHERE  DATUM = 'méli-mélo';
--> Temps UC = 0 ms, temps écoulé = 1 ms.
Sans index la recherche a mis 1 857 ms, avec index 1 ms. SQL Server effectuant une recherche dans l’index.

Test pour PostGreSQL :
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
CREATE TABLE T_CASSE (ID      INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
                      DATUM   VARCHAR(256));
 
INSERT INTO T_CASSE (DATUM) VALUES ('Paris ! Paris outragé ! Paris brisé ! Paris martyrisé ! Mais Paris libéré !');
INSERT INTO T_CASSE (DATUM) VALUES ('Paris ! Paris outragé ! Paris brisé ! Paris martyrisé ! Mais Paris libéré !');
INSERT INTO T_CASSE (DATUM) VALUES ('Paris ! Paris outragé ! Paris brisé ! Paris martyrisé ! Mais Paris libéré !');
INSERT INTO T_CASSE (DATUM) VALUES ('Paris ! Paris outragé ! Paris brisé ! Paris martyrisé ! Mais Paris libéré !');
INSERT INTO T_CASSE (DATUM) VALUES ('Paris ! Paris outragé ! Paris brisé ! Paris martyrisé ! Mais Paris libéré !');
INSERT INTO T_CASSE (DATUM) VALUES ('Paris ! Paris outragé ! Paris brisé ! Paris martyrisé ! Mais Paris libéré !');
INSERT INTO T_CASSE (DATUM) VALUES ('Paris ! Paris outragé ! Paris brisé ! Paris martyrisé ! Mais Paris libéré !');
 
--> lancez 3 fois cette requête :
INSERT INTO T_CASSE (DATUM)
SELECT T1.DATUM
FROM   T_CASSE AS T1
       CROSS JOIN T_CASSE AS T2;
 
--> à ce stade nous avons 10 192 056 lignes dans la table
 
INSERT INTO T_CASSE (DATUM) VALUES ('Méli-Mélo');
 
EXPLAIN ANALYZE
SELECT *
FROM   T_CASSE
WHERE  LOWER(DATUM) = 'méli-mélo';
--> Execution time : 4795.370 ms
 
EXPLAIN ANALYZE
SELECT *
FROM   T_CASSE
WHERE  DATUM ILIKE 'méli-mélo';
--> Execution time : 1541.112 ms
 
EXPLAIN ANALYZE
SELECT *
FROM   T_CASSE
WHERE  DATUM ~ 'méli-mélo';
--> Execution time : 11910.661 ms
 
CREATE INDEX X_CASSE ON T_CASSE (DATUM); 
 
EXPLAIN ANALYZE
SELECT *
FROM   T_CASSE
WHERE  LOWER(DATUM) = 'méli-mélo';
--> Execution time : 5197.753 ms
 
EXPLAIN ANALYZE
SELECT *
FROM   T_CASSE
WHERE  DATUM ILIKE 'méli-mélo';
--> Execution time : 1718.089
 
EXPLAIN ANALYZE
SELECT *
FROM   T_CASSE
WHERE  DATUM ~ 'méli-mélo';
--> Execution time : 10905.050 ms
Sans index, et dans le meilleur des cas PostGreSQL s’en tire un peu mieux que SQL Server grâce à l’opérateur ILIKE, mais sans cet opérateur il est 2,5 fois plus lent. À noter que les plans d’exécution montrent l’emploi du parallélisme avec de 2 threads et systématiquement un parcours séquentiel. Avec un index, le ILIKE est même légèrement moins bon que sans (nous avons réitérer la requête pour être sûr de nous...). Dans tous les cas, avec un index, PostGreSQL est battu à plate couture !

Bilan : SQL Server est 1 718 fois plus rapide que PostGreSQL sur cette recherche insensible à la casse...

* * * * * * *

[T-0060] Test mettant en évidence les contre-performances de PostGreSQL pour des recherches insensibles aux accents et autres caractères diacritiques

Script pour SQL Server :
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
CREATE TABLE T_ACCENTS (ID      INT IDENTITY PRIMARY KEY,
                        DATUM   VARCHAR(256) COLLATE French_CS_AI);
GO
 
INSERT INTO T_ACCENTS VALUES ('Écœurée par l’Aÿ Lætitia et son garçon arrivèrent à Paris');
GO 7 --> ceci exécute la requête 7 fois
 
INSERT INTO T_ACCENTS
SELECT T1.DATUM
FROM   T_ACCENTS AS T1
       CROSS JOIN T_ACCENTS AS T2;
GO 3 --> ceci exécute la requête trois fois
 
--> à ce stade nous avons 10 192 056 lignes dans la table
 
INSERT INTO T_ACCENTS VALUES ('méli-mélo');
 
SET STATISTICS TIME ON;
SELECT *
FROM   T_ACCENTS
WHERE  DATUM = 'meli-melo'
--> Temps UC = 3126 ms, temps écoulé = 1805 ms
 
CREATE INDEX X_ACCENTS ON T_ACCENTS (DATUM); 
GO
 
SELECT *
FROM   T_ACCENTS
WHERE  DATUM = 'meli-melo'
--> Temps UC = 0 ms, temps écoulé = 1 ms.
SQL Server a mis 1 805 ms sans index et 1 ms avec index, grâce à la collation.

Script PostGreSQL :
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
CREATE TABLE T_ACCENTS (ID      SERIAL PRIMARY KEY,
                        DATUM   VARCHAR(256));
 
INSERT INTO T_ACCENTS (DATUM) VALUES ('Écœurée par l’Aÿ Lætitia et son garçon arrivèrent à Paris');
INSERT INTO T_ACCENTS (DATUM) VALUES ('Écœurée par l’Aÿ Lætitia et son garçon arrivèrent à Paris');
INSERT INTO T_ACCENTS (DATUM) VALUES ('Écœurée par l’Aÿ Lætitia et son garçon arrivèrent à Paris');
INSERT INTO T_ACCENTS (DATUM) VALUES ('Écœurée par l’Aÿ Lætitia et son garçon arrivèrent à Paris');
INSERT INTO T_ACCENTS (DATUM) VALUES ('Écœurée par l’Aÿ Lætitia et son garçon arrivèrent à Paris');
INSERT INTO T_ACCENTS (DATUM) VALUES ('Écœurée par l’Aÿ Lætitia et son garçon arrivèrent à Paris');
INSERT INTO T_ACCENTS (DATUM) VALUES ('Écœurée par l’Aÿ Lætitia et son garçon arrivèrent à Paris');
 
--> à jouer 3 fois :
INSERT INTO T_ACCENTS (DATUM)
SELECT T1.DATUM
FROM   T_ACCENTS AS T1
       CROSS JOIN T_ACCENTS AS T2;
 
--> à ce stade nous avons 10 192 056 lignes dans la table
 
INSERT INTO T_ACCENTS (DATUM) VALUES ('méli-mélo');
 
--> création de la fonction de désaccentuation :
CREATE OR REPLACE FUNCTION remove_fr_accents(string text) 
RETURNS text 
AS $$
DECLARE out_str text;
BEGIN
   SELECT translate(string, 'âäàÁÂÄèéêëÈÉÊËîïÎÏôöÕÖùûüÙÛÜÿŸÇç', 
                            'aaaAAAeeeeEEEEiiIIooOOuuuUUUyYCc')
          INTO out_str;
   SELECT replace(out_str, 'Œ', 'OE') INTO out_str;
   SELECT replace(out_str, 'Æ', 'AE') INTO out_str;
   SELECT replace(out_str, 'æ', 'ae') INTO out_str;
   SELECT replace(out_str, 'œ', 'oe') INTO out_str;
   RETURN out_str;
END;
$$ LANGUAGE plpgsql;
 
EXPLAIN ANALYZE
SELECT *
FROM   T_ACCENTS
WHERE  remove_fr_accents(DATUM) = 'meli-melo';
--> 331 666 ms
 
CREATE INDEX X_ACCENTS ON T_ACCENTS (DATUM);
 
EXPLAIN ANALYZE
SELECT *
FROM   T_ACCENTS
WHERE  remove_fr_accents(DATUM) = 'meli-melo';
--> 284 990 ms
Bilan : SQL Server est en moyenne 300 000 fois plus rapide que PostGreSQL sur cette recherche insensible aux accents...

Comme la plupart du temps, les bases Microsoft SQL Server sont créées avec une collation sensible aux caractères diacritiques, nous allons maintenant procéder à un nouveau test. La table sera créée avec une collation sensible à la casse et nous allons utiliser l’opérateur COLLATE, pour retrouver notre information...

Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
CREATE TABLE T_ACCENTS2 (ID      INT IDENTITY PRIMARY KEY,
                         DATUM   VARCHAR(256) COLLATE French_CI_AS);
GO
 
INSERT INTO T_ACCENTS2 VALUES ('Écœurée par l’Aÿ Lætitia et son garçon arrivèrent à Paris');
GO 7 --> ceci exécute la requête 7 fois
 
INSERT INTO T_ACCENTS2
SELECT T1.DATUM
FROM   T_ACCENTS2 AS T1
       CROSS JOIN T_ACCENTS2 AS T2;
GO 3 --> ceci exécute la requête trois fois
 
--> à ce stade nous avons 10 192 056 lignes dans la table
 
SELECT COUNT(*) FROM T_ACCENTS2
 
INSERT INTO T_ACCENTS2 VALUES ('méli-mélo');
 
SET STATISTICS TIME ON;
SELECT *
FROM   T_ACCENTS2
WHERE  DATUM COLLATE French_CI_AI = 'meli-melo'
--> Temps UC = 0 ms, temps écoulé = 1 ms
Aussi surprenant que cela puisse paraître SQL Server a mis 1 ms pour retrouver l’information sans que la table soit indexée !

Bilan : une nouvelle fois SQL Server est 300 000 fois plus rapide que PostGreSQL sur cette recherche insensible aux accents et sans faire usage d’index dans SQL Server !...

* * * * * *

Si quelqu'un à des idées pour améliorer les performances de PostgreSQL je suis preneur. Bien entendu pour le second test on peut utiliser le module unaccent, mais cela modifie la structure de la table et oblige de réécrire les requêtes…

A +