Mon introduction aux bases de données et à PostgreSQL était pour le développement d’applications Web et l’analyse statistique. J’ai appris juste assez de SQL pour que les requêtes renvoient les bonnes réponses. Grâce à mon travail avec PostGIS (et FOSS4G), je suis devenu ami avec Paul Ramsey. Nous sommes maintenant des collègues chez Crunchy Data et il m’aide à améliorer mon SQL-fu. L’une des premières leçons qu’il m’a apprises était « Essayez d’utiliser des jointures plutôt que des sous-requêtes. »

Le post d’aujourd’hui va fonctionner à travers ce conseil, comme Paul et moi travaillons à travers du SQL.

Ce à quoi Nous essayons de répondre

J’essaie de créer de nouveaux supports pédagogiques et oraux sur l’utilisation de SQL en science des données et je travaillais sur une manipulation de données de pré-analyse. J’avais une table, fire_weather, qui est un sous-ensemble de la table météo, et je veux trouver toutes les entrées de weather qui ne sont PAS dans fire_weather. Mon instinct initial était d’écrire une sous-requête, mais cela semblait être une requête simple et facile pour suivre le conseil « utiliser une jointure » de Paul.

Requête de jointure incorrecte

J’ai commencé avec cette requête de jointure:

 select count(weather.*) from weather, fire_weather where weather.id != fire_weather.id;

Et cela n’a pas fonctionné (sinon je n’écrirais pas cet article de blog). Il s’avère que cela fait une jointure croisée où nous nous retrouvons avec toutes les combinaisons par paires de toutes les lignes des deux tables.

La requête de jointure appropriée

Donc, à ce stade, je me relâche (par opposition à sonner au téléphone) Paul et nous commençons à discuter de la façon de faire la jointure appropriée. Il s’avère que la bonne syntaxe est:

select count(weather.*)from weather left join fire_weather on weather.id = fire_weather.idwhere fire_weather.id is null;

Fondamentalement, vous faites une jointure externe gauche, vous donnant toutes les lignes de la table météo et uniquement les entrées fire_weather qui correspondent. Ensuite, vous filtrez tous les enregistrements où il y a des correspondances pour fire_weather. Assez simple à comprendre mais pas très défini, comme dans l’utilisation de la théorie des ensembles (qui est la base des relations dans les systèmes de bases de données relationnelles). Là encore, nous avons maintenant une requête de jointure fonctionnelle.

Analysez Ceci

Dans le cadre de mon cheminement vers une meilleure compréhension de SQL dans PostgreSQL, je suis devenu un grand fan d’EXPLAIN ANALYZE for for timings et en regardant le plan de requête. Juste par curiosité, je décide de regarder le calendrier et le plan de requête pour la requête de jointure. Voici la sortie et cela a pris environ 7 millisecondes avec un plan de requête un peu compliqué:

Aggregate (cost=1358.58..1358.59 rows=1 width=8) (actual time=6.780..6.781 rows=1 loops=1)
-> Hash Anti Join (cost=1168.76..1358.58 rows=1 width=64) (actual time=1.648..6.218 rows=6802 loops=1)
Hash Cond: (weather.id = fire_weather.id)
-> Seq Scan on weather (cost=0.00..159.05 rows=8205 width=68) (actual time=0.008..3.157 rows=8205 loops=1)
-> Hash (cost=785.56..785.56 rows=30656 width=4) (actual time=1.609..1.609 rows=1403 loops=1)
Buckets: 32768 Batches: 1 Memory Usage: 306kB
-> Seq Scan on fire_weather (cost=0.00..785.56 rows=30656 width=4) (actual time=0.008..1.399 rows=1403 loops=1)
Planning Time: 0.110 ms
Execution Time: 6.807 ms

Maintenant, la sous-requête

Et maintenant je voulais voir comment mon idée originale d’une sous-requête fonctionnerait. Voici la méthode de sous-requête pour répondre à la même question:

select count(weather.*) from weather where id not in (select id from fire_weather);

Plus d’analyse

Vous devriez voir pourquoi cette requête m’a plu, elle est très basée sur un ensemble et très simple à écrire. Quand je regarde cette requête avec explain analyze, j’obtiens:

Aggregate (cost=1052.02..1052.03 rows=1 width=8) (actual time=7.458..7.459 rows=1 loops=1)
-> Seq Scan on weather (cost=862.20..1041.76 rows=4102 width=64) (actual time=1.139..6.727 rows=6802 loops=1)
Filter: (NOT (hashed SubPlan 1))
Rows Removed by Filter: 1403
SubPlan 1
-> Seq Scan on fire_weather (cost=0.00..785.56 rows=30656 width=4) (actual time=0.004..0.643 rows=1403 loops=1)
Planning Time: 0.068 ms
Execution Time: 7.497 ms

Comparaison de requêtes

Nous nous retrouvons donc avec un plan et des horaires très simples qui sont à peu près les mêmes que la jointure. Paul et moi avons discuté des raisons pour lesquelles les horaires pourraient être si similaires et nous avons trouvé au moins deux raisons:

  1. L’ensemble de données a très peu de lignes (8 ko), de sorte que les performances de la sous-requête peuvent se dégrader avec un ensemble de données plus grand.
  2. Ma machine dispose de lecteurs de disque NVMe donnant à l’accès séquentiel une différence de performances encore plus grande.

Une autre sous-requête basée sur un ensemble

