Мы уже рассмотрели механизм индексирования PostgreSQL, интерфейс методов доступа и все основные методы доступа, как то: хеш-индексы, B-деревья, GiST, SP-GiST и GIN. А в этой части посмотрим на превращение джина в ром.

RUM


Хоть авторы и утверждают, что джин — могущественный дух, но тема напитков все-таки победила: GIN следующего поколения назвали RUM.

Этот метод доступа развивает идею, заложенную в GIN, и позволяет выполнять полнотекстовый поиск еще быстрее. Это единственный метод в этой серии статей, который не входит в стандартную поставку PostgreSQL и является сторонним расширением. Есть несколько вариантов его установки:

  • Взять пакет yum или apt из репозитория PGDG. Например, если вы ставили PostgreSQL из пакета postgresql-10, то поставьте еще postgresql-10-rum.
  • Самостоятельно собрать и установить из исходных кодов на github (инструкция там же).
  • Пользоваться в составе Postgres Pro Enterprise (или хотя бы читать оттуда документацию).

Ограничения GIN


Какие ограничения индекса GIN позволяет преодолеть RUM?

Во-первых, тип данных tsvector, помимо самих лексем, содержит информацию об их позициях внутри документа. В GIN-индексе, как мы видели в прошлый раз, эта информация не сохраняются. Из-за этого операции фразового поиска, появившиеся в версии 9.6, обслуживается GIN-индексом неэффективно и вынуждены обращаться к исходным данным для перепроверки.

Во-вторых, поисковые системы обычно возвращают результаты в порядке релевантности (что бы это ни означало). Для этого можно пользоваться функциями ранжирования ts_rank и ts_rank_cd, но их приходится вычислять для каждой строки результата, что, конечно, медленно.

Метод доступа RUM в первом приближении можно рассматривать как GIN, в который добавлена позиционная информация, и который поддерживает выдачу результата в нужном порядке (аналогично тому, как GiST умеет выдавать ближайших соседей). Пойдем по порядку.

Фразовый поиск


Запрос в полнотекстовом поиске может содержать специальные конструкции, которые учитывают расстояние между лексемами. Например, можно найти документы, в которых бабку от дедки отделяет еще одно слово:

postgres=# select to_tsvector('Бабка за дедку, дедка за репку...') @@
                  to_tsquery('бабка <2> дедка');
 ?column?
----------
 t
(1 row)

Или указать, что слова должны стоять друг за другом:

postgres=# select to_tsvector('Бабка за дедку, дедка за репку...') @@
                  to_tsquery('дедка <-> дедка');
 ?column?
----------
 t
(1 row)

Обычный индекс GIN может выдать документы, в которых есть обе лексемы, но проверить расстояние между ними можно, только заглянув в tsvector:

postgres=# select to_tsvector('Бабка за дедку, дедка за репку...');
         to_tsvector
------------------------------
 'бабк':1 'дедк':3,4 'репк':6
(1 row)

В индексе RUM каждая лексема не просто ссылается на строки таблицы: вместе с каждым TID-м лежит и список позиций, в которых лексема встречается в документе. Вот как можно представить себе индекс, созданный на уже хорошо знакомой нам таблице с белой березой (по умолчанию для tsvector используется класс операторов rum_tsvector_ops):

postgres=# create extension rum;
CREATE EXTENSION
postgres=# create index on ts using rum(doc_tsv);
CREATE INDEX



Серые квадраты на рисунке — добавленная позиционная информация:

postgres=# select ctid, doc, doc_tsv from ts;
  ctid  |           doc           |            doc_tsv            
--------+-------------------------+--------------------------------
 (0,1)  | Во поле береза стояла   | 'берез':3 'пол':2 'стоя':4
 (0,2)  | Во поле кудрявая стояла | 'кудряв':3 'пол':2 'стоя':4
 (0,3)  | Люли, люли, стояла      | 'люл':1,2 'стоя':3
 (0,4)  | Люли, люли, стояла      | 'люл':1,2 'стоя':3
 (1,1)  | Некому березу заломати  | 'берез':2 'заломат':3 'нек':1
 (1,2)  | Некому кудряву заломати | 'заломат':3 'кудряв':2 'нек':1
 (1,3)  | Люли, люли, заломати    | 'заломат':3 'люл':1,2
 (1,4)  | Люли, люли, заломати    | 'заломат':3 'люл':1,2
 (2,1)  | Я пойду погуляю         | 'погуля':3 'пойд':2
 (2,2)  | Белую березу заломаю    | 'бел':1 'берез':2 'залома':3
 (2,3)  | Люли, люли, заломаю     | 'залома':3 'люл':1,2
 (2,4)  | Люли, люли, заломаю     | 'залома':3 'люл':1,2
