SQL запрос по 2-ум опциональным параметрам

Question

Есть таблица, например book, и вам нужно получить список книг по двум фильтрам: название и автор. Индексы были созданы для обоих этих полей отдельно. Может быть передан любой из этих параметров, либо один, либо оба сразу, но должен быть передан хотя бы один (оба значения null недопустимы). В сервисе есть проверка наличия хотя бы 1 параметра. У нас есть спор о создании одного универсального запроса с помощью OR, такого как:

SELECT *
FROM book
WHERE (:title IS NULL OR title = :title)
AND (:author IS NULL OR author = :author);

Или же на уровне сервиса

if (title != null) {
   вызываем запрос поиска по title
}
else{
вызываем запрос поиска по author
}

Или есть более корректный эффективный и правильный вариант? Теоретически, в первом случае будет сканирование Bitmap Scan, и чем это хуже? База данных - Postgre. Для каждого поля уже есть 2 отдельных индекса. На уровне базы данных для каждого из этих полей нет нулевых ограничений. Перед запросом проверяется наличие хотя бы 1 из этих 2-х полей. Уже есть репозиторий, где запросы пишутся с использованием native SQL. Но с этим запросом у нас возникли споры о том, как это было бы правильнее и эффективнее сделать.

Answer 1

А как вы во втором варианте будете две выборки пересекать? Если переданы оба параметра? Уже в коде приложения, не в БД? Второй вариант совсем не очень, мягко выражаясь...

Но и в первом варианте писать OR необязательно! Можно и индексы оставить и всё в рамках одного запроса:

SELECT B.Id, F.name, F.Data
--фактически строки выдаст только один из трёх запросов, т.ч.
--хоть в плане и будет UNION, по факту он даже выполняться не будет.
FROM(
  -- передамо название
  SELECT id FROM book WHERE tittle = :tittle AND :author IS NULL 
  UNION ALL
  -- передан автор
  SELECT id FROM book WHERE author = :author AND :tittle IS NULL
  UNION ALL
  -- переданы оба
  SELECT id FROM book WHERE tittle = :tittle AND author = :author
)B
  --что-то вроде такого
  JOIN Files F ON B.Id = F.book_id

Если нужны какие-то столбцы из book, можно его отдельно прилепить, это всего лишь ещё один INDEX SEEK. На быстроту выполнения не повлияет.

SELECT B.*
FROM(SELECT id FROM book WHERE tittle = :tittle AND :author IS NULL 
  UNION ALL SELECT id FROM book WHERE author = :author AND :tittle IS NULL
  UNION ALL SELECT id FROM book WHERE tittle = :tittle AND author = :author
)B_Filtered
  --что-то вроде такого
  JOIN Book B ON B.Id = B_Filtered.id

А вот ваш OR во втором варианте - принуждает оптимизатор делать Scan таблицы...

PS: И вообще, когда у запроса есть несколько сценариев поиска, есть смысл на каждый сценарий с индексами получить список ID-ов искомых объектов своим запросом для каждого сценария, записать их во временную/переменную таблицу, а потом уже общим кодом навесить туда всю мешуру, которая требуется клиенту сервера БД.

Не помню как там в постгресе, но на псевдоке примерно так:

IF :author IS NULL
  INSERT #T SELECT Id FROM book WHERE tittle = :tittle
IF :tittle IS NULL
  INSERT #T SELECT Id FROM book WHERE author = :author
ELSE
  INSERT #T SELECT Id FROM book WHERE tittle = :tittle AND author = :author
  
SELECT B.*
FROM #T Filtered JOIN book B ON B.id = Filtered.id

БЛОГ НА HUSL

SQL запрос по 2-ум опциональным параметрам

Ответы (1 шт):