Comments 23
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)Оно?
=# 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[])
А ведь точно! В этом конкретном случае так, конечно, значительно проще и правильней.
Артур, раз уж ты сюда пришел (: расскажи, реально ли для attach/to запилить поддержку выражений, а не только столбцов таблицы? Чтобы можно было функциональные индексы делать.
На свежую голову — и у меня, и у Артура на самом деле должно быть условие where tags && '{a,b,c}', а не where tags <@ '{a,b,c,d}'.
Интересно было бы узнать, какова судьба этого расширения. Забросили ли? В ванильном PostgreSQL как был только GIN, так и остался, несмотря на то, что RUM имеет очевидные преимущества. Также этого расширения нет в списке AWS Aurora, а там есть практически все (даже wal2json какой-нибудь).
Индексы в PostgreSQL — 8