Обсуждение: 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

А еще по 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

>> 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