Почему BigQuery не оптимизирует LEFT JOIN и GROUP BY

У меня есть следующий запрос:

#standardSQL

WITH users AS (
  SELECT 1 id, 'pieter' name UNION ALL
  SELECT 2   , 'george'      UNION ALL
  SELECT 3   , 'maggie'
),
invoices AS (
  SELECT 1 id, 1 userId, 25.0 amount, 'taxi fee' description UNION ALL
  SELECT 2   , 1       , 300.0      , 'conference ticket'    UNION ALL
  SELECT 3   , 1       , 300.0      , 'hotel room'           UNION ALL
  SELECT 5   , 3       , 70         , 'airbnb'               UNION ALL
  SELECT 6   , 3       , 22         , 'concert ticket'
)
SELECT
  users.id userId,
  users.name userName
FROM 
  users
LEFT JOIN 
  invoices ON invoices.userId = users.id
GROUP BY
  users.id,
  users.name
ORDER BY
  users.id

В этом случае комбинация LEFT JOIN и GROUP BY может быть легко оптимизирована (опущена) обработчиком запросов. Однако, когда я смотрю в плане запроса, соединение по-прежнему выполняется BigQuery. Почему? И как избежать такого поведения?

Мой вариант использования: у меня есть представление, содержащее несколько полей, требующих таких объединений. Я не хочу, чтобы соединения выполнялись, когда поля не запрашиваются.

Я использую диалект standardSQL.


google-cloud-platform sql google-bigquery bigquery-standard-sql
person wardva    schedule 20.02.2018    source источник
comment
Оптимизаторы запросов обычно разрабатываются для решения сложных алгоритмических проблем, а не для переписывания плохо написанных запросов. И какая разница, какое соединение на самом деле имеет? Очевидно, что ваша реальная проблема связана с таблицами с более чем 5 строками, поэтому ваш реальный вопрос сильно отличается от того, что здесь задают.   -  person Gordon Linoff    schedule 20.02.2018
comment
Почему вы вообще используете группу без каких-либо агрегатов? Вы имеете в виду различное? В этом отношении, поскольку у вас нет используемых форм данных; зачем вообще присоединяться к счетам? слишком мало информации.   -  person xQbert    schedule 20.02.2018
comment
Конечно, это просто уменьшенный пример. В моей реальной проблеме я запрашиваю несколько полей из представления, содержащего соединения, которые не требуются для запроса.   -  person wardva    schedule 20.02.2018
comment
@вардва . . . Ваш вопрос об оптимизации просмотра — очень хороший момент, и его стоит обсудить с разработчиками BQ. Это не вызвало бы такой же реакции, как вопрос, который вы действительно задали.   -  person Gordon Linoff    schedule 20.02.2018
comment
Чтобы привлечь инженеров BQ, пожалуйста, опубликуйте идентификатор вакансии по вашему запросу.   -  person Pentium10    schedule 20.02.2018
comment
Думаю, Гордон попал в точку. оптимизаторы не созданы для исправления плохого кода. они предполагают, что кодеры будут присоединяться к таблице только тогда, когда из нее потребуются данные. Частично это может быть связано с порядком работы. оптимизатор не знает таблицу, и соединение не требуется до тех пор, пока все аспекты запроса не будут завершены к тому моменту, когда соединение уже выполнено... это интересный вопрос, действительно предназначенный для тех, кто кодирует механизм базы данных; кем я не являюсь. Целевой аудиторией для этого вопроса являются инженеры BQ; не общая пользовательская база SO.   -  person xQbert    schedule 21.02.2018
comment
JobId фактического запроса: spry-framework-483:US.bquijob_77ec7ccb_161b5092194   -  person wardva    schedule 21.02.2018
comment
Как избежать такого поведения? Не пишите запрос, соединяющий таблицу, в которой вам не нужны поля из объединенной таблицы. :)   -  person theMayer    schedule 21.02.2018
comment
@GordonLinoff, хотя это может быть правдой, это определенно не относится к компиляторам кода в целом, которыми, возможно, является оптимизатор запросов. Компиляторы для .NET, например, часто оптимизируют плохо написанный код.   -  person theMayer    schedule 21.02.2018
comment
@theMayer В 867-й раз: у меня есть представление с несколькими столбцами, содержащими значения, сгенерированные путем агрегирования объединенных значений. Я не хочу, чтобы соединения выполнялись, когда я не выбираю ни один из этих столбцов.   -  person wardva    schedule 22.02.2018
comment
@wardva - затем создайте другое представление с меньшим количеством столбцов. Кажется, что наличие большего количества столбцов, чем вы хотите, не позволяет использовать представление. Я не говорю, что вы не нашли ошибку, но много времени было потрачено на изучение этого, но без особой пользы.   -  person theMayer    schedule 26.02.2018

Ответы (1)


arrow_upward
3
arrow_downward

Позвольте мне уточнить мой комментарий.

Вероятно, вы не понимаете всех возможных способов выполнения запроса. Позвольте мне выйти за рамки BQ (о котором я немного знаю, но мне определенно не хватает глубоких знаний) и указать на некоторые алгоритмы, используемые для объединения и агрегирования:

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

Я понимаю, что подходы, основанные на индексах, неприменимы к BQ. Но это для того, чтобы дать вам вкус. Затем, после выбора конкретного алгоритма, могут остаться параметры и подметоды — какой алгоритм использовать? Насколько велика хеш-таблица? Как обрабатывать столкновения? У BQ есть свои параметры, которые необходимо установить, касающиеся параллелизма и перекоса.

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

  • Хочу ли я работать над исправлением плохо написанных запросов, где автор должен знать, как написать это лучше?
  • Хочу ли я выяснить лучшие алгоритмы и реализации для общих классов запросов, которые могут повлиять на всех пользователей?

РЕДАКТИРОВАТЬ:

В комментариях ОП указывает, что на самом деле он / она имеет в виду представление. Это совсем другой зверь, и в этом случае устранение соединений — очень разумная стратегия оптимизации.

person Gordon Linoff    schedule 20.02.2018