(12 rows)

В GIN еще есть отложенная вставка при указании параметра fastupdate; в RUM эта функциональность убрана.

Чтобы посмотреть, как индекс работает на реальных данных, воспользуемся известным нам архивом рассылки pgsql-hackers.

fts=# alter table mail_messages add column tsv tsvector;
ALTER TABLE
fts=# set default_text_search_config = default;
SET
fts=# update mail_messages
set tsv = to_tsvector(body_plain);
...
UPDATE 356125

Вот как выполняется запрос, использующий фразовый поиск, с индексом GIN:

fts=# create index tsv_gin on mail_messages using gin(tsv);
CREATE INDEX
fts=# explain (costs off, analyze)
select * from mail_messages where tsv @@ to_tsquery('hello <-> hackers');
                                   QUERY PLAN                                    
---------------------------------------------------------------------------------
 Bitmap Heap Scan on mail_messages (actual time=2.490..18.088 rows=259 loops=1)
   Recheck Cond: (tsv @@ to_tsquery('hello <-> hackers'::text))
   Rows Removed by Index Recheck: 1517
   Heap Blocks: exact=1503
   ->  Bitmap Index Scan on tsv_gin (actual time=2.204..2.204 rows=1776 loops=1)
         Index Cond: (tsv @@ to_tsquery('hello <-> hackers'::text))
 Planning time: 0.266 ms
 Execution time: 18.151 ms
(8 rows)

Как видно из плана, GIN-индекс используется, но возвращает 1776 потенциальных совпадений, из которых остается 259, а 1517 отбрасываются на этапе перепроверки.

Удалим теперь GIN-индекс и построим RUM.

fts=# drop index tsv_gin;
DROP INDEX
fts=# create index tsv_rum on mail_messages using rum(tsv);
CREATE INDEX

Теперь в индексе есть вся необходимая информация и поиск выполняется точно:

fts=# explain (costs off, analyze)
select * from mail_messages
where tsv @@ to_tsquery('hello <-> hackers');
                                   QUERY PLAN                                  
--------------------------------------------------------------------------------
 Bitmap Heap Scan on mail_messages (actual time=2.798..3.015 rows=259 loops=1)
   Recheck Cond: (tsv @@ to_tsquery('hello <-> hackers'::text))
   Heap Blocks: exact=250
   ->  Bitmap Index Scan on tsv_rum (actual time=2.768..2.768 rows=259 loops=1)
         Index Cond: (tsv @@ to_tsquery('hello <-> hackers'::text))
 Planning time: 0.245 ms
 Execution time: 3.053 ms
(7 rows)

Сортировка по релевантности


Для того, чтобы выдавать документы сразу в нужном порядке, индекс RUM поддерживает упорядочивающие операторы, о которых у нас шла речь в части про GiST. Расширение rum определяет такой оператор <=>, возвращающий некое расстояние между документом (tsvector) и запросом (tsquery). Например:

fts=# select to_tsvector('Бабка за дедку, дедка за репку...') <=> to_tsquery('репка');
 ?column?
----------
  16.4493
(1 row)

fts=# select to_tsvector('Бабка за дедку, дедка за репку...') <=> to_tsquery('дедка');
 ?column?
----------
  13.1595
(1 row)

Документ оказался более релевантен первому запросу, чем второму: чем чаще в документе встречается слово, тем менее оно «ценно».

Снова попробуем сравнить GIN и RUM на относительно большом объеме данных: выберем десять наиболее релевантных документов, содержащих «hello» и «hackers».

fts=# explain (costs off, analyze)
select * from mail_messages
where tsv @@ to_tsquery('hello & hackers')
order by ts_rank(tsv,to_tsquery('hello & hackers'))
limit 10;
                                         QUERY PLAN
