Оптимизация SQL-запроса с несколькими объединениями и группировкой (Postgres 9.3)

Question

Я просмотрел некоторые другие сообщения и сумел ускорить выполнение моих запросов. Тем не менее, я не понял, как оптимизировать этот запрос. Я собираюсь использовать его на веб-сайте, где он будет выполнять запрос при загрузке страницы, но 5,5 секунды - это слишком долго, чтобы ждать чего-то, что должно быть намного проще. Самая большая таблица имеет около 4000 000 строк, а остальные - около 400 000 каждая.

Структура таблицы

матч

id BIGINT PRIMARY KEY,
region TEXT,
matchType TEXT,
matchVersion TEXT

команда

matchid BIGINT REFERENCES match(id),
id INTEGER,
PRIMARY KEY(matchid, id),
winner TEXT

чемпион

id INTEGER PRIMARY KEY,
version TEXT,
name TEXT

вещь

id INTEGER PRIMARY KEY,
name TEXT

участник

PRIMARY KEY(matchid, id),
id INTEGER NOT NULL,
matchid BIGINT REFERENCES match(id),
championid INTEGER REFERENCES champion(id),
teamid INTEGER,
FOREIGN KEY (matchid, teamid) REFERENCES team(matchid, id),
magicDamageDealtToChampions REAL,
damageDealtToChampions REAL,
item0 TEXT,
item1 TEXT,
item2 TEXT,
item3 TEXT,
item4 TEXT,
item5 TEXT,
highestAchievedSeasonTier TEXT

запрос

select champion.name,
sum(case when participant.item0 = '3285' then 1::int8 else 0::int8 end) as it0,
sum(case when participant.item1 = '3285' then 1::int8 else 0::int8 end) as it1,
sum(case when participant.item2 = '3285' then 1::int8 else 0::int8 end) as it2,
sum(case when participant.item3 = '3285' then 1::int8 else 0::int8 end) as it3,
sum(case when participant.item4 = '3285' then 1::int8 else 0::int8 end) as it4,
sum(case when participant.item5 = '3285' then 1::int8 else 0::int8 end) as it5
from participant
left join champion
on champion.id = participant.championid
left join team
on team.matchid = participant.matchid and team.id = participant.teamid
left join match
on match.id = participant.matchid
where (team.winner = 'True' and matchversion = '5.14'  and matchtype='RANKED_SOLO_5x5')
group by champion.name;

Выход из EXPLAIN ANALYZE: http://explain.depesz.com/s/ZYX

Что я сделал до сих пор

Я создал отдельные индексы на match.region, participant.championidи частичный индекс по команде where winner = 'True' (так как это только то, что меня интересует). Обратите внимание, что enable_seqscan = on так как когда он выключен, запрос очень медленный. По сути, результат, который я пытаюсь получить, выглядит примерно так:

Champion   |item0 | item1 | ... | item5
champ_name | num  |  num1 | ... | num5
...

Так как я все еще новичок в области проектирования баз данных, я не удивлюсь, если в моей общей структуре таблиц есть изъян. Я все еще склоняюсь к тому, что запрос абсолютно неэффективен. Я играл как с внутренними, так и с левыми соединениями - разницы нет. Кроме того, матч должен быть bigint (или что-то большее, чем integer, так как он слишком маленький).

Answer 1

Дизайн базы данных

Я предлагаю:

CREATE TABLE matchversion (
  matchversion_id int PRIMARY KEY
, matchversion    text UNIQUE NOT NULL
);

CREATE TABLE matchtype (
  matchtype_id int PRIMARY KEY
, matchtype    text UNIQUE NOT NULL
);

CREATE TABLE region (
  region_id int PRIMARY KEY
, region    text NOT NULL
);

CREATE TABLE match (
  match_id        bigint PRIMARY KEY
, region_id       int REFERENCES region
, matchtype_id    int REFERENCES matchtype
, matchversion_id int REFERENCES matchversion
);

CREATE TABLE team (
  match_id bigint REFERENCES match
, team_id  integer  -- better name !
, winner   boolean  -- ?!
, PRIMARY KEY(match_id, team_id)
);

CREATE TABLE champion (
  champion_id int PRIMARY KEY
, version     text
, name        text
);

CREATE TABLE participant (
  participant_id serial PRIMARY KEY -- use proper name !
, champion_id    int NOT NULL REFERENCES champion
, match_id       bigint NOT NULL REFERENCES match -- this FK might be redundant
, team_id        int
, magic_damage_dealt_to_champions real
, damage_dealt_to_champions       real
, item0      text  -- or integer ??
, item1      text
, item2      text
, item3      text
, item4      text
, item5      text
, highest_achieved_season_tier text  -- integer ??
, FOREIGN KEY (match_id, team_id) REFERENCES team
);

