Join Order Benchmark (JOB)

Le Join Order Benchmark (JOB) met à rude épreuve l’optimiseur de requêtes avec 113 requêtes analytiques sur un jeu de données réel fortement corrélé (un instantané d’IMDb). Depuis son introduction, le benchmark JOB est devenu la norme de facto pour évaluer les performances des optimiseurs de requêtes des SGBD relationnels, notamment pour l’estimation de cardinalité et l’optimisation de l’ordre des jointures. Contrairement aux benchmarks synthétiques qui supposent des données uniformes et indépendantes, JOB utilise des données réelles avec une distribution asymétrique et des corrélations, ce qui en fait un test exigeant pour l’ordre des jointures et l’estimation de cardinalité.

Le jeu de données contient environ 74 millions de lignes réparties sur 21 tables et occupe environ 1,15 GiB une fois compressé dans ClickHouse.

Les 113 requêtes sont organisées en 33 familles (1–33). Les requêtes d’une même famille (a, b, c, …) partagent le même graphe de jointure, mais diffèrent par leurs prédicats de sélection.

Références

• How Good Are Query Optimizers, Really? (Leis et al., VLDB 2015)
• Dépôt Join Order Benchmark

Le jeu de données JOB est un instantané d’IMDb comprenant 21 tables. Les définitions des tables sont disponibles dans init_cloud.sql du dépôt ClickHouse.

Chaque table utilise le moteur MergeTree, avec un tri sur sa colonne de clé primaire id, en miroir du schéma PostgreSQL d’origine dans lequel chaque table déclare id integer NOT NULL PRIMARY KEY. Les colonnes PostgreSQL pouvant contenir des valeurs NULL sont mappées sur des types Nullable(...).

Créez les tables :

curl -O https://raw.githubusercontent.com/ClickHouse/ClickHouse/master/tests/benchmarks/job/init_cloud.sql
clickhouse client --query "CREATE DATABASE IF NOT EXISTS job"
clickhouse client --database job --queries-file init_cloud.sql

Les données proviennent de l’instantané IMDb d’origine utilisé par JOB, distribué sous la forme d’un fichier CSV par table (aka_name.csv, title.csv, …). Ces fichiers CSV utilisent la sémantique COPY de PostgreSQL avec ESCAPE '\' : un backslash n’échappe le caractère de guillemet qu’à l’intérieur d’un champ entre guillemets, tandis qu’en dehors des guillemets, un backslash est un caractère littéral. ClickHouse attend des CSV conformes à la RFC 4180 (guillemets doublés, sans échappement par backslash), les fichiers doivent donc d’abord être réencodés.

convert_csv.py effectue ce réencodage. Il lit le CSV d’origine sur stdin et écrit un CSV standard sur stdout, en doublant les guillemets internes et en conservant les champs vides non entre guillemets (que ClickHouse associe à NULL pour les colonnes Nullable).

Pour créer les tables à partir des CSV d’origine :

• Créez les tables (voir ci-dessus).
• Téléchargez le jeu de données IMDb sous la forme d’un fichier imdb.tgz, en suivant les instructions du dépôt Join Order Benchmark.
• Convertissez et importez les données :

set -euo pipefail

for table in aka_name aka_title cast_info char_name comp_cast_type company_name \
             company_type complete_cast info_type keyword kind_type link_type \
             movie_companies movie_info movie_info_idx movie_keyword movie_link \
             name person_info role_type title; do
    echo "Loading ${table} ..."
    python3 convert_csv.py < "${table}.csv" > "${table}.clean.csv"
    clickhouse client --database job --query "INSERT INTO ${table} FORMAT CSV" < "${table}.clean.csv"
done

Une fois les tables alimentées, elles peuvent être exportées au format Parquet pour être réimportées plus rapidement par la suite, par exemple. clickhouse client --database job --query "SELECT * FROM title ORDER BY id FORMAT Parquet" > title.parquet.

Tailles détaillées des tables :

(Les tailles compressées dans ClickHouse proviennent de system.tables.total_bytes et sont basées sur les définitions de table ci-dessus.)

Les 113 requêtes JOB sont disponibles ici dans le dépôt ClickHouse. Les paramètres utilisés pour les exécuter se trouvent dans settings.json. Consultez le README pour connaître les problèmes connus et les remarques concernant certaines requêtes.

Les requêtes font référence aux tables par leur nom ; exécutez-les donc sur la base de données job (par exemple avec clickhouse client --database job).

Exemple de requête (1a) :

SELECT MIN(mc.note) AS production_note,
       MIN(t.title) AS movie_title,
       MIN(t.production_year) AS movie_year
FROM company_type AS ct,
     info_type AS it,
     movie_companies AS mc,
     movie_info_idx AS mi_idx,
     title AS t
WHERE ct.kind = 'production companies'
  AND it.info = 'top 250 rank'
  AND mc.note NOT LIKE '%(as Metro-Goldwyn-Mayer Pictures)%'
  AND (mc.note LIKE '%(co-production)%'
       OR mc.note LIKE '%(presents)%')
  AND ct.id = mc.company_type_id
  AND t.id = mc.movie_id
  AND t.id = mi_idx.movie_id
  AND mc.movie_id = mi_idx.movie_id
  AND it.id = mi_idx.info_type_id;

ClickHouse suit les performances des requêtes JOB pour chaque version publiée. Consultez les temps d’exécution des 113 requêtes JOB sur la page benchmark des versions de ClickHouse pour voir l’évolution des performances au fil du temps.