Enfin Paul, est venu avec une autre requête basée sur un ensemble pour répondre à la même question:

select * from weather exceptselect weather.* from weather join fire_weather on weather.id = fire_weather.id

Celui-ci utilise une nouvelle clause SQL, EXCEPT, qui fait partie des combinateurs de requêtes set operation. Une grande contrainte sur ces requêtes est que les requêtes de chaque côté de la clause except doivent renvoyer les mêmes colonnes et types de données. Cela explique pourquoi cette requête ne peut pas renvoyer le nombre total de lignes. Mais cette requête s’est avérée moins performante et un plan de requête beaucoup plus compliqué:

HashSetOp Except (cost=0.00..1984.13 rows=8205 width=74) (actual time=10.382..11.171 rows=6802 loops=1)
-> Append (cost=0.00..1532.86 rows=16410 width=74) (actual time=0.021..5.867 rows=9608 loops=1)
" -> Subquery Scan on ""*SELECT* 1"" (cost=0.00..241.10 rows=8205 width=44) (actual time=0.021..2.300 rows=8205 loops=1)"
-> Seq Scan on weather (cost=0.00..159.05 rows=8205 width=40) (actual time=0.006..0.537 rows=8205 loops=1)
" -> Subquery Scan on ""*SELECT* 2"" (cost=261.61..1209.71 rows=8205 width=44) (actual time=1.796..2.788 rows=1403 loops=1)"
-> Hash Join (cost=261.61..1127.66 rows=8205 width=40) (actual time=1.795..2.583 rows=1403 loops=1)
Hash Cond: (fire_weather.id = weather_1.id)
-> Seq Scan on fire_weather (cost=0.00..785.56 rows=30656 width=4) (actual time=0.008..0.413 rows=1403 loops=1)
-> Hash (cost=159.05..159.05 rows=8205 width=40) (actual time=1.768..1.768 rows=8205 loops=1)
Buckets: 16384 Batches: 1 Memory Usage: 765kB
-> Seq Scan on weather weather_1 (cost=0.00..159.05 rows=8205 width=40) (actual time=0.002..0.518 rows=8205 loops=1)
Planning Time: 0.127 ms
Execution Time: 11.975 ms

La torsion surprise

Ensuite, j’ai réfléchi un peu plus à la requête suggérée par Paul et j’ai réalisé que nous n’avions pas vraiment besoin de la jointure sur le côté droit de la clause except. Puisque fire_weather contient toutes les mêmes colonnes que weather, nous pouvons simplement utiliser les colonnes que nous voulons et obtenir la réponse attendue.

select id from weather exceptselect id from fire_weather;

Maintenant, cela a une belle syntaxe définie, ce qui la rend vraiment facile à comprendre. Si nous voulions réellement obtenir le nombre comme dans les autres requêtes, nous pouvons envelopper notre requête dans un CTE.

with count_me as (select id from weather exceptselect id from fire_weather)select count(*) from count_me;

Avec ce ticket d’or, nous obtenons des temps de requête de 6 ms et des plans de requête plus propres mais pas plus simples. Je dois noter que la propreté et la simplicité ne sont pas des facteurs clés dans l’évaluation d’un plan de requête.

HashSetOp Except (cost=0.00..1624.68 rows=8205 width=8) (actual time=4.834..5.313 rows=6802 loops=1)
-> Append (cost=0.00..1527.52 rows=38861 width=8) (actual time=0.004..3.248 rows=9608 loops=1)
-> Subquery Scan on "*SELECT* 1" (cost=0.00..241.10 rows=8205 width=8) (actual time=0.004..1.903 rows=8205 loops=1)
-> Seq Scan on weather (cost=0.00..159.05 rows=8205 width=4) (actual time=0.003..1.001 rows=8205 loops=1)
-> Subquery Scan on "*SELECT* 2" (cost=0.00..1092.12 rows=30656 width=8) (actual time=0.003..0.554 rows=1403 loops=1)
-> Seq Scan on fire_weather (cost=0.00..785.56 rows=30656 width=4) (actual time=0.002..0.409 rows=1403 loops=1)
Planning Time: 0.246 ms
Execution Time: 5.897 ms

Mes derniers points à retenir

Voici les dernières leçons que je voudrais vous laisser de ce petit exercice.

  1. Jamais de temps de requête du globe oculaire – ceux-ci étaient tous à la même vitesse pour mon œil. Expliquer analyser est votre ami.
  2. Écrivez la requête de la manière la plus logique, puis effectuez les timings. Si ce n’est pas bon, alors regardez une alternative (probablement jointures)
  3. Il existe plusieurs façons d’arriver à la même réponse en SQL – la « bonne » réponse dépendra fortement de la situation. Quelques éléments qui influenceront le résultat:
    1. Votre taille de données – une requête peut cesser d’être « ok » à mesure que votre taille de données augmente
    2. Index
    3. Votre vitesse de disque
    4. Taille de la mémoire
    5. Vitesse du processeur
    6. À quelle fréquence vous prévoyez d’exécuter la requête
  4. Enfin, le temps passé à améliorer vos connaissances et vos compétences SQL sera très payant. Même si vous n’écrivez pas les requêtes les plus efficaces, elles sont généralement plus rapides que d’écrire beaucoup de code procédural. Au fur et à mesure que j’apprends de plus en plus de modèles SQL, plus je suis étonné du code que je peux remplacer par quelques lignes de SQL (et j’obtiens généralement un énorme coup de pouce aux performances).

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée.