Comparaison des performances de SIG

PostGreSQL/PostGIS vs SQL Server Spatial

Frédéric Brouard

- Titre : Comparaison des performances de SIG

- Auteur : Frédéric Brouard

- Parution : 19 avril 2017

- Licence : Les sources présentées sur cette page sont libres de droits et vous pouvez les utiliser à votre convenance. Par contre, la page de présentation constitue une œuvre intellectuelle protégée par les droits d'auteur. Copyright ® 2017 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. Élaboration du test

- I-A. Machine utilisée

- I-B. Version des SGBDR

- I-C. Sources des données

- I-D. Chargement des données

- I-E. Méthodologie

- I-F. Quelques différences dans les requêtes

- I-G. Choix des requêtes

- II. Requêtes du test

- II-A. Recherche des données spatiales non valides avec explications détaillées (Q1)

- II-B. Recherche de la plus grande commune en surface (Q2)

- II-C. Agrégation spatiale des communes en départements (Q3)

- II-D. Recherche des communes les plus proches de certains points (Q4)

- II-E. Recherches de départements distants de moins de 5 km et ne se touchant pas (Q5)

- II-F. Recherche des communes intérieures au département faisant au moins une surface de 34 567 890 m² (Q6)

- II-G. Quelle est la commune au barycentre de chaque département ? (Q7)

- II-H. Quelle est la commune dont la surface est la plus proche de 6 666 666 m² (Q8)

- II-I. Calcul de la longueur de routes pour toutes les catégories sauf inconnues (Q9)

- II-J. Calcul de la longueur de routes par département et numéro de route (Q10)

- III. Conclusion

- IV. Fichiers relatifs

- V. Tests annexes

- V-A. Sauvegarde de la base

- V-B. Restauration de la base

- ANNEXE 1 - Ajout des clefs primaires et des index spatiaux pour SQL Server après import

I - Élaboration du test

Le présent test consiste à comparer la performance d'une dizaine de requêtes identiques sur le plan logique. Il est consacré au traitement spatial des données entre PostgreSQL et Microsoft SQL Server dont le SIG de chacun propose les mêmes fonctionnalités du standard OGC

Les requêtes ont porté sur les données spatiales disponibles librement après de l' État français en matière de géographie politique et routière (GEOFLA et ROUTE 500).

I-A - Machine utilisée

Station de travail HP Z840 avec :

- 8 disques SAS de 1.2 To 10 K RPM organisé en un agrégat RAID 10 (unités C et D) ;

- 2 CPU Intel Xeon E5-2680 v3 - 2.5 Ghz - 12 cœurs, hyperthreadés ;

- 128 Go de RAM.

OS Windows 10 version Enterprise.

Figure 1 - Ressources du PC

I-B - Version des SGBDR

Les versions que nous avons utilisées sont les suivantes :

- PostgreSQL 9.6.1, compiled by Visual C++ build 1800, 64-bit (pour Windows) ;

- Microsoft SQL Server 2016 (SP1-GDR) (KB3207512) - 13.0.4199.0 (X64) Nov 18 2016 15:56:54 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows 10 Enterprise 6.3 <X64> (Build 14393: ).

Les deux SGBDR peuvent être téléchargés gratuitement :

PostGreSQL : https://www.postgresql.org/download/.

Pour Windows, utilisez l'installeur de EnterpriseDB et « Stack Builder » pour ajouter la cartouche spatiale PostGIS.

La version SQL Server 2016 SP1 Developer est gratuite, mais ne peut pas servir en production :

https://www.microsoft.com/en-us/sql-server/sql-server-editions-developers.

L'outil client (SSMS : SQL Server management Studio) doit être téléchargé séparément :

https://msdn.microsoft.com/library/mt238290.aspx.

Les installations des deux SGBDR sont standard. Aucun réglage particulier n'a été mis en œuvre au niveau des serveurs.

I-C - Sources des données

GEOFLA : https://www.data.gouv.fr/fr/datasets/geofla-r/.

ROUTE 500 IGN : http://professionnels.ign.fr/route500.

Une table de correspondance codes postaux/code INSEE communes, disponible sur le site du gouvernement à l'URL :

https://www.data.gouv.fr/fr/datasets/correspondance-entre-les-codes-postaux-et-codes-insee-des-communes-francaises/.

I-D - Chargement des données

Pour PostGreSQL, nous avons créé une base de données de nom DB_GEO d'après le template PostGIS. Ceci rajoute quelques objets de métadonnées spécifiques au spatial.

Pour alimenter la base, nous avons utilisé l'outil « PostGIS Shapefile Import/Export Manager », avec le paramétrage standard (voir figure 2).

Figure 2 - Paramétrage de l'import avec shp2pgsql-gui.exe

Nous avons chargé les données en utilisant le format shapefile et l'utilitaire s'est avéré assez rapide pour l'import (une minute trente pour l'ensemble des fichiers).

Finalement, les tables sont constituées des colonnes présentes dans le fichier, auquel est rajouté :

- une colonne de nom gid type INTEGER constituant la clef primaire de la table (avec un index BTree) ;

- un index spatial de type GIST sur la colonne contenant la géométrie.

La base fait 807 Mo.

Pour SQL Server, nous avons créé une base de nom DB_GEO en mode de récupération simple afin d'éviter une journalisation continue et sans purge des transactions.

Nous avons utilisé l'outil FME pour l'import.

Figure 3 - Import avec FME

Nous y avons rajouté les mêmes clefs primaires et index spatiaux, comme suit :

ALTER TABLE nom_table ADD GID INT IDENTITY PRIMARY KEY;
CREATE SPATIAL INDEX nom_index
ON nom_table (GEOM)
WITH ( BOUNDING_BOX = (x_min, y_min, x_max, y_max) );

SQL Server nécessite que les colonnes de type geometry aient un SRID de 0. Pour ceci nous avons modifié par UPDATE, tous les SRID de toutes les colonnes, comme suit :

UPDATE nom_table SET colonne_geometry.STSrid = 0;

Voir annexe 1 : ajout des clefs primaires et index spatiaux et modification des SRID.

La base fait 1 030 Mo.

I-E - Méthodologie

Nous avons fait en sorte que les services de chaque moteur soient à l'arrêt pendant que l'autre était testé.

