Добрый день!

В таблице порядка 300 тысяч строк, добавил массив с тегами, по сути собрал значения 3 полей в одно, чтобы искать более быстро.
Теперь вопрос - можно ли ускорить поиск по varchar массиву с индексом типа gin?

Если можно, то как?

Вопрос два - для выборки всех тегов использую SELECT unnset(atag) FROM store_itemvariant WHERE ...
Можно ли и это как-то ускорить? Пока она выходит самой длинной операцией.

ромашка какой-та, чем плоха композиция? join работает несколько медленнее, чем такой вариант. Не знаю, а поиск по отдельному полю будет работать шустрее, чем по всему этому счастью?

Чего-то я так увлекся этими массивами, что уже успел найти пару ошибок в расширениях django.. что даже не знаю, а надо ли мне такое счастье)

Увеличил оперативную память сервера до 24гб, чтобы вся база помещалась в памяти, надеюсь на чудо)

Вот такая вот модель, корявая, но не нашел другого нормального редактора базы онлайн

Когда мне приходит запрос - показать все товары с параметрами tag_one = 1, tag_two = 1, tag_three = 1, category = 1, 2
я лезу в item_variant, выбираю все item_id у item_variant, в параметрах которых нужные мне значения.
Для ускорения я сложил все это в tags_and_categories, туда закинул только title_for_url, собственно, при каждом запросе мне надо вывести все подходящие товары и все теги в item_variant этих товаров.
Поэтому я использую unnest в запросе типа

SELECT unnset(tags_and_categories) FROM item_variant WHERE item_id IN (SELECT item_id FROM item_variant WHERE tags_and_categories @>[tag_one, tag_two, cat_one, cat_two])

Как-то так.
Этот запрос достаточно длительный, я решил его просто кешировать, что не есть хорошо) Хочу просто быстрее) И поиск быстрее.

И вообще, запрос со временем трансформировался в

SELECT unnset(tags_and_categories) FROM item_variant WHERE item_id IN (SELECT item_id FROM item_variant WHERE tags_and_categories @>[tag_one, tag_two, cat_one, cat_two] AND tags_and_categories &&[tag_three_one, tag_three_two])

Надеюсь более понятно собрал мысли. Нет у меня опыта в задавании вопросов таких(

ромашка какой-та,
у меня есть проблема. Количество "тегов", если это можно так сказать, вырастет)
А с параметром категории - вообще беда) Мне, к примеру, надо выбрать Стулья красного цвета, а иногда Стулья-табуретки красного цвета, а иногда просто табуретки красного цвета.
Да, пример слегка плохой, но можно посчитать, что табуретками бывают не только стулья, но и столы, тогда иногда надо выбирать Столы-табуретки красного цвета. Поэтому точно но выйдет уложить в столбцы. К тому же, категорий иногда бывает 3!
Вот так-то)

Попробовал делать выборку SELECT tag_one_id, tag_two_id, tag_three_id FROM item_variant WHERE atags @> ARRAY['category'] GROUP BY 1, 2, 3
Занимает 200 мс, что, на мой взгляд, достаточно долго, но тот же unnest на этом объеме занимает большее время. Уже не знаю куда ускоряться(

А вот explain buffers

"QUERY PLAN"
"HashAggregate (cost=138772.57..139824.11 rows=207200 width=102) (actual time=606.557..606.579 rows=118 loops=1)"
" Buffers: shared hit=19416"
" -> Hash Semi Join (cost=26582.75..107127.57 rows=12658000 width=102) (actual time=113.616..431.191 rows=754091 loops=1)"
" Hash Cond: (public.store_itemvariant.item_id = public.store_itemvariant.item_id)"
" Buffers: shared hit=19416"
" -> Seq Scan on store_itemvariant (cost=0.00..15202.85 rows=297385 width=106) (actual time=0.019..116.043 rows=293183 loops=1)"
" Buffers: shared hit=12229"
" -> Hash (cost=26553.66..26553.66 rows=2327 width=4) (actual time=113.563..113.563 rows=3205 loops=1)"
" Buckets: 1024 Batches: 1 Memory Usage: 113kB"
" Buffers: shared hit=7187"
" -> Unique (cost=25897.76..26530.39 rows=2327 width=4) (actual time=93.959..112.945 rows=3205 loops=1)"
" Buffers: shared hit=7187"
" -> Sort (cost=25897.76..26214.08 rows=126527 width=4) (actual time=93.958..101.395 rows=125960 loops=1)"
" Sort Key: public.store_itemvariant.item_id"
" Sort Method: quicksort Memory: 8977kB"
" Buffers: shared hit=7187"
" -> Bitmap Heap Scan on store_itemvariant (cost=1364.59..15175.17 rows=126527 width=4) (actual time=30.404..69.973 rows=125960 loops=1)"
" Recheck Cond: (atags @> '{category}'::text[])"
" Buffers: shared hit=7184"
" -> Bitmap Index Scan on store_itemvariant_idx2 (cost=0.00..1332.96 rows=126527 width=0) (actual time=29.312..29.312 rows=130631 loops=1)"
" Index Cond: (atags @> '{category}'::text[])"
" Buffers: shared hit=103"
"Total runtime: 607.383 ms"

Поломалось, вот нормальный вариант - http://pastebin.com/nt5VuVLB

Самым простым решением оказалось добавить поле used_in_search и искать только по тем, у кого оно в true, остальные игнорить и тогда база сократилась до 86 тысяч строк, очень быстрый поиск)

Вся проблема была в большом количестве записей в таблице, делать unnest по такому огромному количеству строк postgresql быстро не умеет. А запрос - просто уберите 3 тега и оставьте один, вот и получится тоже самое

Заметил ошибку в запросе, не unnset, а unnest.