Как можно оптимизировать SQL запрос?

Question

[MZhack]

Здравствуйте! Имеется небольшой сайт, для бд используется PostgreSQL 9.6. Появилась необходимость реализовать авторизацию, через роли в PostgreSQL, по максимуму используя только штатные инструменты. Придумано и реализованно, что роль и пароль в PG - это же и логин и пароль на сайте, групповая роль назначенная одиночной роли - это же группа пользователя на сайте (условно Admin, Operator, User, Guest), каждая, разумеется, со своими привелегиями как в pg, так на и сайте. Комментарий к одиночной роле - это отображаемый юзернейм или ФИО пользователя, комментарий для групповой роли - это отображаемое название группы пользователей.

Было сделанно представление, которое склеивает вместе login, user_name и user_group.

SELECT a.rolname AS login,
    t1.description AS name,
    t2.description AS "group"
   FROM pg_shdescription t1
     JOIN pg_auth_members m ON t1.objoid = m.member
     JOIN pg_shdescription t2 ON m.roleid = t2.objoid
     JOIN pg_authid a ON t1.objoid = a.oid;

Вопросы к знатокам: насколько оптимально такое решение? Будет ли какая-то повышенная нагрузка? Если да, то как можно оптимизировать? И надо ли?

Comments

[Everything_is_bad]

1. чтобы что-то оптимизировать, это что-то минимум нужно замерить, в постгресе это explain analyze
2. 9.6 это уже совсем старье
3.

Придумано и реализованно, что роль и пароль в PG - это же и логин и пароль на сайте

очень сомнительное решение.

Answer 1

[Akina]

насколько оптимально такое решение?

Представление в Постгрессе - чисто динамическая конструкция. Т.е. указанный запрос будет выполняться заново каждый раз, когда запрашивается его информация. С другой стороны, используемые таблицы достаточно компактны и почти стопроцентно кэшированы, так что вряд ли это может заметно сказаться на производительности.

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

Актуализация данных таблицы (точнее, её полный пересчёт) выполняется из триггерной процедуры на каждое событие изменения любой из используемых в запросе таблиц, если изменение связано с вашей системой (заодно можно выпилить оттуда не связанные с приложением записи). Поскольку работа по изменению указанных данных достаточно редкая по сравнению с запросами на получение данных, такая схема может дать определённый профит. Однако, поскольку процесс предусматривает полную очистку таблицы и заполнение её с нуля, следует избегать интерференции со стороны двух конкурентных процессов изменения. Для полной гарантии любые изменения следует проводить в изолирующей транзакции.

Хотя как по мне, следует отделить систему безопасности Постгресса от авторизации в приложении. Мнение по данному вопросу от Everything_is_bad в комментарии - это не сарказм, а весьма правильное замечание.