Chaque requête a été lancée une fois pour obtenir la mise en cache des données puis dix fois de suite. Les valeurs extrêmes minimales et maximales ont été supprimées du jeu, puis une moyenne a été établie sur les huit résultats restants.

Pour recueillir les métriques, nous avons utilisé les commandes suivantes afin d'obtenir les temps d'exécution :

- PostGreSQL : EXPLAIN ANALYZE ;

- SQL Server : SET NOCOUNT ON; SET STATISTICS TIME ON;.

Les outils utilisés sont ceux livrés en standard, à savoir :

- PostGreSQL : pgAdmin 4 (version 1.1) ;

- SQL Server : SSMS (SQL Server Management Studio - version 13.0.16100.1).

REMARQUE : pgAdmin 4 a montré de nombreux bogues : démarrage avec page blanche une fois sur deux, erreur SQL sans explication (Not connected to the server or the connection to the server has been closed.), impossibilité presque systématique de faire du copier/coller des cellules du résultat et enfin une fuite de mémoire quasi systématique et monstrueuse sur une requête (voir test requête Q10).

I-F - Quelques différences dans les requêtes

SQL Server et PostGIS/PostGreSQL sont à même niveau et conforme à l'OGC. Il existe cependant quelques petites différences…

Forme des fonctions spatiales

Le standard OGC et la partie spatiale de la norme SQL (ISO/IEC 13249-3:2016, Part 3 : spatial) propose des fonctions scalaires ayant la forme suivante : ST_nomFonction, par exemple ST_PointOnSurface, ST_Touches, ST_StartPoint.

PostGreSQL s'y plie, mais rajoute des fonctions en dehors du scope de l'OGC/norme SQL avec la même forme, ce qui entretient une certaine confusion et pose des problèmes pour les fonctions standardisées à venir.

SQL Server est proche du motif OGC/norme SQL à deux détails près :

- pas de blanc souligné après le ST ;

- méthode appliquée à un objet et non pas fonction scalaire (donc syntaxe différente)

Pour ce qui concerne les méthodes en dehors du scope OGC, SQL Server ne met pas de ST devant. Exemple : MakeValid (tandis que pour PostGreSQL, c'est ST_MakeValid qui n'existe pas, ni dans le standard OGC, ni dans la norme SQL).

Booléen

Dans PostGreSQL, les fonctions renvoyant un booléen renvoient true ou false, mais les fonctions PostGIS ne peuvent être utilisées directement comme expression logique d'un prédicat.

Dans SQL Server, elles renvoient 0 ou 1, et, de même, ne peuvent être utilisées directement comme expression logique d'un prédicat.

Concaténation

Elle s'opère avec + dans SQL Server et || dans postGreSQL. Cependant, tous deux acceptent la fonction CONCAT.

I-G - Choix des requêtes

Nous nous sommes cantonné aux fonctions principales et standardisées du spatial (OGC) avec parfois une jointure spatiale et des agrégats (spatiaux et scalaires).

La plupart des requêtes que nous avons élaborées résultent de la réalité d'exploitation des bases spatiales à un niveau basique.

Nous aurions pu élaborer des requêtes spatiales beaucoup plus complexes et spécifiques, mais les comparaisons auraient été faussées du fait des fonctions spécifiques en plus d'être peu représentatives.

N'hésitez pas à nous fournir de nouvelles requêtes spatiales de test, mais à condition qu'elles suivent le standard OGC ou que les fonctions spécifiques y figurant soient très largement utilisées.

Lorsque nous aurons de nouveau un panel intéressant de requêtes, nous produirons les résultats d'un nouveau test.

La communauté PostGreSQL est habituée à nous affirmer que le test est faussé en défaveur de PostGreSQL du fait de l'utilisation de Windows et non pas Linux, arguant que sous Linux, les performances sont nettement meilleures. Nous n'avons jamais remarqué cela en production, mais certaines choses tournent un peu plus vite et d'autres un peu moins… Et tout cela à la marge !

Comme SQL Server sortira sous Linux dans quelques mois, nous publierons un comparatif sur cette plateforme pour les deux SGBDR. Nous aurons alors un test complet permettant de comparer à la fois les plateformes complètes (OS + SGBDR) et de manière croisée.

De toute façon, nous donnons tous les éléments techniques sous forme de fichier de manière à ce que chacun puisse reproduire le test chez lui dans ses propres conditions particulières et en tirer les conclusions qui s'imposeront.

II - Requêtes du test

II-A - Recherche des données spatiales non valides avec explications détaillées (Q1)

Dans les deux cas, la requête produit les dix résultats suivants :

insee_com   postal_code   nom_comm
----------- ------------- ----------------------
09042       09240         LA BASTIDE-DE-SEROU
02232       02600         COYOLLES
71028       71270         BEAUVERNOIS
68078       68420         EGUISHEIM
05052       05600         EYGLIERS
48080       48300         LANGOGNE
22209       22650         PLOUBALAY
67486       67920         SUNDHOUSE
2B049       20214         CALENZANA
2A272       20100         SARTENE

SGBDR

Requête

Temps (ms)

PostGreSQL

SELECT *, ST_IsValidDetail(geom)
FROM   s_adr.code_insee_code_postal
WHERE ST_IsValid(geom) = false;

5 095

SQL Server

SELECT *, GEOM.IsValidDetailed()
FROM  S_ADR.CODE_INSEE_CODE_POSTAL
WHERE GEOM.STIsValid() = 0

581

La raison invoquée par postGreSQL est : « Nested shells » et indique une géométrie.

La raison invoquée par SQL Server est : « non valide, car l'anneau (2) est le premier anneau d'un polygone dont il n'est pas l'anneau extérieur. Le problème se produit dans l'entrée (3) d'une collection geometry. »

SQL Server s'avère près de huit fois plus rapide.

Figure 4 - plan d'exécution pour Q1 par SQL Server (cout : 1,490)

Figure 5 - plan d'exécution pour Q1 par PostGreSQL (cout 155353.92)

Le plan d'exécution de SQL Server ne montre aucun parallélisme. Le plan de PostGreSQL ne montre pas le détail des étapes de calcul.

