Установка Join Order Benchmark

Очень иногда у меня возникает желание что-нибудь в PostgreSQL потестировать (оптимизатор например). Путем разговоров выяснил что можно для этого использовать (ну в том числе конечно) Join order benchmark, это который на данных про фильмы (imdb).

В целом, установить этот бенчмарк очень просто. Но я делаю это редко, поэтому некоторые детали забываются, приходится их мучительно вспоминать. Так что сейчас расскажу как установить join order benchmark, а в следующий раз — как его запустить.

Для начала — ресурсы:

• https://github.com/gregrahn/join-order-benchmark — github с запросами (113 запрсов + создание схемы + индексов)
  • https://github.com/gregrahn/join-order-benchmark/blob/master/schema.sql — схема
  • https://github.com/gregrahn/join-order-benchmark/blob/master/fkindexes.sql — индексы
• http://homepages.cwi.nl/~boncz/job/imdb.tgz — архив с данными (набор csv)
  • Архив весит 1.3 Гб
  • После распаковки 3.9 Гб
  • В базе 8576 Мб

Клонируем репозитарий JOB, запускаем psql, там создаем базу данных, например imdb и подключаемся к ней:

CREATE DATABASE imdb;
\c imdb

Далее нужно накатить объекты. Можно это сделать не выходя из psql — запустив sql-файл из директории с job:

\i '~/join-order-benchmark/schema.sql'

CREATE TABLE
...
CREATE TABLE

Далее нужно импортировать csv в базу. Когда-то давно я подготовил вот такой скриптик, с экранированием бекслэша, так что рекомендую вот эти команды:

\copy aka_name from 'aka_name.csv'  WITH (FORMAT csv, ESCAPE '\');
\copy aka_title from 'aka_title.csv'  WITH (FORMAT csv, ESCAPE '\');
\copy cast_info from 'cast_info.csv'  WITH (FORMAT csv, ESCAPE '\');
\copy char_name from 'char_name.csv'  WITH (FORMAT csv, ESCAPE '\');
\copy comp_cast_type from 'comp_cast_type.csv'  WITH (FORMAT csv, ESCAPE '\');
\copy company_name from 'company_name.csv'  WITH (FORMAT csv, ESCAPE '\');
\copy company_type from 'company_type.csv'  WITH (FORMAT csv, ESCAPE '\');
\copy complete_cast from 'complete_cast.csv'  WITH (FORMAT csv, ESCAPE '\');
\copy info_type from 'info_type.csv'  WITH (FORMAT csv, ESCAPE '\');
\copy keyword from 'keyword.csv'  WITH (FORMAT csv, ESCAPE '\');
\copy kind_type from 'kind_type.csv'  WITH (FORMAT csv, ESCAPE '\');
\copy link_type from 'link_type.csv'  WITH (FORMAT csv, ESCAPE '\');
\copy movie_companies from 'movie_companies.csv'  WITH (FORMAT csv, ESCAPE '\');
\copy movie_info from 'movie_info.csv'  WITH (FORMAT csv, ESCAPE '\');
\copy movie_info_idx from 'movie_info_idx.csv'  WITH (FORMAT csv, ESCAPE '\');
\copy movie_keyword from 'movie_keyword.csv'  WITH (FORMAT csv, ESCAPE '\');
\copy movie_link from 'movie_link.csv'  WITH (FORMAT csv, ESCAPE '\');
\copy name from 'name.csv'  WITH (FORMAT csv, ESCAPE '\');
\copy person_info from 'person_info.csv'  WITH (FORMAT csv, ESCAPE '\');
\copy role_type from 'role_type.csv'  WITH (FORMAT csv, ESCAPE '\');
\copy title from 'title.csv'  WITH (FORMAT csv, ESCAPE '\');

Сохраняете их в файл, например — в importCsv.sql, сам файл в рабочую директорию откуда запускали psql, и аналогичным образом запускаете его (процесс длительный, минут десять вроде у меня занял в прошлый раз):

\i importCsv.sql 
COPY 901343
COPY 361472
COPY 36244344
COPY 3140339
COPY 4
COPY 234997
COPY 4
COPY 135086
COPY 113
COPY 134170
COPY 7
COPY 18
COPY 2609129
COPY 14835720
COPY 1380035
COPY 4523930
COPY 29997
COPY 4167491
COPY 2963664
COPY 12
COPY 2528312

Посмотрим что у нас получилось в базе imdb:

\d+
                                         List of relations
 Schema |      Name       | Type  | Owner | Persistence | Access method |    Size    | Description 
--------+-----------------+-------+-------+-------------+---------------+------------+-------------
 public | aka_name        | table | pavel | permanent   | heap          | 89 MB      | 
 public | aka_title       | table | pavel | permanent   | heap          | 48 MB      | 
 public | cast_info       | table | pavel | permanent   | heap          | 1975 MB    | 
 public | char_name       | table | pavel | permanent   | heap          | 285 MB     | 
 public | comp_cast_type  | table | pavel | permanent   | heap          | 8192 bytes | 
 public | company_name    | table | pavel | permanent   | heap          | 23 MB      | 
 public | company_type    | table | pavel | permanent   | heap          | 8192 bytes | 
 public | complete_cast   | table | pavel | permanent   | heap          | 5880 kB    | 
 public | info_type       | table | pavel | permanent   | heap          | 8192 bytes | 
 public | keyword         | table | pavel | permanent   | heap          | 7632 kB    | 
 public | kind_type       | table | pavel | permanent   | heap          | 8192 bytes | 
 public | link_type       | table | pavel | permanent   | heap          | 8192 bytes | 
 public | movie_companies | table | pavel | permanent   | heap          | 147 MB     | 
 public | movie_info      | table | pavel | permanent   | heap          | 1269 MB    | 
 public | movie_info_idx  | table | pavel | permanent   | heap          | 62 MB      | 
 public | movie_keyword   | table | pavel | permanent   | heap          | 191 MB     | 
 public | movie_link      | table | pavel | permanent   | heap          | 1336 kB    | 
 public | name            | table | pavel | permanent   | heap          | 435 MB     | 
 public | person_info     | table | pavel | permanent   | heap          | 436 MB     | 
 public | role_type       | table | pavel | permanent   | heap          | 8192 bytes | 
 public | title           | table | pavel | permanent   | heap          | 281 MB     | 
(21 rows)

Осталось еще индексы накатить, скрипт их создания запускаем из репозитария JOB:

\i '~/join-order-benchmark/fkindexes.sql'
CREATE INDEX
...
CREATE INDEX

Всё готово, можно запускать бенчмарк.

Еще добавлю схему таблиц join order benchmark: