Таблица "многие ко многим" - производительность плохая

Question

Даны следующие таблицы:

--- player --
id serial
name VARCHAR(100)
birthday DATE
country VARCHAR(3)
PRIMARY KEY id

--- club ---
id SERIAL
name VARCHAR(100)
country VARCHAR(3)
PRIMARY KEY id

--- playersinclubs ---
id SERIAL
player_id INTEGER (with INDEX)
club_id INTEGER (with INDEX)
joined DATE
left DATE
PRIMARY KEY id

У каждого игрока есть в таблице ряд игрока (со своими атрибутами). В равной степени каждый клуб имеет запись в настольном клубе. Для каждой станции в его карьере у игрока есть запись в таблице PlayersInClubs (нм) с датой, когда игрок вступил в игру и, необязательно, когда игрок покинул клуб.

Моя главная проблема - производительность этих таблиц. В Table player у нас более 10 миллионов записей. Если я хочу отобразить историю клуба со всеми его игроками, сыгранными в этом клубе, мой выбор выглядит следующим образом:

SELECT * FROM player
 JOIN playersinclubs ON player.id = playersinclubs.player_id
 JOIN club ON club.id = playersinclubs.club_id
WHERE club.dbid = 3;

Но для массовой загрузки игроков будет выполнено сканирование последовательности на столе игрока. Этот выбор занимает много времени.

До того, как я реализовал некоторые новые функции в своем приложении, у каждого игрока есть ровно одна команда (только сегодняшние команды и игроки). Так что у меня не было настольных игроков в клубах. Вместо этого у меня был team_id в таблице player. Я мог выбрать игроков команды непосредственно в таблице player с предложением where team_id = 3.

Есть ли у кого-нибудь несколько советов по производительности для моей структуры базы данных, чтобы ускорить этот выбор?

Answer 1

Самое главное, вам нужен индекс на playersinclubs(club_id, player_id), Остальные детали (это все еще может иметь большое значение).
Вы должны быть точными в своих реальных целях. Ты пишешь:

все его игроки играли за этот клуб:

Вам не нужно присоединяться к club для этого вообще:

SELECT p.* 
FROM   playersinclubs pc
JOIN   player         p ON p.id = pc.player_id
WHERE  pc.club_id = 3;

И вам не нужны столбцы playersinclubs либо в выводе, что является небольшим приростом для производительности - если только он не разрешает сканирование только по индексу playersinclubs, тогда это может быть существенным.

• Как PostgreSQL выполняет ORDER BY, если на этом поле построен индекс b-дерева?

Вам, вероятно, не нужны все столбцы player в результате либо. Только SELECT столбцы, которые вам действительно нужны.

ПК на player предоставляет необходимый индекс для этой таблицы.

Вам нужен индекс на playersinclubs(club_id, player_id), но не делайте его уникальным, если игрокам не разрешено вступать в один и тот же клуб во второй раз.

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

SELECT DISTINCT p.* ...

Но так как вы пытаетесь оптимизировать производительность: дешевле устранить ошибки на ранней стадии:

SELECT p.*
FROM  (
   SELECT DISTINCT player_id
   FROM   playersinclubs
   WHERE  club_id = 3;
   ) pc
JOIN   player p ON p.id = pc.player_id;

Может быть, вы действительно хотите, чтобы все записи в playersinclubs и все столбцы таблицы тоже. Но ваше описание говорит об обратном. Запрос и индексы были бы другими.

Тесно связанный ответ:

• Найти перекрывающиеся диапазоны дат в PostgreSQL

Answer 2

Таблицы выглядят хорошо, как и запрос. Итак, давайте посмотрим, что должен делать запрос:

1. Выберите клуб с идентификатором 3. Одна запись, к которой можно получить доступ через индекс PK.
2. Выберите все записи игроков в клубе для идентификатора клуба 3. Итак, нам нужен индекс, начинающийся с этой колонки. Если у вас его нет, создайте его.

Я предлагаю:

create unique index idx_playersinclubs on playersinclubs(club_id, player_id, joined);

Это будет уникальный бизнес-ключ таблицы. Я знаю, что во многих базах данных с техническими идентификаторами эти уникальные ограничения не установлены, но я считаю это недостатком в этих базах данных и всегда создавал бы эти ограничения / индексы.

3. Используйте идентификаторы игроков, полученные таким образом, и выберите игроков соответственно. Мы можем получить идентификатор игрока из записей игроков в клубах, но это также второй столбец в нашем индексе, поэтому СУБД может выбрать одну или другую для выполнения объединения. (Вероятно, он будет использовать столбец из индекса.)

Так что, может быть, просто вышеупомянутый индекс еще не существует.