NOTA : les coûts des plans d'exécution ne peuvent pas être comparés en données brutes entre SQL Server et PostGreSQL.

II-B - Recherche de la plus grande commune en surface (Q2)

Dans les deux cas, le résultat est la commune d'Arles.

SGBDR

Requête

Temps (ms)

PostGreSQL

SELECT *
FROM   S_GEO.COMMUNE
WHERE ST_Area(geom) =
(SELECT MAX(ST_Area(GEOM))
FROM   S_GEO.COMMUNE)

143

SQL Server

SELECT *
FROM  S_GEO.COMMUNE
WHEREGEOM.STArea() =
(SELECT MAX(GEOM.STArea())
FROM  S_GEO.COMMUNE)

152

Temps similaires, léger avantage à PostGreSQL (5,44 %).

II-C - Agrégation spatiale des communes en départements (Q3)

Le résultat donne 96 lignes.

REMARQUE : à cause du parallélisme, SQL Server renvoie des résultats identiques, mais l'ordre des lignes est aléatoire du fait que certains threads terminent plus rapidement que d'autres. Ceci est parfaitement normal dans un SGBD relationnel fonctionnant sur des principes ensemblistes, car il n'existe pas d'ordre naturel ou par défaut dans les ensembles de données.

SGBDR

Requête

Temps (ms)

PostGreSQL

SELECT CODE_DEPT, ST_Union(GEOM)
FROM   S_GEO.COMMUNE
GROUP BY CODE_DEPT;

55 661

SQL Server

SELECT CODE_DEPT, geometry::UnionAggregate(GEOM)
FROM  S_GEO.COMMUNE
GROUPBY CODE_DEPT;

5 691

Figure 6 - Plan d'exécution de SQL Server pour Q3

Figure 7 - Plan d'exécution de PostGreSQL pour Q3

Le plan d'exécution de SQL Server montre une utilisation massive du parallélisme dans deux séquences différentes. En revanche PostGreSQL n'utilise pas de parallélisme bien qu'il fasse une lecture par balayage de la table (sequential scan).

SQL Server s'avère près de dix fois plus rapide.

II-D - Recherche des communes les plus proches de certains points (Q4)

Recherches des communes les plus proches de seize points distincts dont dix situés à l'intérieur de communes et six en dehors.

SGBDR

Requête

Temps (ms)

PostGreSQL

WITH
POINTS AS
(
SELECT CAST('POINT ( 430354.933 6623007.700)' AS geometry) AS P, 1 AS N
UNION ALL
SELECT CAST('POINT ( 980190.133 6333111.233)' AS geometry), 2
UNION ALL
SELECT CAST('POINT ( 574865.267 6909297.167)' AS geometry), 3
UNION ALL
SELECT CAST('POINT ( 501587.200 6548318.933)' AS geometry), 4
UNION ALL
SELECT CAST('POINT ( 444282.067 6251421.667)' AS geometry), 5
UNION ALL
SELECT CAST('POINT ( 460953.333 6775817.633)' AS geometry), 6
UNION ALL
SELECT CAST('POINT (1032008.400 6323716.133)' AS geometry), 7
UNION ALL
SELECT CAST('POINT ( 875328.767 6866887.500)' AS geometry), 8
UNION ALL
SELECT CAST('POINT ( 666398.867 6560452.500)' AS geometry), 9
UNION ALL
SELECT CAST('POINT ( 354528.400 6636467.900)' AS geometry), 10
UNION ALL
SELECT CAST('POINT (1251250.400 6464646.125)' AS geometry), 11
UNION ALL
SELECT CAST('POINT ( 315060.950 6874532.888)' AS geometry), 12
UNION ALL
SELECT CAST('POINT ( 415263.920 7077070.707)' AS geometry), 13
UNION ALL
SELECT CAST('POINT ( 559988.123 6050300.400)' AS geometry), 14
UNION ALL
SELECT CAST('POINT ( 334455.250 6333111.200)' AS geometry), 15
UNION ALL
SELECT CAST('POINT ( 312459.258 7172737.333)' AS geometry), 16
),
DISTANCES AS
(
SELECT N, P, ID_GEOFLA, INSEE_COM, NOM_COM, GEOM,
ST_Distance(P, GEOM) AS D, RANK() OVER(PARTITION BY N ORDER BY ST_Distance(P, GEOM)) AS R
FROM   S_GEO.COMMUNE
CROSS JOIN POINTS
)
SELECT *, CASE D WHEN 0 THEN 'intérieur' ELSE 'extérieur' END AS SITUATION
FROM   DISTANCES
WHERE R = 1;

9 837

SQL Server

WITH
POINTS AS
(
SELECT CAST('POINT ( 430354.933 6623007.700)' AS geometry) AS P, 1 AS N
UNION ALL
SELECT CAST('POINT ( 980190.133 6333111.233)' AS geometry), 2
UNION ALL
SELECT CAST('POINT ( 574865.267 6909297.167)' AS geometry), 3
UNION ALL
SELECT CAST('POINT ( 501587.200 6548318.933)' AS geometry), 4
UNION ALL
SELECT CAST('POINT ( 444282.067 6251421.667)' AS geometry), 5
UNION ALL
SELECT CAST('POINT ( 460953.333 6775817.633)' AS geometry), 6
UNION ALL
SELECT CAST('POINT (1032008.400 6323716.133)' AS geometry), 7
UNION ALL
SELECT CAST('POINT ( 875328.767 6866887.500)' AS geometry), 8
UNION ALL
SELECT CAST('POINT ( 666398.867 6560452.500)' AS geometry), 9
UNION ALL
SELECT CAST('POINT ( 354528.400 6636467.900)' AS geometry), 10
UNION ALL
SELECT CAST('POINT (1251250.400 6464646.125)' AS geometry), 11
UNION ALL
SELECT CAST('POINT ( 315060.950 6874532.888)' AS geometry), 12
UNION ALL
SELECT CAST('POINT ( 415263.920 7077070.707)' AS geometry), 13
UNION ALL
SELECT CAST('POINT ( 559988.123 6050300.400)' AS geometry), 14
UNION ALL
SELECT CAST('POINT ( 334455.250 6333111.200)' AS geometry), 15
UNION ALL
SELECT CAST('POINT ( 312459.258 7172737.333)' AS geometry), 16
),
DISTANCES AS
(
SELECT N, P, ID_GEOFLA, INSEE_COM, NOM_COM, GEOM,
P.STDistance(GEOM) AS D, RANK() OVER(PARTITION BY N ORDER BY P.STDistance(GEOM)) AS R
FROM  S_GEO.COMMUNE
CROSS JOIN POINTS
)
SELECT *, CASE D WHEN 0 THEN 'intérieur' ELSE 'extérieur' END AS SITUATION
FROM  DISTANCES
WHERER = 1;

