PostGreSQL vs Microsoft SQL Server

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 administratives que les DBA doivent ordinairement exécuter.

• CPU : 2 sockets (Intel Xeon E5-2680 v3).
• 128 Go de RAM.
• OS Windows 10 Enterprise.

• SQL Server 2019 édition Developper.
• PostGreSQL v 13.1 édition communautaire.

• SQL Server management Studio v 18.5 (pour SQL Server).
• PGadmin 4 v 4.29 (pour PostGreSQL).

• SQL Server n’a aucune configuration particulière. La collation d’installation du serveur est French_100_BIN2.
• PostGreSQL a une configuration particulière qui est la suivante :

ALTER SYSTEM SET effective_cache_size = 12000000; -- 12 000 000 x 8 192 o :  96 Go
ALTER SYSTEM SET shared_buffers = 2560000;        --  2 560 000 x 8 192 o :  20 Go
ALTER SYSTEM SET work_mem = 500000;               --    500 000 x 1 024 o : 500 Mo
ALTER SYSTEM SET temp_buffers = 512000;           --    512 000 x 8 192 o :   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;

SQL Server est configuré comme suit au niveau du stockage :

• fichier de données: 12 Gb ;
• fichier du journal de transaction : 8 Gb.

PostGreSQL a une collation spécifique du fait de l’OS utilisé :

• encoding = UTF8 ;
• collation = French_France.1252 ;
• character type = French_France.1252.

Le protocole utilisé est le suivant :

• exécution de chaque requête dix fois ;
• élimination du meilleur et du moins bon temps de réponse ;
• calcul de la moyenne sur les huit temps de réponse restants ;
• arrêt du service d’un SGBDR pendant le test de l’autre.

Les requêtes permettant de reproduire nos tests peuvent être téléchargées ici :

https://1drv.ms/u/s!AqvZfiQYoNpBihI4efgbhodhRZec?e=07igoa (lien)

• Le fichier « Benchmark test PostGreSQL.sql » contient le script SQL pour créer les tables et les peupler, ainsi que les requêtes que nous avons testées pour PostGreSQL.
• Le fichier « Benchmark test SQL Server.sql » contient le script SQL pour créer les tables et les peupler, ainsi que les requêtes que nous avons testées pour SQL Server.
• Le fichier « personnes.zip » contient les données pour charger la table de notre test (la table est en deux versions). C’est un fichier ZIP contenant un fichier texte de 239 820 Ko dans lequel vous trouverez 10 millions de lignes de personnes.

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 dû créer une collation pour PostGreSQL pour la deuxième table, car PostGreSQL possède très peu de collations internes contrairement à SQL Server qui en a plus de 5 500.

SELECT COUNT(*) FROM T_PERSONNE_PRS;

SELECT COUNT(DISTINCT PRS_PRENOM)
FROM T_PERSONNE_PRS;

SELECT COUNT(DISTINCT PRS_PRENOM),
COUNT(DISTINCT PRS_NOM)
FROM T_PERSONNE_PRS;

SELECT COUNT(DISTINCT PRS_PRENOM + PRS_NOM)
FROM T_PERSONNE_PRS;

SELECT PRS_PRENOM, COUNT(*)
FROM T_PERSONNE_PRS
GROUP BY PRS_PRENOM;

SELECT PRS_PRENOM, COUNT(*)
FROM T_PERSONNE_PRS
GROUP BY PRS_PRENOM
HAVING COUNT(*) > 20000;

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));

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)))

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));

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))

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érateurs et en particulier dans notre cas avec l’opérateur LIKE SQL. Aussi avons-nous dû récrire la requête en utilisant une autre table combinée avec l’opérateur LIKE – 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 !

PostGreSQL se révèle entre 10 et 454 fois plus lent que SQL Server pour l’opérateur COUNT sans index.

Les index suivants 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);

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…

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 est plus mauvais en termes de performances avec les index et la compression pour toutes les requêtes, que précédemment. Mais la perte de performances est faible (toujours inférieure à 2).

Le ratio de compression (il n’y a aucun NULL dans les données de la table) est d’un facteur d’environ 2.

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 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)…

• 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…

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ée de multiples versions des mêmes lignes à l’intérieur des pages de données , 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 !

Vos commentaires et remarques, comme vos protestations sont les bienvenus sur le forum de developpez.com et peuvent être postés sur cette enfilade : PostGreSQL vs Microsoft SQL Server - Partie 1 : performances des commandes pour le DBA.

Nous tenons à remercier Claude Leloup pour la relecture orthographique de cet article.