---------------------------------------------------------------------------------------------
 Limit (actual time=27.076..27.078 rows=10 loops=1)
   ->  Sort (actual time=27.075..27.076 rows=10 loops=1)
         Sort Key: (ts_rank(tsv, to_tsquery('hello & hackers'::text)))
         Sort Method: top-N heapsort  Memory: 29kB
         ->  Bitmap Heap Scan on mail_messages (actual ... rows=1776 loops=1)
               Recheck Cond: (tsv @@ to_tsquery('hello & hackers'::text))
               Heap Blocks: exact=1503
               ->  Bitmap Index Scan on tsv_gin (actual ... rows=1776 loops=1)
                     Index Cond: (tsv @@ to_tsquery('hello & hackers'::text))
 Planning time: 0.276 ms
 Execution time: 27.121 ms
(11 rows)

GIN-индекс возвращает 1776 совпадений, которые затем отдельно сортируются для выборки десяти наиболее подходящих.

С индексом RUM запрос выполняется простым индексным сканированием: никакие лишние документы не просматриваются, никакой отдельной сортировки не требуется:

fts=# explain (costs off, analyze)
select * from mail_messages
where tsv @@ to_tsquery('hello & hackers')
order by tsv <=> to_tsquery('hello & hackers')
limit 10;
                                         QUERY PLAN
--------------------------------------------------------------------------------------------
 Limit (actual time=5.083..5.171 rows=10 loops=1)
   ->  Index Scan using tsv_rum on mail_messages (actual ... rows=10 loops=1)
         Index Cond: (tsv @@ to_tsquery('hello & hackers'::text))
         Order By: (tsv <=> to_tsquery('hello & hackers'::text))
 Planning time: 0.244 ms
 Execution time: 5.207 ms
(6 rows)

Дополнительная информация


Индекс RUM, как и GIN, можно построить по нескольким полям. Но если в GIN лексемы разных столбцов хранятся независимо друг от друга, то RUM позволяет «связать» основное поле (tsvector в нашем случае) с дополнительным. Для этого надо воспользоваться специальным классом операторов rum_tsvector_addon_ops:

fts=# create index on mail_messages using rum(tsv rum_tsvector_addon_ops, sent)
  with (attach='sent', to='tsv');
CREATE INDEX

Такой индекс можно использовать, чтобы выдавать результаты в порядке сортировки по дополнительному полю:

fts=# select id, sent, sent <=> '2017-01-01 15:00:00'
from mail_messages
where tsv @@ to_tsquery('hello')
order by sent <=> '2017-01-01 15:00:00'
limit 10;
   id    |        sent         | ?column?
---------+---------------------+----------
 2298548 | 2017-01-01 15:03:22 |      202
 2298547 | 2017-01-01 14:53:13 |      407
 2298545 | 2017-01-01 13:28:12 |     5508
 2298554 | 2017-01-01 18:30:45 |    12645
 2298530 | 2016-12-31 20:28:48 |    66672
 2298587 | 2017-01-02 12:39:26 |    77966
 2298588 | 2017-01-02 12:43:22 |    78202
 2298597 | 2017-01-02 13:48:02 |    82082
 2298606 | 2017-01-02 15:50:50 |    89450
 2298628 | 2017-01-02 18:55:49 |   100549
(10 rows)

Здесь мы ищем подходящие строки, расположенные как можно ближе к указанной дате, не важно, раньше или позже. Чтобы получить результаты, строго предшествующие дате (или следующие за ней), надо воспользоваться операцией <=| (или |=>).

Запрос, как мы и ожидаем, выполняется простым индексным сканированием:

ts=# explain (costs off)
select id, sent, sent <=> '2017-01-01 15:00:00'
from mail_messages
where tsv @@ to_tsquery('hello')
order by sent <=> '2017-01-01 15:00:00'
limit 10;
                                   QUERY PLAN
---------------------------------------------------------------------------------
 Limit
   ->  Index Scan using mail_messages_tsv_sent_idx on mail_messages
         Index Cond: (tsv @@ to_tsquery('hello'::text))
         Order By: (sent <=> '2017-01-01 15:00:00'::timestamp without time zone)
(4 rows)

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

Конечно, кроме даты в RUM-индекс можно добавить поля и других типов данных — поддерживаются практически все базовые типы. Например, интернет-магазин может быстро показывать товары по новизне (дата), цене (numeric), популярности или размеру скидки (целое или плавающая точка).

Другие классы операторов


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