17 738

La requête produit seize résultats identiques. Même les distances sont strictement identiques sur les neuf décimales produites !

Figure 8 - résultats vus à travers l'outil SSMS de SQL Server

Les plans de requête sont très différents et montrent une continuité du parallélisme dans SQL Server.

Figure 9 - Plan de requête (outil Sentry) pour SQL Server, requête Q4

Figure 10 - Plan de requête pour PotsGreSQL, requête Q4

NOTA : dans la figure 10 (plan de requête PostGreSQL), nous avons éliminé une partie du plan montrant seize fois l'appel à la fonction.

Avantage à PostGreSQL qui met presque deux fois moins de temps que SQL Server.

II-E - Recherches de départements distants de moins de 5 km et ne se touchant pas (Q5)

Les résultats sont identiques et dans le même ordre :

NOMS                                           GEO
---------------------------------------------- -------------------------------------------
CALVADOS / SEINE-MARITIME                      0x000000000104550E0000CDCCCCCC2BEF2041CD...
YVELINES / PARIS                               0x000000000104830500009A99999970E4234167...
ALPES-DE-HAUTE-PROVENCE / BOUCHES-DU-RHONE     0x000000000104DD0F000034333333C2CC2C4134...
PARIS / VAL-D'OISE                             0x000000000104EA04000000000000F892234100...
HAUTE-SAONE / HAUT-RHIN                        0x000000000104A10C000034333333A2202F4167...
LOIRE-ATLANTIQUE / MAYENNE                     0x000000000104600E000034333333D5031A4134...
VOSGES / MOSELLE                               0x0000000001048D13000000000000EFB32E4134...

SGBDR

Requête

Temps (ms)

PostGreSQL

SELECT D1.nom_dept || ' / ' || D2.nom_dept AS NOMS, ST_Union(D1.geom, D2.geom) AS GEO
FROM   s_geo.departement AS D1
JOIN s_geo.departement AS D2
ON ST_Distance(D1.geom, D2.geom) < 5000
AND ST_Touches(D1.geom, D2.geom) = false
AND D1.gid < D2.gid;

31 968

SQL Server

SELECT D1.NOM_DEPT + ' / ' + D2.NOM_DEPT AS NOMS, D1.GEOM.STUnion(D2.GEOM) AS GEO
FROM  S_GEO.DEPARTEMENT AS D1
JOIN S_GEO.DEPARTEMENT AS D2
ON D1.GEOM.STDistance(D2.GEOM) < 5000
AND D1.GEOM.STTouches(D2.GEOM) = 0
AND D1.GID < D2.GID;

6 785

Le plan produit par SQL Server est classique et n'effectue aucun parallélisme. Il est pratiquement identique à celui de PostGreSQL.

Figure 11 - Plan de requête SQL Server pour Q5

Figure 12 - Plan de requête PostGreSQL pour Q5

SQL Server s'avère près de cinq fois plus rapide.

Figure 13 - Résultats graphiques partiels vus à travers l'outil SSMS de SQL Server

II-F - Recherche des communes intérieures au département faisant au moins une surface de 34 567 890 m² (Q6)

Cette recherche ne doit pas prendre en compte les communes limitrophes, c'est-à-dire celles ayant une frontière commune avec le département de rattachement.

SGBDR

Requête

Temps (ms)

PostGreSQL

SELECT CODE_COM, NOM_COM, C.CODE_DEPT, C.NOM_DEPT, C.GEOM
FROM   S_GEO.COMMUNE AS C
JOIN S_GEO.DEPARTEMENT AS D
ON C.CODE_DEPT = D.CODE_DEPT
AND ST_Intersects(C.GEOM, ST_Boundary(D.GEOM)) = false
WHERE ST_Area(C.GEOM) > 34567890;

15 081

SQL Server

SELECT CODE_COM, NOM_COM, C.CODE_DEPT, C.NOM_DEPT, C.GEOM
FROM  S_GEO.COMMUNE AS C
JOIN S_GEO.DEPARTEMENT AS D
ON C.CODE_DEPT = D.CODE_DEPT
AND C.GEOM.STIntersects(D.GEOM.STBoundary()) = 0
WHERE C.GEOM.STArea() > 34567890;

3 351

La réponse contient 1716 lignes, dont les dix premiers et dix derniers résultats sont (figure 14) :

Figure 14 - Résultats partiels de la requête Q6 (SSMS)

Les plans de requête sont assez similaires :

Figure 15 - plan de requête PostGreSQL pour la requête Q6

Figure 16 - Plan de requête SQL Server pour Q6

Cependant, SQL Server nous suggère de créer l'index suivant :

CREATE NONCLUSTERED INDEX XG_COM_CODE_DEPT
ON S_GEO.COMMUNE (CODE_DEPT)
INCLUDE (CODE_COM,NOM_COM,NOM_DEPT,GEOM);

Arguant que celui-ci améliorerait de 96 % les performances de la requête… Après de nouveaux tests avec cet index, et alors que le nouveau plan montre bien l'utilisation de cet index, nous pouvons affirmer que cette amélioration n'existe pas, pire, il apparaît que le résultat est légèrement moins bon !

SQL Server s'avère 4,5 fois plus rapide que PostGreSQL.

II-G - Quelle est la commune au barycentre de chaque département ? (Q7)

SGBDR

Requête

Temps (ms)

PostGreSQL

SELECT CODE_COM, NOM_COM, C.CODE_DEPT, C.NOM_DEPT, C.GEOM
FROM   S_GEO.COMMUNE AS C
JOIN S_GEO.DEPARTEMENT AS D
ON C.CODE_DEPT = D.CODE_DEPT
AND ST_Intersects(C.GEOM, ST_Centroid(D.GEOM)) = true;

