Join Order Benchmark (JOB)

Join Order Benchmark (JOB) pone a prueba el optimizador de consultas con 113 consultas analíticas sobre un conjunto de datos real y altamente correlacionado (una instantánea de IMDb). Desde su introducción, el benchmark JOB se ha convertido en el estándar de facto para evaluar el rendimiento de los optimizadores de consultas de bases de datos relacionales, incluida la estimación de cardinalidad y la optimización del orden de los joins. A diferencia de los benchmarks sintéticos, que asumen datos uniformes e independientes, JOB utiliza datos reales con sesgos y correlaciones, lo que lo convierte en una prueba exigente para el orden de los joins y la estimación de cardinalidad.

El conjunto de datos contiene alrededor de 74 millones de filas distribuidas en 21 tablas y ocupa aproximadamente 1.15 GiB comprimidos en ClickHouse.

Las 113 consultas están organizadas en 33 familias (1–33). Las consultas dentro de una misma familia (a, b, c, …) comparten el mismo grafo de joins, pero difieren en sus predicados de selección.

Referencias

• How Good Are Query Optimizers, Really? (Leis et al., VLDB 2015)
• Repositorio Join Order Benchmark

El conjunto de datos JOB es una instantánea de IMDb con 21 tablas. Las definiciones de las tablas están disponibles en init_cloud.sql en el repositorio de ClickHouse.

Cada tabla usa el motor MergeTree, ordenado por su columna de clave primaria id, reflejando el esquema original de PostgreSQL, donde cada tabla declara id integer NOT NULL PRIMARY KEY. Las columnas anulables de PostgreSQL se asignan a tipos Nullable(...).

Cree las tablas:

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

Los datos provienen de la instantánea original de IMDb utilizado por JOB, distribuido como un archivo CSV por tabla (aka_name.csv, title.csv, …). Estos CSV usan la semántica de COPY de PostgreSQL con ESCAPE '\': una barra invertida solo escapa el carácter de comillas dentro de un campo entrecomillado, mientras que fuera de las comillas la barra invertida es un carácter literal. ClickHouse espera archivos CSV conformes con RFC 4180 (comillas duplicadas, sin escapes con barra invertida), por lo que primero hay que volver a codificar los archivos.

convert_csv.py realiza esa recodificación. Lee el CSV original desde stdin y escribe un CSV estándar en stdout, duplicando las comillas incrustadas y conservando los campos vacíos sin comillas (que ClickHouse asigna a NULL en las columnas Nullable).

Para crear las tablas a partir de los CSV originales:

• Cree las tablas (consulte más arriba).
• Descargue el conjunto de datos de IMDb como un archivo imdb.tgz, siguiendo las instrucciones del repositorio Join Order Benchmark.
• Convierta e importe los datos:

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

Una vez pobladas las tablas, pueden exportarse a Parquet para volver a importarlas más rápidamente más adelante; p. ej. clickhouse client --database job --query "SELECT * FROM title ORDER BY id FORMAT Parquet" > title.parquet.

Tamaños detallados de las tablas:

(Los tamaños comprimidos en ClickHouse se obtienen de system.tables.total_bytes y se basan en las definiciones de tabla anteriores.)

Las 113 consultas de JOB se pueden encontrar aquí en el repositorio de ClickHouse. Los ajustes utilizados para ejecutarlas están en settings.json. Consulta el README para ver los problemas conocidos y las notas sobre consultas concretas.

Las consultas hacen referencia a las tablas por su nombre, así que ejecútalas en la base de datos job (por ejemplo, con clickhouse client --database job).

Una consulta de ejemplo (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 realiza un seguimiento del rendimiento de las consultas JOB en todas las versiones publicadas. Puede consultar los tiempos de ejecución de las 113 consultas JOB en la página de benchmark de versiones de ClickHouse para ver cómo ha evolucionado el rendimiento a lo largo del tiempo.