PostGreSQL vs Microsoft SQL Server

Partie 2 : performances des requêtes avec COUNT

Frédéric BROUARD

Pour réagir au contenu de cet article, un espace de dialogue vous est proposé sur le forum. Commentez

- Titre : PostGreSQL vs Microsoft SQL Server

- Auteur : Frédéric BROUARD

- Parution : 22 mars 2021

- Public visé :

- Licence : Copyright ® 2020 Frédéric BROUARD. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents, images, etc. sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts.

Sommaire

- I. L’auteur

- II. Introduction

- III. Matériel de test utilisé

- IV. Versions des SGBDR

- V. Applications clientes

- VI. Configuration des SGBDR

- VII. Configuration de la base de données

- VIII. Protocole de test

- IX. Téléchargement des requêtes de test

- X. Script de définition des tables

- XI. Présentation des requêtes de comptage

- XII. Performances du COUNT sans aucun index

- XIII. Performances du COUNT avec index

- XIV. Performances du COUNT avec index et compression :

- XV. Performances du COUNT avec index columnstore

- XVI. PostGreSQL est très lent pour des comptages ordinaires (COUNT)

- XVII. Pourquoi PostGreSQL est-il si lent ?

- XVIII. Remerciements Developpez.com

I - L’auteur

Blog :
https://blog.developpez.com/sqlpro
Le site :
https://sqlpro.developpez.com

II - Introduction

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.

III - Matériel de test utilisé

- CPU : 2 sockets (Intel Xeon E5-2680 v3).

- 128 Go de RAM.

- OS Windows 10 Enterprise.

IV - Versions des SGBDR

- SQL Server 2019 édition Developper.

- PostGreSQL v 13.1 édition communautaire.

V - Applications clientes

- SQL Server management Studio v 18.5 (pour SQL Server).

- PGadmin 4 v 4.29 (pour PostGreSQL).

VI - Configuration des SGBDR

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

VII - Configuration de la base de données

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.

VIII - Protocole de test

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.

IX - Téléchargement des requêtes de test

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.

X - Script de définition des tables

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

XI - Présentation des requêtes de comptage

Query

QRY 1

SELECT COUNT(*) FROM T_PERSONNE_PRS;

QRY 2

SELECT COUNT(DISTINCT PRS_PRENOM)
FROM T_PERSONNE_PRS;

QRY 3

1. SELECT COUNT(DISTINCT PRS_PRENOM),

2. COUNT(DISTINCT PRS_NOM)

3. FROM T_PERSONNE_PRS;

QRY 4

1. SELECT COUNT(DISTINCT PRS_PRENOM + PRS_NOM)

2. FROM T_PERSONNE_PRS;

QRY 5

1. SELECT PRS_PRENOM, COUNT(*)

2. FROM T_PERSONNE_PRS

3. GROUP BY PRS_PRENOM;

QRY 6

1. SELECT PRS_PRENOM, COUNT(*)

2. FROM T_PERSONNE_PRS

3. GROUP BY PRS_PRENOM

4. HAVING COUNT(*) > 20000;

QRY 7

1. SELECT CONCAT(LEFT(PRS_PRENOM, 1),

2. LEFT(PRS_NOM, 1)),

3. COUNT(*)

4. FROM T_PERSONNE_PRS

5. GROUP BY CONCAT(LEFT(PRS_PRENOM, 1),

6. LEFT(PRS_NOM, 1));

QRY 8

1. SELECT UPPER(CONCAT(LEFT(PRS_PRENOM, 1),

2. LEFT(PRS_NOM, 1))),

3. COUNT(*)

4. FROM T_PERSONNE_PRS

5. GROUP BY UPPER(CONCAT(LEFT(PRS_PRENOM, 1),

6. LEFT(PRS_NOM, 1)))

QRY 9

1. SELECT CONCAT(LEFT(PRS_PRENOM, 1),

2. LEFT(PRS_NOM, 1)),

3. COUNT(*)

4. FROM T_PERSONNE_CI_AI_PRS

5. GROUP BY CONCAT(LEFT(PRS_PRENOM, 1),

6. LEFT(PRS_NOM, 1));

QRY 10 (1)

1. SELECT CONCAT(LEFT(PRS_PRENOM, 1),

2. LEFT(PRS_NOM, 1)),

3. COUNT(*)

4. FROM T_PERSONNE_CI_AI_PRS

5. WHERE PRS_PRENOM LIKE 'Jean-%'

6. GROUP BY CONCAT(LEFT(PRS_PRENOM, 1),

7. LEFT(PRS_NOM, 1))

QRY 10 (PostGreSQL)

1. SELECT UPPER(CONCAT(LEFT(PRS_PRENOM, 1),

2. LEFT(PRS_NOM, 1))),

3. COUNT(*)

4. FROM T_PERSONNE_PRS

5. WHERE PRS_PRENOM ILIKE 'Jean-%'

6. GROUP BY UPPER(CONCAT(LEFT(PRS_PRENOM, 1),

7. 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 !

XII - Performances du COUNT sans aucun index

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

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

XIII - Performances du COUNT avec 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);

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…

XIV - 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 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.

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

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

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

XVII - 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é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.

XVIII - Remerciements Developpez.com

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