Join Order Benchmark (JOB)

O Join Order Benchmark (JOB) coloca o otimizador de consultas sob pressão com 113 consultas analíticas sobre um conjunto de dados real e altamente correlacionado (um snapshot do IMDb). Desde sua introdução, o benchmark JOB tornou-se o padrão de fato para avaliar o desempenho de otimizadores de consultas de bancos de dados relacionais, incluindo estimativa de cardinalidade e otimização da ordem de JOIN. Diferentemente de benchmarks sintéticos que assumem dados uniformes e independentes, o JOB usa dados reais com distribuição desigual e correlações, o que o torna um teste desafiador para a ordenação de JOINs e a estimativa de cardinalidade.

O conjunto de dados contém cerca de 74 milhões de linhas distribuídas em 21 tabelas e ocupa aproximadamente 1,15 GiB comprimido no ClickHouse.

As 113 consultas estão organizadas em 33 famílias (1–33). As consultas dentro de uma família (a, b, c, …) compartilham o mesmo grafo de JOIN, mas diferem nos predicados de seleção.

Referências

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

O conjunto de dados JOB é um snapshot do IMDb com 21 tabelas. As definições das tabelas estão disponíveis em init_cloud.sql no repositório do ClickHouse.

Cada tabela usa o motor MergeTree, ordenado pela coluna da chave primária id, seguindo o schema original do PostgreSQL, em que toda tabela declara id integer NOT NULL PRIMARY KEY. Colunas anuláveis do PostgreSQL são mapeadas para tipos Nullable(...).

Crie as tabelas:

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

Os dados vêm do snapshot original do IMDb usado pelo JOB, distribuído como um arquivo CSV por tabela (aka_name.csv, title.csv, …). Esses arquivos CSV usam a semântica de COPY do PostgreSQL com ESCAPE '\': uma barra invertida escapa o caractere de aspas apenas dentro de um campo entre aspas, enquanto, fora das aspas, a barra invertida é um caractere literal. O ClickHouse espera CSV no formato RFC 4180 (aspas duplicadas, sem escape com barra invertida), portanto os arquivos precisam ser recodificados primeiro.

convert_csv.py faz essa recodificação. Ele lê o CSV original em stdin e grava o CSV padrão em stdout, duplicando aspas internas e preservando campos vazios sem aspas (que o ClickHouse mapeia para NULL em colunas Nullable).

Para criar as tabelas a partir dos arquivos CSV originais:

• Crie as tabelas (veja acima).
• Baixe o conjunto de dados do IMDb como um arquivo imdb.tgz, seguindo as instruções no repositório Join Order Benchmark.
• Converta e importe os dados:

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

Depois que as tabelas estiverem preenchidas, elas poderão ser exportadas para Parquet para uma reimportação mais rápida posteriormente, por exemplo: clickhouse client --database job --query "SELECT * FROM title ORDER BY id FORMAT Parquet" > title.parquet.

Tamanhos detalhados das tabelas:

(Os tamanhos comprimidos no ClickHouse são obtidos de system.tables.total_bytes e se baseiam nas definições das tabelas acima.)

As 113 consultas JOB podem ser encontradas aqui, no repositório do ClickHouse. As configurações usadas para executá-las estão em settings.json. Consulte o README para ver problemas conhecidos e observações sobre consultas específicas.

As consultas fazem referência às tabelas pelo nome, portanto execute-as no banco de dados job (por exemplo, com clickhouse client --database job).

Uma consulta de exemplo (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;

O ClickHouse acompanha o desempenho das consultas JOB em todas as versões lançadas. Você pode consultar os tempos de execução das 113 consultas JOB na página de benchmark de versões do ClickHouse para ver como o desempenho evoluiu ao longo do tempo.