AkinaDima TХЗ за что его так не любят. План для простых запросов быстро строится.Планировщик - он один на инстанс сервера, и работает в одном потоке. При большом потоке запросов он запросто может стать узким местом.
Мой пример 20011643 можно к параметризованному свести, в итоге будет не более сотни видов запросов типа такого


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

PS Сразу не сообразил что cat_id в запросе лишний и дублировать его не надо в таблицу Attribute.

maytonХм... моя модель вышла более скромной. 3 таблицы. Где-то я просчитался?
Таблица PROD_ATTRS как понимаю у тебя не нормализована. Твой ATTR_NAME надо в отдельную таблицу ATTRIBUTES, а сюда ATTR_ID.
Раз по ТЗ все галочками, то ATTR_VALUE вообще не надо. У тебя два значения TRUE/FALSE, TRUE есть запись в PROD_ATTRS, FALSE - нет записи.

У меня Category это категории характеристик (Диагональ, Разрешение и т.д.) а у тебя как понимаю группа товаров (Телевизор, Холодильник и т.д.) это надо добавить для наполнения базы, но я это вообще не рассматривал, т.к. вопрос о поиске.

maytonХм... из личного опыта. Запросы с group by ... having всегда работали тяжело и не в OLTP-стиле.
Не уверен что так происходит, но тут после выборок подзапросов серверу можно снимать блокировки с рабочей таблицы, сливать результат union во временную таблицу и group by делать по ней. Про тяжесть уже писал, group by по 50к записей это не много.
maytonПо поводу параллелизма. Для обыкновенных DBMS он остаётся мифом. Практически очень мало
запросов могут в explain plan выдать признаки параллелизма.
В данном случае имеем как раз то что параллелится, несколько подзапросов объединенных через union, каждый можно выполнять независимо от других. Это редкий случай когда есть что параллелить.

Можно написать так чтобы не параллелилось


group by тут не надо, в промежуточных таблицах много данных не возникнет, но это не параллелится.

maytonDima TПро размер:
Предположим в среднем 50 атрибутов на товар, тогда по размеру Attribute получится примерно 500 000 записей, 16 байт на запись итого 8 Мб.

Можно затестить, надо только генератор данных сделать
Разрази меня гром. Я не понимаю как ты посчитал про 8 мегабайт. Здесь оценки можно уверенно дать
только после моделирования. Опции хранения - это самая загадочная часть dbms и здесь я-бы не стал
делать прогнозы.
Все просто: таблица Attribute это привязка товара к галочке. Т.е. чтобы товар "ТВ Gnusmas 40" FullHD" найти галочками, то надо этот товар привязать к двум Value, т.е. 39"-43" и FullHD. Это две записи в таблицу Attribute.
Ты написал что товаров 10000, Akina 20009771 предположил что привязок не более 50 на товар, вот я и прикинул 50*10000.

maytonПо поводу генерации данных. Это больной вопрос. Забегая вперед я даже скажу что он на 60%-80% определяет
саму постановку. Тоесть эффективность решения нашей задачи не столько зависит от формул или технологий
сколько от гистограммы наших данных и характера нагрузки.

По сабжу я пока решил сделать так. Я нагуглил штук 10 магазинов стройматериалов и скачал их прайсы в виде
xls, и думаю что если их обработать и загрузить то будет вполне себе нормальный набор для тестов.
Выделение характеристик и привязку к ним кто будет делать? Жопа в этом, написания в прайсах не нормализованы, я про ИИ потому и писал что сложная это работа выделять характеристики и привязывать к ним.

maytonDimitry SibiryakovЭто очень упрощает задачу. Главная таблица связи товаров с их характеристиками упрощается, поскольку значение характеристики представлено одним полем-ссылкой на справочник групп. Критерии в поисковом запросе сводятся к "Тип_характеристики=? and Группа_значения=?", что покрывается индексом. Отсутствие having в запросе позволяет нечёткий поиск по релевантности.
Хм... мне не очень нравится постановка нечеткого поиска. Я как клиент интернет магазинов
желаю точный поиск. К примеру я указал что я ищу зеркальный фотик где производитель никон
и матрица в 24 мегапиксела и я хочу получить список именно из этих позиций товара без всяких
посторонних похожих.


Сибиряков прав: если сначала выдать то что совпадает по всем трем характеристикам (count(*) = 3), затем по двум, то никому хуже не будет. Покупатель в начале увидит то что просил, но дальше вполне может оказаться близкое, но то что не просил. Возможно оно больше его устроит по каким-то пятым свойствам не упомянутым в фильтре.

maytonDima TНе уверен что так происходит, но тут после выборок подзапросов серверу можно снимать блокировки с рабочей таблицы, сливать результат union во временную таблицу и group by делать по ней. Про тяжесть уже писал, group by по 50к записей это не много.

Ну... я еще раз акцентирую внимание на том что пользователь нашего API это праздный клиент.
Который может рассеянно кликать-кликать на один и тот-же checkbox просто "по приколу".
Таков он есть. А мы на каждый его клик будем создавать временную таблицу?
Я не про временную в терминологии БД, давай назовем ее промежуточной.

Вобщем я о том что сервер сначала сделает


сохранит результат куда-то во временную/промежуточную таблицу T, затем выполнит


Результат вернет клиенту, а T удалит.

maytonДима. К тебе просьба оценить разницу между твоей и моей моделью. Может я чего-то недосказал
или наоборот ты глубже сделал нормализацию.
Это та же картинка только текстом. Картинкой понятней. Хоть чуть-чуть поясни какая таблица что содержит. Я тут 20016306 уже пытался гадать что к чему, пока добавить нечего.

Давай конкретный пример рассмотрим:
Товар "ТВ Gnusmas 40" FullHD" надо привязать к галочкам 39"-43" и FullHD

Как это сохранится в твоей БД?

У меня будет так:

Tovar
tov_idtov_name1ТВ Gnusmas 40" FullHD
Category
cat_idcat_name2Диагональ3Разрешение
Value
val_idcat_idval_name4239"-43"53FullHD
Attribute
tov_idval_id1415