PostrgreSQL: ускоряемся через intarray

[KAndy]

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

[xobotyi]

агрЕгациях, вать машу!!!! *аштрисёт* (┛ಠДಠ)┛彡┻━┻
И речь вообще не про эластик, а про методики ускорения запросов с параметрической фильтрацией посредством intarray, каким боком сюда затесались полнотекстовые солр и эластик?

[anydasa]

Полнотекстовым поиском конечно можно решить эту задачу. Как можно и отверткой копать землю.

[vitalif]

Вы зря, это вполне нормальный подход, во многих крупных проектах применяется.
Смысл в том, что если фильтров много, их выгодно загнать в один инвертированный индекс. GIN — это инвертированный индекс и полнотекстовый индекс — это тоже инвертированный индекс, да и задача фактически одна и та же — взять несколько терминов, взять наборы документов, их содержащие, и пересечь. Полнотекстовый индекс получится один общий, поэтому будет даже ещё немного шустрее. Плюс туда же можно загнать и непосредственно текстовые описания, и тогда шустро работать будет ещё и поиск по тексту + фильтрам :-)
Да, плюс полнотекстовые движки умеют заодно и информацию о пресловутых агрегациях выдавать, их фасетами ещё называют, т.е. это ответ на вопрос «какие значения фильтров есть у найденных товаров?»

[anydasa]

Я не спорю в целом. А конкретно в данном случае, фильтры это записи в таблице. т.е. у них есть ID:INTEGER.
Поиск по инту быстрее чем по строке, пусть и полнотекстовым поиском со всеми умными индексами.

а что до «какие значения фильтров есть у найденных товаров?», ну тут мы тоже имеем айдишки на выходе.

[foxmuldercp]

а можно несколько нескромно влезть в тредик?
а если, допустим, часть полей описания товара, те поля, которые динамические, и в т.ч список его фильтров, хранить в jsonb?

[anydasa]

можно и так, только для чего? вопрос не полон.

[alekciy]

всреднем 7 атрибутах

Автор явно заявил атрибут (параметр) != фильтр

[anydasa]

Kandy, ваш вариант тоже предполагает что набор фильтров может быть динамическим, и с любым количеством?

[anydasa]

Если взять в пример, хотлайн или фотос, то там фильтр это запись в бд. Это может быть range или нет, это вообще не важно. Фильтр для товара, на уровне базы, это просто метка. На проекте где я реализовал такой поход, есть дополнительные обработчики, которые определяют в какой фильтр должен входить товар и автоматически назначают его.

[anydasa]

ползунки не получится.

[xobotyi]

Вот тут, на мой взгляд, не стоит городить огород и использовать стандартное ложкой-в-лоб решение:

SELECT * FROM products WHERE width>=$w_min AND width <=$w_max;

Если какой-то более упоротый рейндж, в котором не все подряд значения, то с применением intarray выходит что-то а-ля:

SELECT *FROM products WHERE idx('{1,2,3,15,100500}', width)::boolean;

Правда такой вариант работает в районе 40-50 раз медленнее. И тогда уже лучше использовать опять же ортодоксальный IN

SELECT *FROM products WHERE width IN (1,2,3,15,100500);

который работает с той же скорость что >= AND <=

[xobotyi]

Типа, от 1 до 10 000 с шагом 1. Такое в intarray не положить.

Ну, вот такое, опять же, просто решить:

SELECT * FROM products WHERE width>=1 AND width<=10000 AND (width%2)::boolean;

А что касается этого:

С таким вариантом проблема, когда продукты разнородные и у них есть не только width, но и height, depth, length, diagonal, density, volume, etc.
Каждый фильтр = столбец.

Надо по-просту верно составлять запрос, выставляя фильтры в порядке убывания фактора сокращения выборки, самые тяжелые фильры оставляя «на потом». А после этого прогнать запрос через EXPLAIN (ANALYSE, buffers) и поиграться с расположением фильтров.

[alekciy]

Как я понимаю помогут только через пресеты как тут: "Невозможность закодировать фильтры типа range… фильтр по ценам представлял из себя просто пять вариантов"

[anydasa]

Вы поняли верно.

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

Если магазин токовый, то набор фильтров для определенной категории, тщательно продумывается.

Для пользователя гораздо легче просто кликнуть чем думать о том что указать. Тем более если он не в теме.

[velvetcat]

Если я ничего не путаю (не спец в этой области), то для решения таких задач хорошо подходят битмап-индексы.

[anydasa]

Спасибо за вопрос. Он меня натолкнул на размышление. Вообще я не гуру, и даже не спец SQL. Просто данное решение работает очень быстро. Что мне собственно и нужно. Хотелось бы все знать, но увы. Человеческий ресурс.

Вопрос к знатокам.

Делаю запрос, поле filters, которое как помним INTEGER[].

SELECT * FROM products WHERE filters @> ARRAY[7267]

Если индекса, нет

Seq Scan on public.products  (cost=0.00..20.38 rows=1 width=68)
  Output: id, title, filters
  Filter: (products.filters @> '{7267}'::integer[])

Индекс, GIST (gist__int_ops)

Index Scan using products_idx on public.products  (cost=0.14..8.16 rows=1 width=68)
  Output: id, title, filters
  Index Cond: (products.filters @> '{7267}'::integer[])

Индекс, GIN (gin__int_ops)

Bitmap Heap Scan on public.products  (cost=8.01..12.02 rows=1 width=68)
  Output: id, title, filters
  Recheck Cond: (products.filters @> '{7267}'::integer[])
  ->  Bitmap Index Scan on products_idx  (cost=0.00..8.01 rows=1 width=0)
        Index Cond: (products.filters @> '{7267}'::integer[])

Не тот ли это битмап, о котором вы спрашиваете?

[Chpock]

Я далеко не профессионал в PostgreSQL, но как-то пришлось переезжать с v8 на v9 и наблюдать, с моего взгляда, совершенно иррациональное поведение постгреса с индексами для intarray и огромными таблицами. Гугл мне ничем не помог, пришлось просто методом тыка определить что быстрее всего будет для v9 и именно с моими таблицами. Что бы не забыть почему я в конечном итоге сделал так, а не иначе, результаты своих экспериментов сохранял — goo.gl/TbOePV. Может чем-нибудь поможет.

[velvetcat]

Не тот ли это битмап, о котором вы спрашиваете

Да, это оно. Круто, что Постгресс именно так и работает.