Re: [pgsql-ru-general] несколько вопросов новичка (ограничения и индексы)

Приветствую, Дмитрий,

21 февраля 2011 г. 9:28 пользователь Dmitry E. Oboukhov
<unera(at)debian(dot)org>написал:

> есть табличка
>
> servers:
>
> | id | name |
>
> есть другая табличка в которой каждая запись может иметь отношение к
> нескольким серверам, однако выборок кроме как статистики не
> планируется, соответственно хочется сделать в ней такой вот столбик:
>
> resources:
>
> | id | some column | ... | server_id[] |
>
> то есть денормализованный вариант который позволяет без join'ов
> отвечать на вопрос: на каких еще серверах лежит данный ресурс?
>
>
> Вопрос: как написать ограничение для столбика server_id[] чтобы
>
> 1. в нем проводилась проверка валидности укладываемых в него ID
> 2. чтобы эта проверка не убила профит от денормализации по накладным
> расходам
>
Проектная задача тривиальна - отношение "многие ко многим", как
и её реализация - 3 таблицы: server, resource и server_resource. Последняя
таблица (кстати, называйте как хотите, все равно это не сущность,
а лишь способ реализации "многих ко многим") содержит 2 столбца,
которые - суть внешние ключи - один "смотрит" в server, другой -
в resource. Первичный ключ данной таблицы как раз состоит из этих 2-х
столбцов. Полезно также создать индекс на тот столбец, который
входит в первичный ключ вторым (оптимизация). Первичный ключ
(он же, на самом деле, просто сочетание огранчений уникальности
и недопустимости значений NULL) и обеспечит ограничение целостости.
Уверяю, что реализовать лучшее ограничение целостности написанием
триггерной функции не удастся, как бы не старались. Да и зачем?
Оптимизация? Боязнь JOINов ? :-)

>
>
> и еще: можно ли выборкой из второй и первой таблицы получить результат
> вида:
>
> | id | some column | ... | server_name[] |
>
> Я с массивами в столбиках пока еще плаваю :)
>
В вышеописанном варианте этого добиться легко, например
SELECT serverid, array_agg(resource.id)
FROM server_resource JOIN resource ON (resource = id);
Результатом будет выборка, в каждой строке которой
будет id сервера и массив id ресурсов.

>
>
> И еще вопрос по индексам.
>
> есть табличка
>
> | id | ... | type::varchar(16) |
>
> по полю type построен BTREE индекс. Значение в поле type по сути -
> ENUM. Однако варианты иногда таки меняются и альтертаблить не хочется.
> Так же во всех выборках нужно строго строковое значение,
> соответственно нормализовывать тоже не хочется.
>
> вопрос: индекс по varchar(16) vs индекс по ENUM (или INT) насколько у
> них будет большая разница в скорости работы?
>
ну места сама таблица будет конечно больше занимать, но места вроде не
> жалко. имеет ли большой смысл нормализация?
>
Части индексов для быстрой работы размещаются в оперативной
памяти. Если на сервере оперативной памяти достаточно для размещения
индексов, то разницы в скорости нет, будь это хоть text, хоть integer.

Нормализация имеет смысл. Почему вообще такой вопрос встаёт?
Жалко создать таблицу? :-)

> --
> ... mpd is off
>
> . ''`. Dmitry E. Oboukhov
> : :’ : email: unera(at)debian(dot)org jabber://UNera(at)uvw(dot)ru
> `. `~’ GPGKey: 1024D / F8E26537 2006-11-21
> `- 1B23 D4F8 8EC0 D902 0555 E438 AB8C 00CF F8E2 6537
>

--
// Dmitriy.