Лучшие индексы для ГРУППОВОГО запроса и запроса равенства в объединенных таблицах

#sql #postgresql #indexing #group-by

#sql #postgresql #индексирование #группирование по

Вопрос:

У меня есть этот повторяющийся SQL-запрос в postgres:

 SELECT b.name, COUNT(*)
FROM a JOIN b ON a.x = b.x
WHERE a.value = some_value
GROUP BY b.name;

(Подсчитайте вхождения.value для каждого b.name )

Какие индексы лучше всего подходят для повышения эффективности этого запроса? Я предполагаю, что для значения.value помогает хэш-индекс. Но как насчет group by? Существует ли какое-либо решение для составного индекса? Или другое решение?

1. Вы действительно хотите присоединиться здесь? Если в A есть несколько строк, которые соответствуют (скрытому) условию соединения, строки в B будут подсчитаны несколько раз.

2. Это postgres, спасибо, я отредактировал сообщение. Что касается объединения, да, суть в том, чтобы подсчитать несколько случаев совпадения. Естественное соединение просто заменяет связь первичный ключ — внешний ключ.

3. » Естественное соединение просто заменяет связь первичный ключ — внешний ключ » — нет, это не так. Он объединяет все столбцы с одинаковыми именами , не основанные на объявленных внешних ключах.

4. Верно, у меня нет его как естественного соединения, я просто поместил его как таковой в этом сообщении, чтобы упростить запрос, но теперь я понимаю, что это также относится к вопросу, поэтому я отредактирую его.

5. Насколько выборочно a.value = some_value ? Пожалуйста, покажите EXPLAIN (ANALYZE) план запроса в том виде, в каком он выполняется в данный момент.

Ответ №1:

Никогда не используйте NATURAL JOIN . Неясно, что на самом деле делает ваш запрос. Легко совершать ошибки. И самое главное, так называемое «естественное» соединение не использует естественные отношения, явно объявленные с использованием отношений внешнего ключа.

Позвольте мне предположить, что ваш запрос выглядит следующим образом:

 SELECT b.name, COUNT(*)
FROM a JOIN
     b 
     ON b.x = a.x
WHERE a.value = some_value
GROUP BY b.name;

В большинстве баз данных вам понадобятся следующие индексы:

a(value, x)
b(x, name)

1. Правильно, я не использую естественное соединение, я на самом деле использую соединение, как вы упомянули, с a.x = b.x . Я написал это так, потому что считал, что это не будет иметь отношения к вопросу — моя вина. Зная, что x является первичным ключом в обеих таблицах, ваш ответ остается прежним? Кроме того, являются ли эти индексы b-деревом или хэшем? Как бы вы реализовали их в SQL? (извините, возможно, вы выразились ясно, но я не знаю обозначения индекса)

2. @DaltonicD . , , Индекс, удовлетворяющий where условиям, поможет, даже если оба являются первичными ключами. Вам не нужен второй индекс, если x он уже является первичным ключом. Тип индекса не имеет большого значения; значение по умолчанию работает нормально. Для вашего вопроса потребуется гораздо больше информации, чтобы провести различие между типами индексов (некоторые вопросы, которые приходят на ум, — каковы условия where? насколько велика таблица? сколько фильтрации? является name уникальным)