Thread: GIN индекс: сортировка
есть большая база текстушек id, text1, text2, text3, text4, ... text10 она местами разрежена (то есть text3 и text5 скажем могут быть равны null) далее в поисковом запросе юзер вводит поля через запятую от одного до десяти полей, но может вводить их в разном порядке соответственно построил я GIN индекс так CREATE INDEX ... USING GIN ((ARRAY[text1, text2, text3, text4, ... text10])) далее ищу в таблице так SELECT * FROM table WHERE ARRAY[text1, text2, text3, text4, ... text10] @> ARRAY[user_text1, ... user_textn] LIMIT 10 Ищет быстро и хорошо но хочется тут двух вещей 1. сортировки по близости то есть хочу чтобы сперва выдавались наиболее (или наоборот наименее) заполненные записи. то есть если в базе лежит 'text1', NULL, NULL, 'text4', 'text5', ... 'text1', 'text2', 'text3', 'text4', 'text5', ... А юзер в поиске прислал text1 и text4, то я хочу чтобы либо первый вариант выдавался в первую очередь, либо наоборот - второй, в зависимости от настроек. вопрос: можно ли выбрать это из индекса? 2. сортировки по порядку если юзер ввел 'text5', 'text1', можно ли чтобы это либо не находилось, либо иметь возможность чтобы оно попадало куда-то вглубь выборки (то есть первыми выводились бы записи с текстовым И позиционным совпадением, а далее только текстовым)? Ну и еще вопрос, уже наверное не про GIN, хотя может и про него предполагаем что пользователь вводит фразу text1, text2, abc сплитим фразу по запятым, все кроме последней части считаем точными совпадениями, а вот последнюю часть считаем частью ввода. то есть хочу чтобы индекс отвечал на вопрос "все поля, содержащие в себе text1 и text2 и плюс к этому любое поле, начинающееся (или содержащее в себе) с букв abc" Можно ли последнее упихать как-то в ОДИН индекс? -- . ''`. Dmitry E. Oboukhov : :’ : email: unera@debian.org jabber://UNera@uvw.ru `. `~’ GPGKey: 1024D / F8E26537 2006-11-21 `- 1B23 D4F8 8EC0 D902 0555 E438 AB8C 00CF F8E2 6537
Attachment
А еще по GIST может кто подскажет такая база: id, text база оч большая (база из прошлого примера, просто все строки сконкатенированы) строим GIST на триграммах CREATE INDEX "test_trgm_idx" ON "table" USING GIST ( "text" "public"."gist_trgm_ops" ); Далее кладем в базу мнооого записей. Далее запрос SELECT * FROM table ORDER BY "text" <-> 'test' LIMIT 100 Работает, но меееееедленно: EXPLAIN ANALYZE показывает такое: Limit (cost=0.67..209.06 rows=50 width=159) (actual time=36.071..2356.039 rows=50 loops=1) -> Index Scan using test_trgm_idx on table (cost=0.67..23567041.91 rows=5654375 width=159) (actual time=36.070..2356.012rows=50 loops=1) Order By: text <-> 'test' Total runtime: 2356.102 ms (4 строки) Я чет не понимаю. по идее он должен был бы открыть итератор и идти от наиболее похожих к наименее и взять первые 100. а он весь индекс перебирает (то есть проку от индекса - 0, без индекса работает столько же времени - 2 секунды) Вопрос: что сделать чтобы уменьшить время работы? -- . ''`. Dmitry E. Oboukhov : :’ : email: unera@debian.org jabber://UNera@uvw.ru `. `~’ GPGKey: 1024D / F8E26537 2006-11-21 `- 1B23 D4F8 8EC0 D902 0555 E438 AB8C 00CF F8E2 6537
Attachment
>> USING GIST >> ... >> Далее кладем в базу мнооого записей. > Возможно, у вас вырождаются сигнатуры индексов и оптимизатор решает, > что проще просмотреть всю таблицу, чем разыменовывать кучу дубликатов. > Какого в среднем размера текстовые поля? если в символах, то средний размер записи получается 64.02 байта ну а если в байтах, то там utf8, вдвое больше но вроде триграммы же юникодные я попробовал по 20-символьной части индекс построить, тоже самое > Такой вопрос: вы это через прямое хранение tsvector и сравнение с ним > не пробовали делать? нет еще. я пока просто разглядываю что есть в рамках решения задачи в предыдущем письме. в целом GIN индекс меня полностью устраивает (и классно работает!) но мне нужно кроме выборок из него - сортировки. и like 'word%' по последнему элементу ну вот я грешным делом подумал соединить это все в кучу и на GIST поиграться на том что он близость умеет. но пока вышла фигня какая-то -- . ''`. Dmitry E. Oboukhov : :’ : email: unera@debian.org jabber://UNera@uvw.ru `. `~’ GPGKey: 1024D / F8E26537 2006-11-21 `- 1B23 D4F8 8EC0 D902 0555 E438 AB8C 00CF F8E2 6537