Способ попробовать несколько SELECT, пока не будет доступен результат?

Что если я хочу найти одну строку в таблице с убывающей точностью, например, так:

SELECT * FROM image WHERE name LIKE 'text' AND group_id = 10 LIMIT 1

Когда это не дает мне результата, попробуйте это:

SELECT * FROM image WHERE name LIKE 'text' LIMIT 1

И когда это не дает мне результата, попробуйте это:

SELECT * FROM image WHERE group_id = 10 LIMIT 1

Можно ли сделать это только одним выражением?

Также возникает проблема, когда у меня есть не два, а, например, три или более параметров поиска. Есть ли общее решение для этого? Конечно, это пригодится, когда результаты поиска отсортированы по значимости.

Источник

4 ответа

Решение

LIKE без подстановочного знака эквивалентно =, Предполагая, что вы на самом деле имели в виду name = 'text',

Индексы являются ключом к производительности.

Испытательная установка 

CREATE TABLE image (
  image_id serial PRIMARY KEY
, group_id int NOT NULL
, name     text NOT NULL
);

В идеале вы должны создать два индекса (в дополнение к первичному ключу):

CREATE INDEX image_name_grp_idx ON image (name, group_id);
CREATE INDEX image_grp_idx ON image (group_id);

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

запрос

Это должен быть самый быстрый запрос для вашего случая:

SELECT * FROM image WHERE name = 'name105' AND group_id = 10
UNION ALL
SELECT * FROM image WHERE name = 'name105'
UNION ALL
SELECT * FROM image WHERE group_id = 10
LIMIT  1;

SQL Fiddle.

LIMIT пункт распространяется на весь запрос. Postgres достаточно умен, чтобы не выполнять более поздние этапы UNION ALL как только он нашел достаточно строк, чтобы удовлетворить LIMIT, Следовательно, для матча в первом SELECT запроса, вывод EXPLAIN ANALYZE выглядит так (прокрутите вправо!):

Предел (стоимость =0.00..0.86 строк =1 ширина =40) (фактическое время =0.045..0.046 строк =1 цикл =1) Буферы: локальное попадание =4
  -> Результат (стоимость =0.00..866.59 строк =1002 ширина =40) (фактическое время =0.042..0.042 строки =1 цикл =1) Буферы: локальное попадание =4
  -> Добавить (стоимость =0.00..866.59 строк =1002 ширина =40) (фактическое время =0.039..0.039 строк =1 петля =1) Буферы: локальное попадание =4
  -> индексное сканирование с использованием image_name_grp_idx на изображении (стоимость = 0,00..3.76 строк =2 ширины =40) (фактическое время = 0,035..0.035 строк =1 петли =1) Индекс Cond: ((name = 'name105'::text) AND (group_id = 10)) Буферы: локальное попадание =4
  -> индексное сканирование с использованием image_name_grp_idx на изображении (стоимость =0.00..406.36 строк =500 ширина = 40) (никогда выполнено) Index Cond: (name = 'name105':: text) -> Сканирование индекса с использованием image_grp_idx на изображении (стоимость = 0,00.406,36 строк =500 width = 40) (никогда не выполняется) Cond индекса: (group_id = 10) Всего время выполнения: 0,087 мс

Жирный акцент мой.

Не добавлять ORDER BY пункт, это аннулирует эффект. Тогда Postgres должен будет рассмотреть все строки перед возвратом верхнего ряда.

Заключительные вопросы

Есть ли общее решение для этого?

Это общее решение. Добавить как можно больше SELECT Заявления, как вы хотите.

Конечно, это пригодится, когда результаты поиска отсортированы по значимости.

В результате есть только одна строка с LIMIT 1, Вид сортировки пустот.

Источник

Уже поздно, и мне не хочется писать полное решение, но если бы мне это понадобилось, я бы, вероятно, создал функцию клиента, которая возвращала бы тип клиента, запись или таблицу (в зависимости от ваших потребностей). Преимущество этого состоит в том, что как только вы найдете свою запись, вы можете остановиться.

Создание динамического числа параметров сделает его более сложным. В зависимости от вашей версии PostgreSQL (и доступного вам расширения) вы можете передать hstore или json и динамически создать запрос.

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

Источник

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

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

Источник 
SELECT *, 
CASE WHEN name like 'text' AND group_id = 10 THEN 1
WHEN name like 'text' THEN 2
WHEN group_id = 10 THEN 3
ELSE 4
END ImageRank
FROM image
WHERE ImageRank <> 4
ORDER BY ImageRank ASC
LIMIT 1

Это был бы подход с псевдо-решением, но я не совсем уверен, если синтаксис в вашем сценарии позволит это

Источник
Другие вопросы по тегам