18 139

SQL Server

SELECT CODE_COM, NOM_COM, C.CODE_DEPT, C.NOM_DEPT, C.GEOM
FROM  S_GEO.COMMUNE AS C
JOIN S_GEO.DEPARTEMENT AS D
ON C.CODE_DEPT = D.CODE_DEPT
AND C.GEOM.STIntersects(D.GEOM.STCentroid()) = 1;

286

Les dix premiers résultats sont les suivants :

Figure 17 - les 10 premiers résultats de la requête Q7 (SSMS)

Les performances sont surprenantes : les deux systèmes montrent un écart de 63 fois en faveur de SQL Server ce qui est franchement exceptionnel ! Quelle peut en être la raison ? D'autant que là encore, les plans de requête sont quasi identiques !

Figure 18 - Plan de requête PostGreSQL pour Q7

Figure 19 - Plan de requête SQL Server pour Q7

SQL Server s'annonce 63 fois plus rapide que PostGreSQL.

II-H - Quelle est la commune dont la surface est la plus proche de 6 666 666 m² (Q8)

Le résultat est la commune de Neuilly-sur-Marne.

SGBDR

Requête

Temps (ms)

PostGreSQL

WITH T AS (
SELECT CODE_COM, INSEE_COM, NOM_COM, ST_Area(GEOM) AS SURFACE, GEOM,
RANK() OVER(ORDER BY ABS(ST_Area(GEOM) - 6666666)) AS RANK_DIF
FROM   S_GEO.COMMUNE)
SELECT CODE_COM, INSEE_COM, NOM_COM, GEOM, SURFACE
FROM   T
WHERE RANK_DIF = 1;

395

SQL Server

WITH T AS (
SELECT CODE_COM, INSEE_COM, NOM_COM, GEOM.STArea() AS SURFACE, GEOM,
RANK() OVER(ORDER BY ABS(GEOM.STArea() - 6666666)) AS RANK_DIF
FROM  S_GEO.COMMUNE)
SELECT CODE_COM, INSEE_COM, NOM_COM, GEOM, SURFACE
FROM  T
WHERERANK_DIF = 1;

239

La différence de performance est assez peu importante, mais en faveur de SQL Server.

En revanche, la différence de plan de requête est importante.

Figure 20 - Plan de requête PostGreSQL pour Q8 (PGAdmin 4)

Figure 21 - Plan de requête SQL Server pour Q8 (Sentry Plan Explorer)

SQL Server faisant du parallélisme à l'aide de 48 threads dans deux séquences différentes identifiées par un demi-cadre rouge dans la figure 21.

Nous avons testé de nouveaux résultats en augmentant le niveau de parallélisme progressivement depuis un thread (donc pas de parallélisme), jusqu'à 48 pour SQL server. Voici la courbe des temps obtenus (figure 22) :

Figure 22 - Test de parallélisme pour SQL Server, requête Q8

À l'évidence on constate qu'au-delà de 30, le temps se stabilise et qu'il est déjà très bon, aux alentours de 20.

La conclusion est que SQL Server mobilise beaucoup plus de ressources que PostGreSQL pour une performance très voisine, et que le réglage de la limite du parallélisme dans SQL Server n'est pas un vaine tâche (personnellement je conseille d'être toujours en dessous de la moitié du nombre de cœurs, et dans ce cas précis 20 me va bien !)

SQL Server s'annonce 1,7 fois plus rapide que PostGreSQL.

II-I - Calcul de la longueur de routes pour toutes les catégories sauf inconnues (Q9)

Une très légère différence de résultat apparaît due aux calculs arrondis :

Résultat

Nature

SQL Server

PostGreSQL

Autoroute

15294,8748325865

15294.8748325866

Départementale

379031,6276194950

379031.6276194940

Nationale

21565,3369883408

21565.3369883408

Mais cette différence est bien inférieure au millimètre !

SGBDR

Requête

Temps (ms)

PostGreSQL

SELECT CLASS_ADM, SUM(ST_Length(GEOM)) / 1000 AS LONGUEUR_KM
FROM   S_RTE.TRONCON_ROUTE
WHERE CLASS_ADM <> 'Sans objet'
GROUP BY CLASS_ADM

462

SQL Server

SELECT CLASS_ADM, SUM(GEOM.STLength()) / 1000 AS LONGUEUR_KM
FROM  S_RTE.TRONCON_ROUTE
WHERECLASS_ADM <> 'Sans objet'
GROUPBY CLASS_ADM;

448

Au niveau des performances, les temps mesurés sont similaires…

Temps similaires, léger avantage à SQL Server (3,11 %).

II-J - Calcul de la longueur de routes par département et numéro de route (Q10)

La requête fait appel à une jointure spatiale et un agrégat (SUM) sur la longueur des géométries et y ajoute un tri du résultat qui comporte 41 145 lignes.

Notons que PostGreSQL présente les chaines vides en fin de tri alphabétique ce qui n'est pas conforme au jeu de caractères ni à la séquence de collation (french_france_1252).

Figure 23 - Plan d'exécution PostGreSQL pour la requête Q10

Le plan PostGreSSQL (figure 23) est simple, tandis que celui de SQL Server est assez complexe utilisant à la fois une recherche sur l'index spatial pour les tronçons et sur l'index de la clef primaire (de type clustered) pour le reste des informations de la même table. On note une importante séquence parallélisée (mise en évidence en vert dans la figure 24) comportant 18 opérations sur les 21 que compte le plan.

Figure 24 - Plan d'exécution SQL Server pour la requête Q10 (vue avec Sentry Plan Explorer)

NOTA : à plusieurs reprises la requête Q10 PostGreSQL n'a jamais abouti, du fait d'une fuite de mémoire de PGAdmin 4 (voir figure 24). Nous avons dû arrêter l'application au bout de quelques minutes, non sans qu'elle n'ait consommé plusieurs Go de RAM !

Figure 25 - Fuite de mémoire de pgAdmin 4 (23 Go !)

Il semble évident que la version 1.1 de pgAdmin 4 n'est pas du tout stable et les nombreux bogues déjà évoqués la rendent difficilement exploitable.

SGBDR

Requête

Temps (ms)

PostGreSQL

