Могу ли я попросить SQL использовать индекс (с похожими на строки) в условии левого соединения, если индекс успешно используется в условии где?

У меня есть структуры таблиц:

journal_entries (id integer, account varchar(20), doc_date, date, amount numeric(15,4))
selected_accounts (account varchar(20), selection_id integer)  

Я могу запросить это, и SQL использовал индексы как для doc_date, так и для учетной записи:

select je.*
  from journal_entries je
  where je.doc_date>='01.01.2022' and
        je.doc_date<='31.03.2022' and
        (je.account like '23%' or 
         je.account like '24%')

Но когда я заполняю таблицу selected_accounts данными:

23%, 1
24%, 1

И я пытаюсь использовать условие в левом соединении:

select
  from selected_accounts sa
    left join journal_entries je on (
      je.doc_date>='01.01.2022' and
      je.doc_date<='31.03.2022' and
      je.account like sa.account)

Тогда SQL не использует индекс для данных journal_entries.account, он использует индекс только для je.doc_date.

Могу ли я подсказать оптимизатору или механизму SQL, что условие je.account like sa.account должно использовать индекс для je.account?

Я использую Firebird 3.1 и Firebird 2.1, но я думаю, что эта проблема есть и в других базах данных SQL.

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

Вопрос дополнен: Я скопировал план (например, предоставленный IBExpert) из первого запроса как предложение plan во второй запрос, но механизм SQL сообщает:

 index <index on journal_entries.account> cannot be used in the specified plan

Итак, в моем запросе есть что-то, что препятствует ссылке и использованию индекса journal_entries.account.

Дополнительное наблюдение: На самом деле - в моей базе данных есть 1 млн записей журнала в 2022Q1 (период, указанный в моих примерах), тогда первый (хороший) запрос сообщает о чтении менее 1 млн индексированных записей, но второй (плохой) запрос сообщает о 2 * 1 млн прочтений индексированных записей ( индексируется из-за индекса на journal_entries.doc_date), так что это даже хуже, чем полное чтение по doc_date, а затем просто фильтрация по selected_records записям.

Один шаг вперед: Благодаря комментарию @Damien_The_Unbeliever я сделал этот тест (sic! Первая строка с префиксом %):

select je.*
  from journal_entries je
  where je.doc_date>='01.01.2022' and
        je.doc_date<='31.03.2022' and
        (je.account like '%23%' or 
         je.account like '24%')

И больше не используется индекс je.account и количество прочтений увеличилось. Итак, мне кажется, что движок/оптимизатор запросов Firebird сканирует те строковые литералы, которые используются в условиях like, и принимает решение о возможности использования индекса для je.account.

Итак, может быть, я могу уведомить Firebird (для моего второго/медленного запроса), что я ожидаю только строки с пост-%-fixed как select_accounts.account output? Это решило бы мою проблему в случае с движком Firebird.

Разница не в join против where. Дело в том, что в первую очередь он может оптимизировать запрос, зная, что оба выражения like соответствуют префикс. Во втором запросе нужно учесть шаблон Любые, который может появиться в таблице.

Damien_The_Unbeliever 17.05.2022 10:21

Firebird 3.1 не существует, и если вы используете Firebird 3.0.1, вам действительно следует обновиться до 3.0.9.

Mark Rotteveel 17.05.2022 12:13
Формы c голосовым вводом в React с помощью Speechly
Формы c голосовым вводом в React с помощью Speechly
Пытались ли вы когда-нибудь заполнить веб-форму в области электронной коммерции, которая требует много кликов и выбора? Вас попросят заполнить дату,...
Стилизация и валидация html-формы без использования JavaScript (только HTML/CSS)
Стилизация и валидация html-формы без использования JavaScript (только HTML/CSS)
Будучи разработчиком веб-приложений, легко впасть в заблуждение, считая, что приложение без JavaScript не имеет права на жизнь. Нам становится удобно...
Flatpickr: простой модуль календаря для вашего приложения на React
Flatpickr: простой модуль календаря для вашего приложения на React
Если вы ищете пакет для быстрой интеграции календаря с выбором даты в ваше приложения, то библиотека Flatpickr отлично справится с этой задачей....
В чем разница между Promise и Observable?
В чем разница между Promise и Observable?
Разберитесь в этом вопросе, и вы значительно повысите уровень своей компетенции.
Что такое cURL в PHP? Встроенные функции и пример GET запроса
Что такое cURL в PHP? Встроенные функции и пример GET запроса
Клиент для URL-адресов, cURL, позволяет взаимодействовать с множеством различных серверов по множеству различных протоколов с синтаксисом URL.
Четыре эффективных способа центрирования блочных элементов в CSS
Четыре эффективных способа центрирования блочных элементов в CSS
У каждого из нас бывали случаи, когда нам нужно отцентрировать блочный элемент, но мы не знаем, как это сделать. Даже если мы реализуем какой-то...
1
2
42
1
Перейти к ответу Данный вопрос помечен как решенный

Ответы 1

Ответ принят как подходящий

В этом случае оптимизация like невозможна, потому что Firebird не может знать, какие значения имеет ваш столбец. Тот факт, что somecolumn like '24%' может использовать индекс, заключается в том, что Firebird будет переписать этого выражения для somecolumn starting with '24' (см. также LIKE, в частности примечание под названием «О LIKE и Оптимизаторе»). Это невозможно сделать с параметрами или значениями, полученными из столбцов.

Другими словами, очевидное решение вашей проблемы состоит в том, чтобы заполнить selected_accounts.account не '24%', а '24', и использовать STARTING WITH в вашем условии соединения.

В случае, если подстановочный знак не всегда встречается и иногда вам нужно точное совпадение, вы можете использовать что-то вроде je.account starting with replace(sa.account, '%', '') and je.account like sa.account. Это решение предполагает, что % всегда встречается только как последний символ, и подстановочный знак _ не используется.

Другие вопросы по теме