Postgresql — индекс отдельных элементов массива или индекс ключей в hstore

У меня есть таблица пользователей со столбцом uuid и тегами столбца:

| uuid varchar PRIMARY KEY | tags ????? |

Я не уверен, какого типа должен быть столбец tags, hstore или varchar[]. Я хочу, чтобы он содержал список интересов или категорий, таких как «гамбургеры» или «овощи», чтобы я мог запрашивать всех пользователей, у которых есть какие-либо теги в указанном массиве (например, «Какие пользователи любят какие-либо из« бургеров »« овощи ''хот-доги'?") Чтобы этот запрос был быстрым, я думаю, мне следует индексировать отдельные категории, как бы они ни хранились. Я ожидаю, что у большинства пользователей будет небольшое количество тегов (0-5), но потенциально у них может быть до 100 или около того. И есть много разных вариантов тегов (может быть 1000+).

Я считаю, что могу проиндексировать ключи в hstore, чтобы знать, что тип hstore является вариантом. Можно ли индексировать отдельные элементы varchar массивов? (Я видел сообщения об этом, но они были неубедительными.)

Постгрес версии 9.3.5


postgresql arrays indexing hstore
person tscizzle    schedule 19.02.2015    source источник
comment
Вы действительно продумывали вариант использования отдельной таблицы для тегов? Я реализовал теги в корпоративной производственной среде и настоятельно рекомендую использовать отдельную таблицу или таблицы. Например, одна таблица с сопоставлениями пользовательских тегов и, возможно, отдельная таблица для тегов (которая дает каждому имени тега идентификатор), если вам нужно быть уверенным, что вы можете сгруппировать по имени тега. Обычно в среде базы данных такие решения для массивов лучше всего реализовать в отдельных таблицах.   -  person Simo Kivistö    schedule 19.02.2015
comment
Я об этом не подумал! Можете ли вы объяснить это (или ссылку)? Звучит как солидная рекомендация.   -  person tscizzle    schedule 19.02.2015

Ответы (1)


arrow_upward
2
arrow_downward

Я бы рекомендовал отдельные таблицы для тегов

У вас уже есть пользователи таблицы с uuid, скажем:

CREATE TABLE users (
    uuid serial primary key,
    user_name text
);

Теперь теги:

CREATE TABLE tags (
    tag_id serial primary key,
    tag_name text
);

CREATE TABLE users_tags (
    uuid integer references users,
    tag_id integer references tags,
    primary key (uuid, tag_id)
);

Теперь вы можете легко запросить, например:

SELECT * FROM users
    JOIN users_tags USING (uuid)
    JOIN tags USING (tag_id)
    WHERE tag_name = 'Burgers';

Теперь вы можете легко добавить индекс к tag_name. Вы также можете легко обеспечить уникальность имени тега или создать уникальный индекс для нижнего (имя_тега), что устранит проблемы с заглавными буквами в именах тегов (Burgers vs. BurgerS).

Более простым решением было бы оставить таблицу тегов и просто создать:

CREATE TABLE users_tags (
    uuid integer references users,
    tag_name text,
    primary key (uuid, tag_name)
);

Создаете ли вы отдельную таблицу для тегов или просто используете user_tags -table, в основном зависит от того, как используются теги. Отдельная таблица необходима, если у вас есть (в основном) определенный набор тегов, и вы, возможно, захотите добавить информацию к определенному имени тега позже. Запрос «какие пользователи любят хот-доги» будет предлагать отдельный тег, в котором тег «хот-дог» имеет определенный идентификатор. Если пользователи могут свободно добавлять всевозможные мутные теги, и к ним позже не будет прикреплена информация, тогда оставьте отдельную таблицу.

person Simo Kivistö    schedule 19.02.2015
comment
Потрясающий! Мой вариант использования имеет смысл для последнего, используя только таблицу users_tags. Может ли primary key быть (uuid, tag_name) вместо (uuid, tag_id)? - person tscizzle; 19.02.2015
comment
И последнее (я думаю), если я хочу найти все uuid, связанные с таким тегом, как «овощи», я могу сделать простое "SELECT uuid FROM users INNER JOIN users_tags USING uuid ON users.uuid = users_tags.uuid WHERE users_tags.tag_name = 'vegetables'", но чтобы сделать это очень быстро, какой индекс мне нужно добавить? Обычный ('btree') на users_tags(tag_name)? - person tscizzle; 19.02.2015
comment
И хотел бы я также иметь такой на users_tags(uuid)? - person tscizzle; 19.02.2015
comment
Я думаю, что создание первичного ключа создаст уникальный индекс, состоящий из двух частей, который будет использоваться в вашем конкретном запросе (вы можете проверить это, поместив объяснение перед select), но индекс btree для tag_name, вероятно, потребуется и для других целей. - person Simo Kivistö; 19.02.2015