Начнем с rum_tsvector_hash_ops и rum_tsvector_hash_addon_ops. Они во всем аналогичны уже рассмотренным выше rum_tsvector_ops и rum_tsvector_addon_ops, но в индексе сохраняется не сама лексема, а ее хеш-код. Это может уменьшить размер индекса, но, разумеется, делает поиск менее точным и требующим перепроверки. Кроме того, индекс перестает поддерживать поиск частичных совпадений.

Любопытен класс операторов rum_tsquery_ops. Он позволяет решать «обратную» задачу: находить запросы, которые соответствуют документу. Зачем это может понадобиться? Например, подписать пользователя на новые товары по его фильтру. Или автоматически классифицировать новые документы. Вот простой пример:

fts=# create table categories(query tsquery, category text);
CREATE TABLE
fts=# insert into categories values
  (to_tsquery('vacuum | autovacuum | freeze'), 'vacuum'),
  (to_tsquery('xmin | xmax | snapshot | isolation'), 'mvcc'),
  (to_tsquery('wal | (write & ahead & log) | durability'), 'wal');
INSERT 0 3
fts=# create index on categories using rum(query);
CREATE INDEX

fts=# select array_agg(category)
from categories
where to_tsvector(
  'Hello hackers, the attached patch greatly improves performance of tuple
   freezing and also reduces size of generated write-ahead logs.'
) @@ query;
  array_agg  
--------------
 {vacuum,wal}
(1 row)

Остаются классы операторов rum_anyarray_ops и rum_anyarray_addon_ops — они предназначены для работы не с tsvector, а с массивами. Для GIN это уже рассматривалось в прошлый раз, так что нет резона повторяться.

Размер индекса и журнала предзаписи


Понятно, что, раз RUM содержит больше информации, чем GIN, то и места он будет занимать больше. В прошлый раз мы сравнивали размеры разных индексов; добавим в эту таблицу и RUM:

  rum   |  gin   |  gist  | btree
--------+--------+--------+--------
 457 MB | 179 MB | 125 MB | 546 MB

Как видно, объем вырос довольно существенно — такова плата за быстрый поиск.

Еще один неочевидный момент, на который стоит обратить внимание, связан с тем, что RUM является расширением, то есть его можно устанавливать, не внося никаких изменений в ядро системы. Это стало возможным в версии 9.6 благодаря патчу, который сделал Александр Коротков. Одна из задач, которые при этом пришлось решить — генерация журнальных записей. Механизм журналирования обязан быть абсолютно надежным, поэтому расширение нельзя пускать в эту кухню. Вместо того, чтобы позволять расширению создавать свои собственные типы журнальных записей, сделано так: код расширения сообщает о намерении изменить страницу, вносит в нее любые изменения и сигнализирует о завершении, а уже ядро системы сравнивает старую и новую версии страницы и само генерирует необходимые унифицированные журнальные записи.

Текущий алгоритм генерации сравнивает страницы побайтово, находит измененные фрагменты и записывает в журнал каждый такой фрагмент вместе со смещением от начала страницы. Это работает хорошо и при изменении всего нескольких байтов, и когда страница поменялась полностью. Но если внутрь страницы добавить какой-то фрагмент, сдвинув остальное содержимое вниз (или, наоборот, убрать фрагмент, сдвинув содержимое вверх), формально изменится значительно больше байтов, чем реально было добавлено или удалено.

Из-за этого активно изменяющийся RUM-индекс может генерировать журнальные записи существенно большего размера, чем GIN (который, будучи не расширением, а частью ядра, управляет журналом сам). Степень этого неприятного эффекта сильно зависит от реальной нагрузки, но, чтобы как-то почувствовать проблему, давайте попробуем несколько раз удалить и добавить некоторое количество строк, перемежая эти действия очисткой (vacuum). Оценить размер журнальных записей можно так: в начале и в конце запомнить позицию в журнале функцией pg_current_wal_location (до десятой верcии — pg_current_xlog_location) и затем посмотреть на их разность.

Тут, конечно, надо иметь в виду много факторов. Нужно убедиться, что в системе работает только один пользователь, иначе в расчет попадут «лишние» записи. Даже в этом случае мы учитываем не только RUM, но и изменения самой таблицы и индекса, поддерживающего первичный ключ. Влияют и значения конфигурационных параметров (здесь использовался уровень журнала replica, без сжатия). Но все же попробуем.

fts=# select pg_current_wal_location() as start_lsn \gset

fts=# insert into mail_messages(parent_id, sent, subject, author, body_plain, tsv)
  select parent_id, sent, subject, author, body_plain, tsv
  from mail_messages where id % 100 = 0;