SELECT D.CODE_DEPT, NUM_ROUTE, SUM(ST_Length(TR.GEOM)) / 1000 AS LONGUEUR_KM
FROM   S_RTE.TRONCON_ROUTE AS TR
JOIN S_GEO.DEPARTEMENT AS D
ON ST_Intersects(TR.GEOM, D.GEOM) = true
GROUP BY D.CODE_DEPT, NUM_ROUTE
ORDER BY CODE_DEPT, NUM_ROUTE;

49 171

SQL Server

SELECT D.CODE_DEPT, NUM_ROUTE, SUM(TR.GEOM.STLength()) / 1000 AS LONGUEUR_KM
FROM  S_RTE.TRONCON_ROUTE AS TR
JOIN S_GEO.DEPARTEMENT AS D
ON TR.GEOM.STIntersects(D.GEOM) = 1
GROUPBY D.CODE_DEPT, NUM_ROUTE
ORDER BY CODE_DEPT, NUM_ROUTE;

7 680

SQL Server s'annonce 6,4 fois plus rapide que PostGreSQL.

III - Conclusion

Le tableau ci-dessous résume les temps des requêtes et nous avons arrondi les gains à deux chiffres significatifs :

Query

Temps PG (ms)

Temps SQL Server (ms)

Gain PG

Gain SQL Server

Winner

Q1

5 095

581

0,11

8,8

SQL Server

Q2

143

152

1,1

0,95

PostGreSQL

Q3

55 661

5 691

0,1

9,8

SQL Server

Q4

9 835

17 738

1,8

0,55

PostGreSQL

Q5

31 968

6 785

0,21

4,7

SQL Server

Q6

15 081

3 351

0,22

4,5

SQL Server

Q7

18 139

286

0,016

63

SQL Server

Q8

395

239

0,6

1,7

SQL Server

Q9

462

448

0,96

1

=

Q10

49 171

7 680

0,16

6,4

SQL Server

Total

185 950

42 951

0,5276

10,14

moyenne

SQL Server est sorti gagnant face à PostGreSQL pour huit requêtes sur dix avec un gain moyen de douze fois ce qui est important.

PostGreSQL est sorti en tête devant postGreSQL pour deux requêtes sur dix avec un gain moyen de 1,45 fois ce qui est assez faible.

Globalement, sur les dix requêtes, en rapport de gain, SQL Server est dix fois supérieur à PostGreSQL.

En additionnant les dix temps moyens des requêtes par serveur, nous obtenons les métriques suivantes :

PostGreSQL : 185 950 ms ;

SQL Server : 42 951 ms.

L'écart est alors de 4,3 en faveur de SQL Server.

Nous pouvons donc dire que SQL Server est plus de quatre fois plus rapide que PostGreSQL dans la manipulation des données spatiales.

IV - Fichiers relatifs

Afin que vous puissiez reproduire ce test chez vous avec l'ensemble des données et requêtes, cet article est accompagné des fichiers suivants :

Nom fichier

Description

Backup restore PostGreSQL.txt

Contient les commandes système de sauvegarde et restauration de la base PostGreSQL

Backup Restore SQL Server.sql

Contient les commandes SQL de sauvegarde et restauration de la base SQL Server

DB_GEO_SQLSERVER.BAK2

Fichier de sauvegarde SQL Server (compressé)

DB_GEOPG.BAK

Fichier de sauvegarde PostGreSQL

Requêtes PG.sql

Liste des dix requêtes PostGreSQL de test

Requêtes SQL Server.sql

Liste des dix requêtes SQL Server de test

V - Tests annexes

V-A - Sauvegarde de la base

PostGreSQL

SQL Server

Requête

Temps (ms)
Taille Mo

Temps (ms)
Taille (Mo)

Requête

"C:\Program Files\PostgreSQL\9.6\bin\pg_dump.exe"
--host localhost --port 5432 --username postgres
--blobs --format=c -f "C:\SAVE_DB\DB_GEOPG.BAK" DB_GEO

56 140
332

14 577
1 002

BACKUP DATABASE DB_GEO TO DISK = 'C:\SAVE_DB\DB_GEO_SQLSERVER.BAK'

"C:\Program Files\PostgreSQL\9.6\bin\pg_dump.exe"
--host localhost --port 5432 --username postgres
--blobs --format=d -f "C:\SAVE_DB\DB_GEOPG.BAK" DB_GEO

55 210
323

6 336
362

BACKUP DATABASE DB_GEO TO DISK = 'C:\SAVE_DB\DB_GEO_SQLSERVER.BAK2'
WITH COMPRESSION

"C:\Program Files\PostgreSQL\9.6\bin\pg_dump.exe"
-j 8 --host localhost --port 5432 --username postgres
--blobs --format=d -f "C:\SAVE_DB\DB_GEOPG2.BAK" DB_GEO

16 440
323

4 102
362

BACKUP DATABASE DB_GEO
TO DISK = 'C:\SAVE_DB\DB_GEO_SQLSERVER.BAK31',
DISK = 'D:\SAVE_DB\DB_GEO_SQLSERVER.BAK32'

7.103
371

BACKUP DATABASE DB_GEO
TO DISK = 'C:\SAVE_DB\DB_GEO_SQLSERVER.BAK41',
DISK = 'D:\SAVE_DB\DB_GEO_SQLSERVER.BAK42'
WITH COMPRESSION

NOTA : la commande PG dump est bloquante, car elle pose un verrou de type lecture (AccessShareLock) pendant toute la durée de la sauvegarde, verrou qui peut ne pas être acquis si une transaction est en cours. Ceci empêche donc les sauvegardes à chaud contrairement à SQL Server qui effectue un snapshot de la base, ce qui n'empêche nullement des transactions en cours, ni le démarrage de nouvelles transactions au cours de la sauvegarde.

Au mieux, pour la sauvegarde, PostGreSQL met presque quatre fois plus de temps que SQL Server en mode compressé avec deux destinations. La taille de la sauvegarde est en revanche 11 % moins volumineuse avec la sauvegarde PostGreSQL que le fichier SQL Server de sauvegarde résultant. En multipliant le parallélisme des destinations, SQL Server permet de diminuer très sensiblement le temps de réponse.

V-B - Restauration de la base