• Больше нормализации, чтобы получить меньшие таблицы и индексы и более быстрый доступ. Создать таблицы для поиска matchversion, matchtype а также region и только написать маленькое целое число в match,

• Похоже на столбцы participant.item0.. item5 а также highestAchievedSeasonTier может быть integer, но определяются как text?

• Колонка team.winner кажется boolean, но определяется как text,

• Я также изменил порядок столбцов, чтобы быть более эффективным. Подробности:

  • Расчет и экономия места в PostgreSQL

запрос

Опираясь на вышеупомянутые модификации и для Postgres 9.3:

SELECT c.name, *
FROM  (
   SELECT p.champion_id
        , count(p.item0 = '3285' OR NULL) AS it0
        , count(p.item1 = '3285' OR NULL) AS it1
        , count(p.item2 = '3285' OR NULL) AS it2
        , count(p.item3 = '3285' OR NULL) AS it3
        , count(p.item4 = '3285' OR NULL) AS it4
        , count(p.item5 = '3285' OR NULL) AS it5
   FROM   matchversion   mv  
   CROSS  JOIN matchtype mt
   JOIN   match          m  USING (matchtype_id, matchversion_id)
   JOIN   team           t  USING (match_id)
   JOIN   participant    p  USING (match_id, team_id)
   WHERE  mv.matchversion = '5.14'
   AND    mt.matchtype = 'RANKED_SOLO_5x5'
   AND    t.winner = 'True' -- should be boolean
   GROUP  BY p.champion_id
   ) p
JOIN  champion c USING (champion_id);  -- probably just JOIN ?

• поскольку champion.name не определено UNIQUE это, вероятно, неправильно GROUP BY Это. Это также неэффективно. использование participant.championid вместо этого (и присоединиться к champion позже, если вам нужно имя в результате).

• Все случаи LEFT JOIN бессмысленно, так как у вас все равно есть предикаты в левых таблицах и / или используется столбец в GROUP BY,

• Круглые скобки AND -ed WHERE условия не нужны.

• В Postgres 9.4 или новее вы можете использовать новый агрегат FILTER синтаксис вместо. Детали и альтернативы:

  • Как я могу упростить этот игровой статистический запрос?

Индекс

Частичный индекс на team вы уже должны выглядеть так, чтобы разрешить сканирование только по индексу:

CREATE INDEX on team (matchid, id) WHERE winner -- boolean

Но из того, что я вижу, вы можете просто добавить winner столбец к participant и уронить стол team полностью (если это не так).

Кроме того, этот индекс не сильно поможет, потому что (согласно вашему плану запросов) таблица имеет 800 тыс. Строк, половина из которых соответствует требованиям:

rows=399999 ... Filter: (winner = 'True'::text) ... Rows Removed by Filter: 399999

Этот индекс на match поможет немного больше (позже), когда у вас будет больше разных типов соответствия и версий:

CREATE INDEX on match (matchtype_id, matchversion_id, match_id);

Тем не менее, хотя 100 тыс. Строк соответствуют 400 тыс., Индекс полезен только для сканирования только по индексу. В противном случае последовательное сканирование будет быстрее. Индекс обычно платит за выбор 5 % таблицы или меньше.

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

В сторону

Убедитесь, что вы настроили основные параметры Postgres, такие как random_page_cost или же work_mem и т.п.

enable_seqscan = on Само собой разумеется. Это отключено только для отладки или локально в качестве крайней меры крайней меры.

Answer 2

Я бы попробовал использовать фильтр count(*) (где item0 = '3285') в качестве it0

по вашим подсчетам вместо сумм.

Кроме того, почему вы оставили присоединение к вашим последним 2 таблицам, а затем с оператором where. Это побеждает цель, и регулярное внутреннее соединение быстрее

select champion.name,
count(*) filter( where participant.item0 = 3285) as it0,
count(*) filter( where participant.item1 = 3285) as it1,
count(*) filter( where participant.item2 = 3285) as it2,
count(*) filter( where participant.item3 = 3285) as it3,
count(*) filter( where participant.item4 = 3285) as it4,
count(*) filter( where participant.item5 = 3285) as it5
from participant
join champion on champion.id = participant.championid
join team on team.matchid = participant.matchid and team.id = participant.teamid
join match on match.id = participant.matchid
where (team.winner = 'True' and matchversion = '5.14'  and matchtype='RANKED_SOLO_5x5')
group by champion.name;