INSERT 0 3576
fts=# delete from mail_messages where id % 100 = 99;
DELETE 3590
fts=# vacuum mail_messages;
VACUUM

fts=# insert into mail_messages(parent_id, sent, subject, author, body_plain, tsv)
  select parent_id, sent, subject, author, body_plain, tsv
  from mail_messages where id % 100 = 1;
INSERT 0 3605
fts=# delete from mail_messages where id % 100 = 98;
DELETE 3637
fts=# vacuum mail_messages;
VACUUM

fts=# insert into mail_messages(parent_id, sent, subject, author, body_plain, tsv)
  select parent_id, sent, subject, author, body_plain, tsv from mail_messages
  where id % 100 = 2;
INSERT 0 3625
fts=# delete from mail_messages where id % 100 = 97;
DELETE 3668
fts=# vacuum mail_messages;
VACUUM

fts=# select pg_current_wal_location() as end_lsn \gset
fts=# select pg_size_pretty(:'end_lsn'::pg_lsn - :'start_lsn'::pg_lsn);
 pg_size_pretty
----------------
 3114 MB
(1 row)

Итак, получилось около 3 ГБ. А если тот же эксперимент повторить с индексом GIN, будет всего около 700 МБ.

Поэтому хотелось бы иметь другой алгоритм, находящий минимальное количество операций вставки и удаления, с помощью которых одно состояние страницы можно привести к другому — аналогично тому, как работает утилита diff. Такой алгоритм уже реализовал Олег Иванов, его патч обсуждается. В приведенном примере этот патч, ценой небольшого замедления, позволяет сократить объем журнальных записей в полтора раза, до 1900 МБ.

Свойства


Традиционно посмотрим на свойства метода доступа rum (запросы приводились ранее), обратив внимание на отличия от gin.

Свойства метода:

 amname |     name      | pg_indexam_has_property
--------+---------------+-------------------------
 rum    | can_order     | f
 rum    | can_unique    | f
 rum    | can_multi_col | t
 rum    | can_exclude   | t -- f для gin

Свойства индекса:

     name      | pg_index_has_property
---------------+-----------------------
 clusterable   | f
 index_scan    | t -- f для gin
 bitmap_scan   | t
 backward_scan | f

Отметим, что RUM, в отличие от GIN, поддерживает индексное сканирование — иначе нельзя было бы получить ровно необходимое количество результатов в запросах с фразой limit. Соответственно, нет необходимости и в аналоге параметра gin_fuzzy_search_limit. Ну и, как следствие, индекс может использоваться для поддержки ограничений исключения.

Свойства уровня столбца:

        name        | pg_index_column_has_property
--------------------+------------------------------
 asc                | f
 desc               | f
 nulls_first        | f
 nulls_last         | f
 orderable          | f
 distance_orderable | t -- f для gin
 returnable         | f
 search_array       | f
 search_nulls       | f

Здесь отличие в том, что RUM поддерживает упорядочивающие операторы. Хотя и не для всех классов операторов: например, для tsquery_ops будет false.

Продолжение следует.

