Запуск Join Order Benchmark

Подразумеваю что всё из статьи «Установка Join Order Benchmark» вы уже выполнили. Значит, у нас в наличии есть:

• База данных imdb
• 21 заполненная данными таблица в ней
• Директория со 113-ю запросами по адресу ~/join-order-benchmark

Давайте попробуем прогнать все эти запросы и получить, например, время их планирования и выполнения. Будем пробовать такой запуск Join Order Benchmark делать автоматизировано. Я буду использовать bash.

Нижеприведенный способ наиболее удобен мне. У вас может быть любой другой способ запуска бенчмарка.

Время планирования и выполнения можно взять при выполнении команды EXPLAIN (ANALYZE). Напомню вывод:

EXPLAIN ANALYZE SELECT 1;
                                     QUERY PLAN                                     
------------------------------------------------------------------------------------
 Result  (cost=0.00..0.01 rows=1 width=4) (actual time=0.005..0.006 rows=1 loops=1)
 Planning Time: 0.082 ms
 Execution Time: 0.036 ms
(3 rows)

EXPLAIN с опцией ANALYZE реально выполняет запрос и выдает его актуальную информацию. Так что из результата я заберу строки Planning иExecution Time.

Но сначала нужно каждому запросу в их начало дописать строчку EXPLAIN ANALYZE, и тоже можно это сделать скриптом. Например, таким:

#!/bin/bash

for var in $(ls ~/join-order-benchmark)
  do
      sed -i -e '1 s/^/EXPLAIN ANALYZE\n/;' ~/join-order-benchmark/$var
  done

Открыв любой запрос из той директории, у вас должно получиться нечто подобное (2a.sql):

EXPLAIN ANALYZE
SELECT MIN(t.title) AS movie_title
FROM company_name AS cn,
     keyword AS k,
     movie_companies AS mc,
     movie_keyword AS mk,
     title AS t
WHERE cn.country_code ='[de]'
  AND k.keyword ='character-name-in-title'
  AND cn.id = mc.company_id
  AND mc.movie_id = t.id
  AND t.id = mk.movie_id
  AND mk.keyword_id = k.id
  AND mc.movie_id = mk.movie_id;

Подготовительные работы выполнены. Теперь дело за малым — нужно выполнить эти запросы. Для этого я написал небольшой скрипт:

#!/bin/bash
#название директории где лежат запросы (для удобства, можно другой каталог с запросами подставить)
job_dir="join-order-benchmark"

#Очистка прошлых результатов
rm -rf results/*
rm -rf PlanTime
rm -rf ExecTime

#выполняю запросы из списка list
for var in $(ls ~/$job_dir | grep '^[1-9]')
  do
	psql -d imdb -f ~/$job_dir/$var -o results/$var
  done

#Вырезаю нужные строки, сортирую по названию файла, итоговое время сохраняю в файлы
grep -r "Planning Time:" results/ | sed 's|.*/||' | sort -n | awk '{print $4}' > PlanTime
grep -r "Execution Time:" results/ | sed 's|.*/||' | sort -n | awk '{print $4}' > ExecTime

В том месте, где вы сохраните этот скрипт нужно создать директорию results. В ней будут сохраняться планы получившихся запросов (можно их по отдельности открыть, проверить результаты). Директория со скриптами должна находится по адресу домашняя_папка_пользователя/join-order-benchmark. В скрипте можно поменять адрес директории с запросами, но отсчет идёт от домашней директории.

Скрипт состоит из нескольких частей. Сначала идёт очистка прошлых результатов (если они были), это команда rm -rf.

Далее в цикле я беру все запросы из директории с запросами и выполняю их — последовательно отправляю в psql, результат (вывод команды EXPLAIN ANALYZE) сохраняю в results по названием «выполненный_сценарий.sql».

В последних двух строках я вырезаю время планирования и выполнения из результата; убираю всё лишнее, что стоит до начала номера файла-запроса; сортирую по названию; беру четвертое слово в получившейся строке — это и есть нужное время; сохраняю его в файл результата в том месте, где находится скрипт. Да, по умолчанию, ls выводит в порядке сортировки по возрастанию. Но я добавил команду сортировки для бо́льшей уверенности (ну и чтобы потренироваться).

Оформил эти скрипты в репозитарий на github. Далее планирую его дорабатывать.

Запускаем скрипт, но учтите, что на моём не слабом ноутбуке полная отработка всех запросов заняла около восьми минут. В результате ниже я заменил точки на запятые для удобства дальнейшей обработки.

Что делать с полученным результатом? Можно, например, вывести вот такой график (использовал Google таблицу для этого):

Синий цвет — это время планирования, красный — выполнения. Размерности разные! Не перепутайте.