Thread: Re: [pgsql-ru-general] несколько вопросов новичка (ограничения и индексы)
Re: [pgsql-ru-general] несколько вопросов новичка (ограничения и индексы)
From
Dmitriy Igrishin
Date:
Приветствую, Дмитрий,
--
// Dmitriy.
21 февраля 2011 г. 9:28 пользователь Dmitry E. Oboukhov <unera@debian.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ов ? :-)
и её реализация - 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 ресурсов.
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.
памяти. Если на сервере оперативной памяти достаточно для размещения
индексов, то разницы в скорости нет, будь это хоть text, хоть integer.
Нормализация имеет смысл. Почему вообще такой вопрос встаёт?
Жалко создать таблицу? :-)
Жалко создать таблицу? :-)
--
... mpd is off
. ''`. 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
--
// Dmitriy.
DI> Проектная задача тривиальна - отношение "многие ко многим", как DI> и её реализация - 3 таблицы: server, resource и server_resource. Последняя DI> таблица (кстати, называйте как хотите, все равно это не сущность, DI> а лишь способ реализации "многих ко многим") содержит 2 столбца, DI> которые - суть внешние ключи - один "смотрит" в server, другой - DI> в resource. Первичный ключ данной таблицы как раз состоит из этих 2-х DI> столбцов. Полезно также создать индекс на тот столбец, который DI> входит в первичный ключ вторым (оптимизация). Первичный ключ DI> (он же, на самом деле, просто сочетание огранчений уникальности DI> и недопустимости значений NULL) и обеспечит ограничение целостости. DI> Уверяю, что реализовать лучшее ограничение целостности написанием DI> триггерной функции не удастся, как бы не старались. Да и зачем? DI> Оптимизация? Боязнь JOINов ? :-) нормализованный вариант понятен. суть в том что по логике приложения будет получаться join будет делаться *всегда*, соответственно отсюда и стремление к денормализации DI> Нормализация имеет смысл. Почему вообще такой вопрос встаёт? DI> Жалко создать таблицу? :-) опять та же оптимизация, если мы не делаем запроса без JOIN'а, следовательно есть смысл выкинуть его перенеся поля в основную таблицу. ну может мне MySQL'ный опыт мешает :) -- . ''`. 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
Attachment
Re: [pgsql-ru-general] несколько вопросов новичка (ограничения и индексы)
From
Dmitriy Igrishin
Date:
21 февраля 2011 г. 12:42 пользователь Dmitry E. Oboukhov <unera@debian.org> написал:
DI> Проектная задача тривиальна - отношение "многие ко многим", как
DI> и её реализация - 3 таблицы: server, resource и server_resource. Последняя
DI> таблица (кстати, называйте как хотите, все равно это не сущность,
DI> а лишь способ реализации "многих ко многим") содержит 2 столбца,
DI> которые - суть внешние ключи - один "смотрит" в server, другой -
DI> в resource. Первичный ключ данной таблицы как раз состоит из этих 2-х
DI> столбцов. Полезно также создать индекс на тот столбец, который
DI> входит в первичный ключ вторым (оптимизация). Первичный ключ
DI> (он же, на самом деле, просто сочетание огранчений уникальности
DI> и недопустимости значений NULL) и обеспечит ограничение целостости.
DI> Уверяю, что реализовать лучшее ограничение целостности написанием
DI> триггерной функции не удастся, как бы не старались. Да и зачем?
DI> Оптимизация? Боязнь JOINов ? :-)
нормализованный вариант понятен. суть в том что по логике приложения
будет получаться join будет делаться *всегда*, соответственно отсюда и
стремление к денормализации
В любом случае, это не правильное стремление. Нормальные формы
позволяют минимизировать избыточность. Чем не оптимизиация?
Рассматривать оптимизацию только с одной стороны - "скорость выборки" -
это не правильно. Да и вообще, как можно что-то ускорять, не
проведя массу тестов, результаты которых окажутся неудовлетворительными?
"Оптимизация", ведущая к деградации дизайна БД, неразумна.
Если так уж не хочется выполнять аггрегацию данных при каждой выборке,
можно хранить готовый массив, например, в таблице server, который
генерируется каждый раз при выполнении операций над таблицей
server_resource. Для решения этой задачи потребуется написать
триггерную функцию и создать триггер на таблицу server_resource.
Этот массив следует расценивать как некий кэш.
позволяют минимизировать избыточность. Чем не оптимизиация?
Рассматривать оптимизацию только с одной стороны - "скорость выборки" -
это не правильно. Да и вообще, как можно что-то ускорять, не
проведя массу тестов, результаты которых окажутся неудовлетворительными?
"Оптимизация", ведущая к деградации дизайна БД, неразумна.
Если так уж не хочется выполнять аггрегацию данных при каждой выборке,
можно хранить готовый массив, например, в таблице server, который
генерируется каждый раз при выполнении операций над таблицей
server_resource. Для решения этой задачи потребуется написать
триггерную функцию и создать триггер на таблицу server_resource.
Этот массив следует расценивать как некий кэш.
DI> Нормализация имеет смысл. Почему вообще такой вопрос встаёт?
DI> Жалко создать таблицу? :-)
опять та же оптимизация, если мы не делаем запроса без JOIN'а,
следовательно есть смысл выкинуть его перенеся поля в основную
таблицу.
ну может мне MySQL'ный опыт мешает :)
Если это так уж критично, то лучше определить 2-х столбцовый
внешний ключ (например - id, name) в дочерней таблице и
установить режим каскадного обновления данных (ON UPDATE
CASCADE).
внешний ключ (например - id, name) в дочерней таблице и
установить режим каскадного обновления данных (ON UPDATE
CASCADE).
--. ''`. 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
--
// Dmitriy.