PostGreSQL

SQL Server

Requête

Temps (ms)

Temps (ms)

Requête

"C:\Program Files\PostgreSQL\9.6\bin\pg_restore.exe"
-U postgres -d DB_GEO "C:\SAVE_DB\DB_GEOPG.BAK"

132 550

20 422

RESTORE DATABASE DB_GEO FROM DISK = 'C:\SAVE_DB\DB_GEO_SQLSERVER.BAK'

"C:\Program Files\PostgreSQL\9.6\bin\pg_restore.exe"
-j 8 -U postgres -d DB_GEO "C:\SAVE_DB\DB_GEOPG.BAK"

40 900

25 460

RESTORE DATABASE DB_GEO FROM DISK = 'C:\SAVE_DB\DB_GEO_SQLSERVER.BAK2'

18 820

RESTORE DATABASE DB_GEO
FROM DISK = 'C:\SAVE_DB\DB_GEO_SQLSERVER.BAK31',
DISK = 'D:\SAVE_DB\DB_GEO_SQLSERVER.BAK32'

22 615

RESTORE DATABASE DB_GEO
FROM DISK = 'C:\SAVE_DB\DB_GEO_SQLSERVER.BAK41',
DISK = 'D:\SAVE_DB\DB_GEO_SQLSERVER.BAK42'

Au mieux, pour la restauration, PostGreSQL met deux fois plus de temps que SQL Server en mode non compressé en utilisant huit threads.

NOTA : les commandes de sauvegardes et de restaurations de PostGreSQL sont très mal documentées et peu d'exemples montrent comment faire telle ou telle sauvegarde et encore moins, restauration. Nous avons perdu plus de quatre heures en tentatives diverses et essais infructueux avant de trouver la bonne syntaxe par tâtonnement. Dans certains cas, il faut d'abord créer une base dans PostGreSQL pour la restaurer alors que dans SQL Server la restauration d'une base induit une création, ce qui simplifie grandement les choses (une seule commande) et diminue le temps de traitement (nous n'avons pas tenu compte du temps de création de la base PG dans notre métrique de la restauration). Enfin, PostGreSQL nous a fait deux warnings lors de la restauration, concernant des fonctions intégrées du template PostGIS, ce qui fait un peu « désordre »…

ANNEXE 1 - Ajout des clefs primaires et des index spatiaux pour SQL Server après import

