PostGreSQL vs Microsoft SQL Server (comparaison) – Partie 2 : performances des requêtes avec COUNT

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 :

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 ollation 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 ServerPostGreSQL
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 1SELECT COUNT(*) FROM T_PERSONNE_PRS;
QRY 2SELECT COUNT(DISTINCT PRS_PRENOM)
FROM T_PERSONNE_PRS;
QRY 3SELECT COUNT(DISTINCT PRS_PRENOM),
COUNT(DISTINCT PRS_NOM)
FROM T_PERSONNE_PRS;
QRY 4SELECT COUNT(DISTINCT PRS_PRENOM + PRS_NOM)
FROM T_PERSONNE_PRS;
QRY 5SELECT PRS_PRENOM, COUNT(*)
FROM T_PERSONNE_PRS
GROUP BY PRS_PRENOM;
QRY 6SELECT PRS_PRENOM, COUNT(*)
FROM T_PERSONNE_PRS
GROUP BY PRS_PRENOM
HAVING COUNT(*) > 20000;
QRY 7SELECT 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 8SELECT 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 9SELECT 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 12629011.15
QRY 27934770440.23
QRY 315269000453.95
QRY 414668800060.03
QRY 57687311.49
QRY 6787569.69
QRY 7222595926.84
QRY 8257892034.71
QRY 92121277660.26
QRY 10 (1)140269519.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 12830510.89
QRY 26132995540.41
QRY 312369000560.98
QRY 413678700063.64
QRY 51238266.72
QRY 6937167.70
QRY 7346917815.57
QRY 82381287338.56
QRY 912373.98
QRY 10 (1)140275522.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 1343058.97
QRY 210032995329.65
QRY 316169000428.57
QRY 414018700062.10
QRY 52288263.62
QRY 61297165.55
QRY 7345917817.62
QRY 82791287332.90
QRY 923953.86
QRY 10 (1)161275517.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 11230525.42
QRY 231329951063.39
QRY 345690001533.33
QRY 411298700077.06
QRY 52182639.33
QRY 61771642.12
QRY 7203917829.95
QRY 82661287334.50
QRY 919964.69
QRY 10 (1)192755145.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 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…

COUNT performances: PostGreSQL vs SQL Server

 

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)

Ce contenu a été publié dans Performances, PostGreSQL, SQL Server, avec comme mot(s)-clé(s) , , , , . Vous pouvez le mettre en favoris avec ce permalien.