Комментарии (18)


  1. linuxover
    04.12.2017 17:42

    а вот если не tsvector, а свои функции?


    например у нас есть некие массивы txt (например — облако меток для постов блога), допустим столбик "column" TEXT[]
    кладем их в GIN


    далее GIN позволяет индекс использовать в запросах:


    WHERE "column" @> ARRAY['a','b','c']::TEXT;

    но при этом выдача несортирована.


    Вопрос: а как с GIN или RUM сортировать выборку например "начиная от тех кто еще имеет 'd' в списке"?


    Я чет попытался понять про приоритеты и GIN но не понял можно ли и как правильно индексировать своё.


    1. linuxover
      04.12.2017 17:49

      ARRAY['a','b','c']::TEXT[];

      сорри скобки недописал


      1. erogov Автор
        05.12.2017 01:14

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


        Но если очень нужно, то всегда можно пойти в обход.


        Вот у нас таблица и какие-то строчки в ней:


        posts=# CREATE TABLE posts(id serial, tags text[]);
        CREATE TABLE
        posts=# INSERT INTO posts(tags) VALUES 
          (ARRAY['a']), (ARRAY['a','d']), 
          (ARRAY['b']), (ARRAY['b','d']),
          (ARRAY['c']), (ARRAY['c','d']), 
          (ARRAY['x']), (ARRAY['x','d']); 
        INSERT 0 8

        Фактически мы хотим получить вот такой результат (только с помощью индекса):


        posts=# SELECT id, tags, tags @> ARRAY['d'] has_d 
        FROM posts 
        WHERE tags <@ ARRAY['a','b','c','d'] 
        ORDER BY 3 DESC;
         id | tags  | has_d 
        ----+-------+-------
          2 | {a,d} | t
          4 | {b,d} | t
          6 | {c,d} | t
          1 | {a}   | f
          3 | {b}   | f
          5 | {c}   | f
        (6 rows)

        Ок, создаем дополнительный столбец под нужное для сортировки свойство и триггер. Столбец делаем целочисленным, потому что с boolean RUM не научили работать.


        posts=# ALTER TABLE posts ADD has_d integer;
        ALTER TABLE
        
        posts=# CREATE FUNCTION set_has_d() RETURNS TRIGGER AS $$
        BEGIN
          new.has_d := CASE WHEN new.tags @> ARRAY['d'] THEN 1 ELSE 0 END;
          RETURN new;
        END;
        $$ LANGUAGE plpgsql;
        CREATE FUNCTION
        
        posts=# CREATE TRIGGER posts_has_d_trigger
        BEFORE INSERT OR UPDATE ON posts
        FOR EACH ROW
        EXECUTE PROCEDURE set_has_d();
        CREATE TRIGGER

        Можно обновить столбец, заодно проверим, что триггер отрабатывает корректно:


        cards=# UPDATE posts SET tags = tags;
        UPDATE 8
        cards=# SELECT * FROM posts;
         id | tags  | has_d 
        ----+-------+-------
          1 | {a}   |     0
          2 | {a,d} |     1
          3 | {b}   |     0
          4 | {b,d} |     1
          5 | {c}   |     0
          6 | {c,d} |     1
          7 | {x}   |     0
          8 | {x,d} |     1
        (8 rows)

        Ну а теперь уже индекс:


        posts=# CREATE INDEX ON posts USING rum(tags rum_anyarray_addon_ops, has_d) WITH (attach='has_d', to='tags');
        CREATE INDEX
        
        posts=# SELECT * FROM posts
        WHERE tags <@ ARRAY['a','b','c','d'] 
        ORDER BY has_d <=> 1;
         id | tags  | has_d 
        ----+-------+-------
          4 | {b,d} |     1
          2 | {a,d} |     1
          6 | {c,d} |     1
          5 | {c}   |     0
          3 | {b}   |     0
          1 | {a}   |     0
        (6 rows)

        Результат правильный, и в плане — индексный доступ:


        posts=# EXPLAIN (costs off)
        SELECT * FROM posts 
        WHERE tags <@ ARRAY['a','b','c','d']
        ORDER BY has_d <=> 1;
                           QUERY PLAN                   
        ------------------------------------------------
         Index Scan using posts_tags_has_d_idx on posts
           Index Cond: (tags <@ '{a,b,c,d}'::text[])
           Order By: (has_d <=> 1)
        (3 rows)

        Оно?


        1. linuxover
          05.12.2017 08:47

          Оно, спасибо за подробный пример, попробую обязательно!


          PS: а байду с триггером можно ли не разводить?


          Если свять IMMUTABLE has_d функцию:


          CREATE FUNCTION has_d(a TEXT[]) RETURNS INTEGER AS $$
          BEGIN
            IF a @> ARRAY['d'] THEN
                RETURN 1;
            END IF;
            RETURN 0;
          END;
          $$
          IMMUTABLE;

          то можно ли поправить WITH секцию на фукнциональный стайл?:


          
          CREATE INDEX ON posts
          USING rum(tags rum_anyarray_addon_ops, has_d(tags))
             WITH (attach='has_d', to='tags'); -- вот тут непонятно как с функцией влезть
          

          но в любом случае, можно или нельзя функцию использовать — огромное спасибо за развернутый пример :)


          1. erogov Автор
            05.12.2017 13:43

            Увы, сейчас в параметре attach ожидается имя столбца таблицы, ничего другого туда не подсунуть. Но я поговорю с разработчиками, возможно это получится изменить.


            1. linuxover
              05.12.2017 19:42

              > Но я поговорю с разработчиками, возможно это получится изменить.

              если имя функции передавать (принимающей весь рекорд таблицы), то наверно можно будет полностью функциональный индекс построить.
              иногда ведь и массива то может не быть, его аналог функций *tsvector может строить налету.


        1. linuxover
          05.12.2017 08:59

          Оно?

          круто. а если has_d было бы числом 0..N, то можно ли было бы по нему отсортировать?


          я правильно понимаю что если has_d у каждого будет свой, то оператор


          WHERE has_d <=> 27

          выдаст близкие, то есть сперва те у кого has_d = 27, потом те у кого has_d = 26 и 28? так?
          или потом только те у кого has_d = 26, а 28 нет?


          1. linuxover
            05.12.2017 09:47

            все, сам понял, да 26 и 28


            PS: опечатка WHERE/ORDER BY в вопросе :)
            сорри с телефона писал


            1. erogov Автор
              05.12.2017 13:48

              Да, так и есть. Но есть еще операторы |=> и <=|. Например, has_d <=| 27 выдаст 27, 26, 25, ..., 0, 28, 29, ...


        1. select_artur
          05.12.2017 17:41
          +1

          linuxover, если я правильно понял вопрос, то можно поступить чуть проще.

          =# create index on posts using rum(tags rum_anyarray_ops);
          =# select * from posts where tags <@ '{a,b,c,d}' order by tags <=> '{d}';
           id | tags
          ----+-------
            2 | {a,d}
            4 | {b,d}
            6 | {c,d}
            1 | {a}
            3 | {b}
            5 | {c}
          

          При этом способе сортировка также выполняется в индексе:
          =# set enable_sort to off;
          =# explain (costs off) select * from posts where tags <@ '{a,b,c,d}' order by tags <=> '{d}';
                           QUERY PLAN                  
          ---------------------------------------------
           Index Scan using posts_tags_idx on posts
             Index Cond: (tags <@ '{a,b,c,d}'::text[])
             Order By: (tags <=> '{d}'::text[])
          


          1. erogov Автор
            05.12.2017 20:56

            А ведь точно! В этом конкретном случае так, конечно, значительно проще и правильней.


            Артур, раз уж ты сюда пришел (: расскажи, реально ли для attach/to запилить поддержку выражений, а не только столбцов таблицы? Чтобы можно было функциональные индексы делать.


            1. select_artur
              06.12.2017 12:43

              Такую поддержку реально сделать ) Но ее пока нет.


          1. linuxover
            06.12.2017 08:55

            where tags <@ '{a,b,c,d}' order by tags <=> '{d}';

            не не правильно, на выходе нужны только <@ {a,b,c}, а только сортировка по порядку "начиная от тех кто еще и d имеет".


            то есть имеем облако меток например на хабре. Представим что некий анализатор статистики добавляет метки по мере просмотров "популярное", "сверхпопулярное" итп


            и когда пользователь ищет по меткам — ему выдавать выдачу "сперва очень популярное, потом просто популярное, а потом все остальное". То есть a,b,c — метки выданные пользователем, а d,e,f — добавленные движком. Поэтому в фильтре они не участвуют, а только в ORDER BY.
            Как-то так.


            Ну и в общем виде — решение erogov — обобщенное. Ему б еще функциндекс можно было б прикрутить — ваще круто бы было :D


            1. erogov Автор
              06.12.2017 12:16

              На свежую голову — и у меня, и у Артура на самом деле должно быть условие where tags && '{a,b,c}', а не where tags <@ '{a,b,c,d}'.


              1. linuxover
                06.12.2017 15:45

                то есть в WHERE может быть одно а в order by другое и будет из индекса нормально выбирать?

                надо будет попробовать. Разгребусь с текучкой — покопаюсь, спасибо :)


                1. erogov Автор
                  07.12.2017 12:40

                  Ну да, лишь бы условие в WHERE поддерживалось индексом, а в ORDER BY был бы <=>.


  1. linuxover
    05.12.2017 09:01

    еще вопрос: по администрированию: RUM пересобираем отдельно — ок.
    но вот например надо смигрировать с Pg 9.5.5 на 9.5.6, RUM пересобирать надо или нет?


    1. erogov Автор
      05.12.2017 13:55

      Версии, отличающиеся только последней цифрой, всегда совместимы, так что при переходе с 9.5.5 и 9.5.6 точно ничего не надо. Вот если с 9.5 на 9.6, то скорее всего придется пересобрать.