ALTER TABLE [S_ADR].[CODE_INSEE_CODE_POSTAL] ADD GID INT IDENTITY PRIMARY KEY;
ALTER TABLE [S_GEO].[CANTON] ADD GID INT IDENTITY PRIMARY KEY;
ALTER TABLE [S_GEO].[COMMUNE] ADD GID INT IDENTITY PRIMARY KEY;
ALTER TABLE [S_GEO].[DEPARTEMENT] ADD GID INT IDENTITY PRIMARY KEY;
ALTER TABLE [S_GEO].[LIMITE_CANTON] ADD GID INT IDENTITY PRIMARY KEY;
ALTER TABLE [S_GEO].[LIMITE_COMMUNE] ADD GID INT IDENTITY PRIMARY KEY;
ALTER TABLE [S_GEO].[LIMITE_DEPARTEMENT] ADD GID INT IDENTITY PRIMARY KEY;
ALTER TABLE [S_GEO].[REGION] ADD GID INT IDENTITY PRIMARY KEY;
ALTER TABLE [S_RTE].[AERODROME] ADD GID INT IDENTITY PRIMARY KEY;
ALTER TABLE [S_RTE].[COMMUNE] ADD GID INT IDENTITY PRIMARY KEY;
ALTER TABLE [S_RTE].[COMMUNICATION_RESTREINTE] ADD GID INT IDENTITY PRIMARY KEY;
ALTER TABLE [S_RTE].[COTE_FRONTIERE] ADD GID INT IDENTITY PRIMARY KEY;
ALTER TABLE [S_RTE].[LIMITE_ADMINISTRATIVE] ADD GID INT IDENTITY PRIMARY KEY;
ALTER TABLE [S_RTE].[NOEUD_COMMUNE] ADD GID INT IDENTITY PRIMARY KEY;
ALTER TABLE [S_RTE].[NOEUD_FERRE] ADD GID INT IDENTITY PRIMARY KEY;
ALTER TABLE [S_RTE].[NOEUD_ROUTIER] ADD GID INT IDENTITY PRIMARY KEY;
ALTER TABLE [S_RTE].[TRONCON_HYDROGRAPHIQUE] ADD GID INT IDENTITY PRIMARY KEY;
ALTER TABLE [S_RTE].[TRONCON_ROUTE] ADD GID INT IDENTITY PRIMARY KEY;
ALTER TABLE [S_RTE].[TRONCON_VOIE_FERREE] ADD GID INT IDENTITY PRIMARY KEY;
ALTER TABLE [S_RTE].[ZONE_OCCUPATION_SOL] ADD GID INT IDENTITY PRIMARY KEY;
CREATE SPATIAL INDEX XS_S_ADR_CODE_INSEE_CODE_POSTAL_GEOM_BC0DFC82_E903_48C2_BA68_DC9CF5744FE6_20170108
ON [S_ADR].[CODE_INSEE_CODE_POSTAL] ([GEOM])
WITH ( BOUNDING_BOX = (-62, -22, 56, 52) );
CREATE SPATIAL INDEX XS_S_GEO_CANTON_GEOM_38CAC797_AE97_4458_9765_55FD25ED40AF_20170108
ON [S_GEO].[CANTON] ([GEOM])
WITH ( BOUNDING_BOX = (99217, 6.04965e+006, 1.24242e+006, 7.11048e+006) );
CREATE SPATIAL INDEX XS_S_GEO_COMMUNE_GEOM_C83BBDA2_7339_40C4_B9D4_B9FEBCD91549_20170108
ON [S_GEO].[COMMUNE] ([GEOM])
WITH ( BOUNDING_BOX = (99217, 6.04965e+006, 1.24242e+006, 7.11048e+006) );
CREATE SPATIAL INDEX XS_S_GEO_DEPARTEMENT_GEOM_51802421_B65D_49B3_9080_6572B9760913_20170108
ON [S_GEO].[DEPARTEMENT] ([GEOM])
WITH ( BOUNDING_BOX = (99217, 6.04965e+006, 1.24242e+006, 7.11048e+006) );
CREATE SPATIAL INDEX XS_S_GEO_LIMITE_CANTON_GEOM_C3548EDB_28B8_464F_998D_5F059FE4ABCB_20170108
ON [S_GEO].[LIMITE_CANTON] ([GEOM])
WITH ( BOUNDING_BOX = (99217, 6.04965e+006, 1.24242e+006, 7.11048e+006) );
CREATE SPATIAL INDEX XS_S_GEO_LIMITE_COMMUNE_GEOM_15E61A92_68F1_423C_A513_3A0B19424DD3_20170108
ON [S_GEO].[LIMITE_COMMUNE] ([GEOM])
WITH ( BOUNDING_BOX = (99217, 6.04965e+006, 1.24242e+006, 7.11048e+006) );
CREATE SPATIAL INDEX XS_S_GEO_LIMITE_DEPARTEMENT_GEOM_5F416933_F42A_42A5_81D4_C68C99634A5E_20170108
ON [S_GEO].[LIMITE_DEPARTEMENT] ([GEOM])
WITH ( BOUNDING_BOX = (99217, 6.04965e+006, 1.24242e+006, 7.11048e+006) );
CREATE SPATIAL INDEX XS_S_GEO_REGION_GEOM_E81C9089_822B_440A_A913_7D1EA6A6DCAE_20170108
ON [S_GEO].[REGION] ([GEOM])
WITH ( BOUNDING_BOX = (99225, 6.04965e+006, 1.24238e+006, 7.11053e+006) );
CREATE SPATIAL INDEX XS_S_RTE_AERODROME_GEOM_970E6D00_7988_4274_B518_5FFB58499909_20170108
ON [S_RTE].[AERODROME] ([GEOM])
WITH ( BOUNDING_BOX = (104879, 6.06389e+006, 1.23246e+006, 7.10481e+006) );
CREATE SPATIAL INDEX XS_S_RTE_COMMUNE_GEOM_374CA1F4_4BD3_4CCF_94EB_03C296679B02_20170108
ON [S_RTE].[COMMUNE] ([GEOM])
WITH ( BOUNDING_BOX = (99038, 6.04656e+006, 1.24244e+006, 7.11048e+006) );
CREATE SPATIAL INDEX XS_S_RTE_COMMUNICATION_RESTREINTE_GEOM_B7933F54_E122_43D3_BE9F_D304E1676E75_20170108
ON [S_RTE].[COMMUNICATION_RESTREINTE] ([GEOM])
WITH ( BOUNDING_BOX = (136746, 6.13961e+006, 1.22909e+006, 7.10688e+006) );
CREATE SPATIAL INDEX XS_S_RTE_COTE_FRONTIERE_GEOM_3EC80F17_5D8F_4A4E_A9C1_30C686D32269_20170108
ON [S_RTE].[COTE_FRONTIERE] ([GEOM])
WITH ( BOUNDING_BOX = (99038, 6.04656e+006, 1.24244e+006, 7.11048e+006) );
CREATE SPATIAL INDEX XS_S_RTE_LIMITE_ADMINISTRATIVE_GEOM_28488076_9A6B_464E_B18D_4EEA576F453F_20170108
ON [S_RTE].[LIMITE_ADMINISTRATIVE] ([GEOM])
WITH ( BOUNDING_BOX = (99038, 6.04656e+006, 1.24244e+006, 7.11048e+006) );
CREATE SPATIAL INDEX XS_S_RTE_NOEUD_COMMUNE_GEOM_7A8FB9F1_2774_4BE4_A582_91FDEE694D9C_20170108
ON [S_RTE].[NOEUD_COMMUNE] ([GEOM])
WITH ( BOUNDING_BOX = (102422, 6.05197e+006, 1.23912e+006, 7.10852e+006) );
CREATE SPATIAL INDEX XS_S_RTE_NOEUD_FERRE_GEOM_25735B55_11BC_46E6_8089_2E563EE8DECA_20170108
ON [S_RTE].[NOEUD_FERRE] ([GEOM])
WITH ( BOUNDING_BOX = (147061, 6.10919e+006, 1.22984e+006, 7.1068e+006) );
CREATE SPATIAL INDEX XS_S_RTE_NOEUD_ROUTIER_GEOM_3A64D2DD_5EC9_4EED_A140_A0454EC4F8F8_20170108
ON [S_RTE].[NOEUD_ROUTIER] ([GEOM])
WITH ( BOUNDING_BOX = (100076, 6.02103e+006, 1.32974e+006, 7.12064e+006) );
CREATE SPATIAL INDEX XS_S_RTE_TRONCON_HYDROGRAPHIQUE_GEOM_5E21F066_6DE0_4674_98A4_0BBC30245CB2_20170108
ON [S_RTE].[TRONCON_HYDROGRAPHIQUE] ([GEOM])
WITH ( BOUNDING_BOX = (142855, 6.06364e+006, 1.24221e+006, 7.10382e+006) );
CREATE SPATIAL INDEX XS_S_RTE_TRONCON_ROUTE_GEOM_ADEE13A4_957F_4473_9328_4C1B450ADBEB_20170108
ON [S_RTE].[TRONCON_ROUTE] ([GEOM])
WITH ( BOUNDING_BOX = (100076, 6.02103e+006, 1.32974e+006, 7.12064e+006) );
CREATE SPATIAL INDEX XS_S_RTE_TRONCON_VOIE_FERREE_GEOM_FDF04A4E_173C_4A02_9DEA_E811CF3D1538_20170108
ON [S_RTE].[TRONCON_VOIE_FERREE] ([GEOM])
WITH ( BOUNDING_BOX = (147061, 6.10919e+006, 1.22987e+006, 7.1068e+006) );
CREATE SPATIAL INDEX XS_S_RTE_ZONE_OCCUPATION_SOL_GEOM_42C4083F_9D6B_497D_A43C_6D6A32D506BA_20170108
ON [S_RTE].[ZONE_OCCUPATION_SOL] ([GEOM])
WITH ( BOUNDING_BOX = (79389, 6.03224e+006, 1.26265e+006, 7.12045e+006) );