Ce second article compare PostGreSQL à SQL Server et met en avant les différences de performances des requêtes d’agrégation qui utilisent la fonction COUNT.
Notre premier article comparait les temps de réponse entre PostGreSQL et SQL Server des requêtes administrative que les DBA doivent ordinairement exécuter.
Notre matériel de test, PC HP Z840 workstation avec :
- 2 sockets (CPU Intel Xeon E5-2680 v3);
- 128 Go de RAM;
- OS Windows 10 Enterprise.
Les SGBDR sont :
- SQL Server 2019 Developper’s edition;
- PostGreSQL v 13.1 community edition.
Les applications clientes utilisées pour nos tests sont :
- SQL Server management Studio v 18.5 (pour SQL Server);
- PGadmin 4 v 4.29 (pour PostGreSQL).
Configuration des SGBDR :
- SQL Server n’a aucune configuration particulière. La collation du serveur est French_100_BIN2;
- PostGreSQL a une configuration particulière :
1 2 3 4 5 6 7 |
ALTER SYSTEM SET effective_cache_size = 12000000; -- 12 000 000 x 8 192 b : 96 Go ALTER SYSTEM SET shared_buffers = 2560000; -- 2 560 000 x 8 192 b : 20 Go ALTER SYSTEM SET work_mem = 500000; -- 500 000 x 1 024 b : 500 Mo ALTER SYSTEM SET temp_buffers = 512000; -- 512 000 x 8 192 b : 4 Go ALTER SYSTEM SET max_worker_processes = 48; ALTER SYSTEM SET max_parallel_workers = 48; ALTER SYSTEM SET max_parallel_workers_per_gather = 8; |
Configuration des bases de données :
Le stockage de la base SQL Server a été dimensionné comme suit :
- fichier de données : 12 Gb;
- fichier de transactions : 8 Gb.
PostGreSQL utilise une collation spécifique qui dépend de l’OS:
- Encoding = UTF8;
- Collation = French_France.1252;
- Character type = French_France.1252.
Le protocole de test utilisé est le suivant ;
- exécution des requêtes 10 fois
- élimination du meilleur et plus mauvais résultat en terme de temps de réponse;
- calcul du temps de réponse moyen sur les 8 résultats retenus;
- arrêt de l’un des SGBDR pendant que l’autre effectue ses tests.
Pour reproduire Les requêtes de notre test chez vous, les fichiers peuvent être téléchargés à :
ttps://1drv.ms/u/s!AqvZfiQYoNpBihI4efgbhodhRZec?e=07igoa
- Le fichier « Benchmark test PostGreSQL.sql » contient le script SQL script pour créer les tables et les peupler ainsi que les requêtes de tests, l’ensemble pour PostGreSQL;
- Le fichier « Benchmark test SQL Server.sql » contient le script SQL script pour créer les tables et les peupler ainsi que les requêtes de tests, l’ensemble pour SQL Server;
- Le fichier « personnes.zip » contient les données pour charger les tables de notre test (les tables ayant deux versions). C’est un fichier ZIP contenant un fichier textede 239 820 Ko dns lequel vous trouverez 10 millions de lignes de « personnes ».
Les tables sont :
SQL Server | PostGreSQL |
---|---|
CREATE TABLE T_PERSONNE_PRS (PRS_ID INT IDENTITY PRIMARY KEY, PRS_NOM VARCHAR(32), PRS_PRENOM VARCHAR(25)) | CREATE TABLE T_PERSONNE_PRS (PRS_ID SERIAL PRIMARY KEY, PRS_NOM VARCHAR(32), PRS_PRENOM VARCHAR(25)); |
CREATE TABLE T_PERSONNE_CI_AI_PRS (PRS_ID INT PRIMARY KEY, PRS_NOM VARCHAR(32) COLLATE french_ci_ai, PRS_PRENOM VARCHAR(25) COLLATE french_ci_ai); | CREATE COLLATION fr_ci_ai (provider = icu, locale = 'fr-u-ks-level1-kc-false', deterministic=false); CREATE TABLE t_personne_ci_ai_prs (prs_id INT PRIMARY KEY, prs_nom VARCHAR(32) COLLATE fr_ci_ai, prs_prenom VARCHAR(25) COLLATE fr_ci_ai); |
Nous avons du créer une collation pour la table 2 de PostGreSQL’s parce que PostGreSQL possède un nombre très limitées de collations par défaut, contrairement à SQL Server qui en compte en standard plus de 5 500.
Nos requêtes de comptage sont les suivantes :
Query | |
---|---|
QRY 1 | SELECT COUNT(*) FROM T_PERSONNE_PRS; |
QRY 2 | SELECT COUNT(DISTINCT PRS_PRENOM) FROM T_PERSONNE_PRS; |
QRY 3 | SELECT COUNT(DISTINCT PRS_PRENOM), COUNT(DISTINCT PRS_NOM) FROM T_PERSONNE_PRS; |
QRY 4 | SELECT COUNT(DISTINCT PRS_PRENOM + PRS_NOM) FROM T_PERSONNE_PRS; |
QRY 5 | SELECT PRS_PRENOM, COUNT(*) FROM T_PERSONNE_PRS GROUP BY PRS_PRENOM; |
QRY 6 | SELECT PRS_PRENOM, COUNT(*) FROM T_PERSONNE_PRS GROUP BY PRS_PRENOM HAVING COUNT(*) > 20000; |
QRY 7 | SELECT CONCAT(LEFT(PRS_PRENOM, 1), LEFT(PRS_NOM, 1)), COUNT(*) FROM T_PERSONNE_PRS GROUP BY CONCAT(LEFT(PRS_PRENOM, 1), LEFT(PRS_NOM, 1)); |
QRY 8 | SELECT UPPER(CONCAT(LEFT(PRS_PRENOM, 1), LEFT(PRS_NOM, 1))), COUNT(*) FROM T_PERSONNE_PRS GROUP BY UPPER(CONCAT(LEFT(PRS_PRENOM, 1), LEFT(PRS_NOM, 1))) |
QRY 9 | SELECT CONCAT(LEFT(PRS_PRENOM, 1), LEFT(PRS_NOM, 1)), COUNT(*) FROM T_PERSONNE_CI_AI_PRS GROUP BY CONCAT(LEFT(PRS_PRENOM, 1), LEFT(PRS_NOM, 1)); |
QRY 10 (1) | SELECT CONCAT(LEFT(PRS_PRENOM, 1), LEFT(PRS_NOM, 1)), COUNT(*) FROM T_PERSONNE_CI_AI_PRS WHERE PRS_PRENOM LIKE 'Jean-%' GROUP BY CONCAT(LEFT(PRS_PRENOM, 1), LEFT(PRS_NOM, 1)) |
QRY 10 (PostGreSQL) | SELECT UPPER(CONCAT(LEFT(PRS_PRENOM, 1), LEFT(PRS_NOM, 1))), COUNT(*) FROM T_PERSONNE_PRS WHERE PRS_PRENOM ILIKE 'Jean-%' GROUP BY UPPER(CONCAT(LEFT(PRS_PRENOM, 1), LEFT(PRS_NOM, 1))); |
(1) QRY 10 lève une exception dans PostGreSQL, parce que PostGreSQL est incapable d’utiliser l’opérateur COLLATE combiné à d’autres opérateur et en particulier dans notre cas avec l’opérateur LIKE SQL. Aussi avons nous du récrire la requête en utilisant une autre table combinées avec l’opérateur ILIKE – QRY 10 (PostGreSQL).
Dans des circonstances de production normales, par exemple avec un logiciel d’éditeur, ceci aurait été impossible à faire. Cette limitation de PostGreSQL est une véritable anomalie et si nous avions dû effectuer des comptages sans tenir compte des accents et autres caractères diacritiques (ligatures par exemple), cela aurait été strictement impossible avec PostGreSQL !
Performances du COUNT sans aucun index:
SQL Server execution time (ms) | PostGreSQL execution time (ms) | ratio | |
---|---|---|---|
QRY 1 | 26 | 290 | 11.15 |
QRY 2 | 79 | 34770 | 440.23 |
QRY 3 | 152 | 69000 | 453.95 |
QRY 4 | 1466 | 88000 | 60.03 |
QRY 5 | 76 | 873 | 11.49 |
QRY 6 | 78 | 756 | 9.69 |
QRY 7 | 222 | 5959 | 26.84 |
QRY 8 | 257 | 8920 | 34.71 |
QRY 9 | 212 | 12776 | 60.26 |
QRY 10 (1) | 140 | 2695 | 19.25 |
PostGreSQL se révèle entre 10 et 454 fois plus lent que SQL Server pour l’opérateur COUNT sans index.
Performances du COUNT avec indexes:
Les index suivant sont créés :
- CREATE INDEX X_1 ON T_PERSONNE_PRS (PRS_NOM);
- CREATE INDEX X_2 ON T_PERSONNE_PRS (PRS_PRENOM);
- CREATE INDEX X_3 ON T_PERSONNE_PRS (PRS_NOM, PRS_PRENOM);
SQL Server execution time (ms) | PostGreSQL execution time (ms) | ratio | |
---|---|---|---|
QRY 1 | 28 | 305 | 10.89 |
QRY 2 | 61 | 32995 | 540.41 |
QRY 3 | 123 | 69000 | 560.98 |
QRY 4 | 1367 | 87000 | 63.64 |
QRY 5 | 123 | 826 | 6.72 |
QRY 6 | 93 | 716 | 7.70 |
QRY 7 | 346 | 9178 | 15.57 |
QRY 8 | 238 | 12873 | 38.56 |
QRY 9 | 123 | 73.98 | |
QRY 10 (1) | 140 | 2755 | 22.40 |
PostGreSQL se révèle entre 7 et 561 fois plus lent que SQL Server pour l’opérateur COUNT avec des index appropriés.
SQL Server s’en sort mieux que précédemment pour les requêtes 2, 8, 9, 10 mais le gain est très faible. SQL Server est plus mauvais, avec les index, pour les requêtes 5, 6, 7, mais la perte de performances est aussi très faible.
PostGreSQL n’a quasiment aucun gain ou perte avec ou sans index…
Performances du COUNT avec index et compression :
Nous avons laissé les index et compressé les index et les tables de SQL Server (la compression d’index ou de table n’existe pas dans PostGreSQL):
- ALTER TABLE T_PERSONNE_PRS REBUILD WITH(DATA_COMPRESSION = PAGE);
- ALTER INDEX X_1 ON T_PERSONNE_PRS REBUILD WITH(DATA_COMPRESSION = PAGE);
- ALTER INDEX X_2 ON T_PERSONNE_PRS REBUILD WITH(DATA_COMPRESSION = PAGE);
- ALTER INDEX X_3 ON T_PERSONNE_PRS REBUILD WITH(DATA_COMPRESSION = PAGE);
SQL Server execution time (ms) | PostGreSQL execution time (ms) | ratio | |
---|---|---|---|
QRY 1 | 34 | 305 | 8.97 |
QRY 2 | 100 | 32995 | 329.65 |
QRY 3 | 161 | 69000 | 428.57 |
QRY 4 | 1401 | 87000 | 62.10 |
QRY 5 | 228 | 826 | 3.62 |
QRY 6 | 129 | 716 | 5.55 |
QRY 7 | 345 | 9178 | 17.62 |
QRY 8 | 279 | 12873 | 32.90 |
QRY 9 | 239 | 53.86 | |
QRY 10 (1) | 161 | 2755 | 17.11 |
SQL Server est plus mauvais en terme de performances avec les index et la compression pour toutes les requêtes, que précédemment. Mais la perte de performance est faible (toujours inférieure à 2).
Le ration de compression (il n’y a aucun NULL dans les données de la table) est d’un facteur d’environ 2.
Performances du COUNT avec index columnstore :
Nous avons supprimé tous les index de la table et rajouté un seul index columnstore comme suit :
- CREATE COLUMNSTORE INDEX XC ON T_PERSONNE_CI_AI_PRS (PRS_PRENOM, PRS_NOM);
Les index verticaux (de type columnstore) n’existent pas dans PostGreSQL.
SQL Server execution time (ms) | PostGreSQL execution time (ms) | ratio | |
---|---|---|---|
QRY 1 | 12 | 305 | 25.42 |
QRY 2 | 31 | 32995 | 1063.39 |
QRY 3 | 45 | 69000 | 1533.33 |
QRY 4 | 1129 | 87000 | 77.06 |
QRY 5 | 21 | 826 | 39.33 |
QRY 6 | 17 | 716 | 42.12 |
QRY 7 | 203 | 9178 | 29.95 |
QRY 8 | 266 | 12873 | 34.50 |
QRY 9 | 199 | 64.69 | |
QRY 10 (1) | 19 | 2755 | 145.00 |
SQL Server est meilleur avec l’index columnstore qu’avec l’index BTree dans un facteur compris entre 2 et 7, excepté pour les requêtes 4, 8 et 9 (transformations de données).
Ce que nous pouvons dire en matière de comparaison des performances entre SQL Server et PosstGreSQL au sujet de l’opérateur COUNT, est le suivant:
PostGreSQL est incroyablement lent pour le COUNT DISTINCT : SQL Server est entre 61 et 561 fois plus rapide que PostGreSQL dans toutes les situations, et avec l’indexation verticale (columnstore index) SQL Server est 1 533 fois plus rapide (Query 3)…
PostGreSQL est très lent pour des comptages ordinaires (COUNT) :
- SQL Server est entre 10 et 60 fois plus rapide sans index
- SQL Server est entre 7 et 74 fois plus rapide avec des index appropriés
- SQL Server est entre 4 et 54 fois plus rapide avec des index appropriés et la compression (ce que PostGreSQL ne permet pas)
- SQL Server est entre 25 et 145 fois plus rapide avec un index columnstore.
NOTE : PostGreSQL peut s’enrichir d’index verticaux (à stockage colonne plutôt que ligne) mais il faut migrer de la version communautaire vers une version comme celle de Fujitsu ou Citus.
Globalement PostGreSQL est 114 fois plus lent que SQL Server pour les requêtes de type COUNT…
Pourquoi PostGreSQL est-il si lent ?
Le processus de comptage de PostGreSQL nécessite de balayer toutes les pages de la table et de lire chaque emplacement de ligne un par un, du fait que le MVCC créé de multiples version des même lignes à l’intérieur des pages de données pour toutes les transactions d’écriture, tandis que d’autres SGBDR, utilisent un stockage spécifique (le journal de transaction ou la base système tempdb…). Il apparait aussi que PostGreSQL n’utilise qu’un seul thread pour calculer le COUNT, en dépit des paramètres activés pour une exécution multithread, et qu’aussi il doit comparer les données pour les dédoublonner lorsqu’est requis un COUNT DISTINCT..
Pour faire son comptage, SQL Server lit uniquement l’en-tête de page des pages de données et le fait en parallèle (jusqu’à 48 threads dans notre cas). Tous les en-têtes de page contiennent deux octets, au même emplacement, dans lesquels il trouve le nombre de lignes vivantes. Mais je ne connais pas l’astuce utilisée par SQL Server pour être si rapide pour le COUNT DISTINCT …. Cela semble magique! Bien sûr, le département de recherche et développement de Microsoft est l’un des meilleurs … Même la base de données Oracle est plus lente que SQL Server!
Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
* Le site sur les SGBD relationnels et le SQL : https://sqlpro.developpez.com *
* le blog SQL, SQL Server, SGBDR... sur : https://blog.developpez.com/sqlpro/ *
* Entreprise SQL SPOT : modélisation, conseil, audit, optimisation, formation *
* Site d'entreprise : https://www.sqlspot.com -- email : SQLpro[@]SQLspot.com *
* * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *
PRÉCÉDENT :
PostGreSQL vs Microsoft SQL Server (comparaison) – Partie 1 : performances des commandes pour le DBA
SUIVANT: PostGreSQL vs Microsoft SQL Server – Comparaison partie 3 : FONCTIONNALITÉS (sera publié aux alentours de 2021-04-01)