postgres view что и зачем
Postgres view что и зачем
CREATE VIEW — создать представление
Синтаксис
Описание
CREATE VIEW создаёт представление запроса. Создаваемое представление лишено физической материализации, поэтому указанный запрос будет выполняться при каждом обращении к представлению.
Команда CREATE OR REPLACE VIEW действует подобным образом, но если представление с этим именем уже существует, оно заменяется. Новый запрос должен выдавать те же столбцы, что выдавал запрос, ранее определённый для этого представления (то есть, столбцы с такими же именами должны иметь те же типы данных и следовать в том же порядке), но может добавить несколько новых столбцов в конце списка. Вычисления, в результате которых формируются столбцы представления, могут быть совершенно другими.
Параметры
С таким указанием представление создаётся как временное. Временные представления автоматически удаляются в конце сеанса. Существующее постоянное представление с тем же именем не будет видно в текущем сеансе, пока существует временное, однако к нему можно обратиться, дополнив имя указанием схемы.
Если в определении представления задействованы временные таблицы, представление так же создаётся как временное (вне зависимости от присутствия явного указания TEMPORARY ). RECURSIVE
Создаёт рекурсивное представление. Синтаксис
Для рекурсивного представления обязательно должен задаваться список с именами столбцов. имя
Имя создаваемого представления (возможно, дополненное схемой). имя_столбца
В этом предложении могут задаваться следующие необязательные параметры представления:
Этот параметр может принимать значение local (локально) или cascaded (каскадно) и равнозначен указанию WITH [ CASCADED | LOCAL ] CHECK OPTION (см. ниже). Изменить этот параметр у существующего представления с помощью ALTER VIEW нельзя. security_barrier ( boolean )
Этот параметр следует использовать, если представление должно обеспечивать защиту на уровне строк. За дополнительными подробностями обратитесь к Разделу 39.5.
Это указание управляет поведением автоматически изменяемых представлений. Если оно присутствует, при выполнении операций INSERT и UPDATE с этим представлением будет проверяться, удовлетворяют ли новые строки условию, определяющему представление (то есть, проверяется, будут ли новые строки видны через это представление). Если они не удовлетворяют условию, операция не будет выполнена. Если указание CHECK OPTION отсутствует, команды INSERT и UPDATE смогут создавать в этом представлении строки, которые не будут видны в нём. Поддерживаются следующие варианты проверки:
Новые строки проверяются только по условиям, определённым непосредственно в самом представлении. Любые условия, определённые в нижележащих базовых представлениях, не проверяются (если только в них нет указания CHECK OPTION ). CASCADED
Указание CHECK OPTION нельзя использовать с рекурсивными представлениями.
Замечания
Будьте аккуратны в определении представления, чтобы получить желаемые имена и типы столбцов. Например, такая команда:
Доступ к таблицам, задействованным в представлении, определяется правами владельца представления. В некоторых случаях это позволяет организовать безопасный, но ограниченный доступ к нижележащим таблицам. Однако учтите, что не все представления могут быть защищёнными; за подробностями обратитесь к Разделу 39.5. Функции, вызываемые в представлении, выполняются так, как будто они вызываются непосредственно из запроса, обращающегося к представлению. Поэтому пользователь представления должен иметь все права, необходимые для вызова всех функций, задействованных в представлении.
При выполнении CREATE OR REPLACE VIEW для существующего представления меняется только правило SELECT, определяющее представление. Другие свойства представления, включая владельца, права и правила, кроме SELECT, остаются неизменными. Чтобы изменить определение представления, необходимо быть его владельцем (или членом роли-владельца).
Изменяемые представления
Список FROM в запросе, определяющем представлении, должен содержать ровно один элемент, и это должна быть таблица или другое изменяемое представление.
Список выборки в запросе не должен содержать агрегатные и оконные функции, а также функции, возвращающие множества.
Автоматически обновляемое представление может содержать как изменяемые, так и не изменяемые столбцы. Столбец будет изменяемым, если это простая ссылка на изменяемый столбец нижележащего базового отношения; в противном случае этот столбец будет доступен только для чтения, и если команда INSERT или UPDATE попытается записать значение в него, возникнет ошибка.
Учтите, что пользователь, выполняющий операции добавления, изменения или удаления данных в представлении, должен иметь соответствующие права для этого представления. Кроме того, владелец представления должен иметь сопутствующие права в нижележащих базовых отношениях, хотя пользователь, собственно выполняющий эти операции, может этих прав не иметь (см. Раздел 39.5).
Примеры
Создание представления, содержащего все комедийные фильмы:
Создание представления с указанием LOCAL CHECK OPTION :
Создание представления с указанием CASCADED CHECK OPTION :
Создание представления с изменяемыми и неизменяемыми столбцами:
Создание рекурсивного представления, содержащего числа от 1 до 100:
Совместимость
38.2. Система правил и представления
Представления в PostgreSQL реализованы на основе системы правил. Фактически по сути нет никакого отличия
от следующих двух команд:
так как именно эти действия CREATE VIEW выполняет внутри. Это имеет некоторые побочные эффекты. В частности, информация о представлениях в системных каталогах PostgreSQL ничем не отличается от информации о таблицах. Поэтому при анализе запроса нет абсолютно никакой разницы между таблицами и представлениями. Они представляют собой одно и то же — отношения.
38.2.1. Как работают правила SELECT
Правила ON SELECT применяются ко всем запросам на последнем этапе, даже если это команда INSERT, UPDATE или DELETE. Эти правила отличаются от правил других видов тем, что они модифицируют непосредственно дерево запросов, а не создают новое. Поэтому мы начнём описание с правил SELECT.
В настоящее время возможно только одно действие в правиле ON SELECT и это должно быть безусловное действие SELECT, выполняемое в режиме INSTEAD. Это ограничение было введено, чтобы сделать правила достаточно безопасными для применения обычными пользователями, так что действие правил ON SELECT сводится к реализации представлений.
В примерах этой главы рассматриваются два представления с соединением, которые выполняют некоторые вычисления, и которые, в свою очередь, используются другими представлениями. Первое из этих двух представлений затем модифицируется, к нему добавляются правила для операций INSERT, UPDATE и DELETE, так что в итоге получается представление, которое работает как обычная таблица с некоторыми необычными функциями. Это не самый простой пример для начала, поэтому понять некоторые вещи будет сложнее. Но лучше иметь один пример, поэтапно охватывающий все обсуждаемые здесь темы, чем несколько различных, при восприятии которых в итоге может возникнуть путаница.
Например, нам нужна простейшая функция min, которая возвратит минимальное из двух целых чисел. Её можно создать так:
Таблицы, которые понадобятся нам для описания системы правил, выглядят так:
Как можно догадаться, в них хранятся данные обувной фабрики.
Представления создаются так:
Команда CREATE VIEW для представления shoelace (самого простого из имеющихся) создаёт отношение shoelace и запись в pg_rewrite о правиле перезаписи, которое должно применяться, когда в запросе на выборку задействуется отношение shoelace. Для этого правила не задаются условия применения (о них рассказывается ниже, в описании правил не для SELECT, так как правила SELECT в настоящее бывают только безусловными) и оно действует в режиме INSTEAD. Заметьте, что условия применения отличаются от условий фильтра запроса, например, действие для нашего правила содержит условие фильтра. Действие правила выражается одним деревом запроса, которое является копией оператора SELECT в команде, создающей представление.
Замечание: Два дополнительных элемента списка отношений NEW и OLD, которые можно увидеть в соответствующей строке pg_rewrite, не представляют интереса для правил SELECT.
Сейчас мы наполним таблицы unit (единицы измерения), shoe_data (данные о туфлях) и shoelace_data (данные о шнурках) и выполним простой запрос к представлению:
Это самый простой запрос SELECT, который можно выполнить с нашими представлениями, и мы воспользуемся этим, чтобы объяснить азы правил представлений. Запрос SELECT * FROM shoelace интерпретируется анализатором запросов и преобразуется в дерево запроса:
Это дерево передаётся в систему правил, которая проходит по списку отношений и проверяет, есть ли какие-либо правила для этих отношений. Обрабатывая элемент отношения shoelace (сейчас он единственный), система правил находит правило _RETURN с деревом запроса:
Чтобы развернуть представление, механизм перезаписи просто формирует новый элемент для списка отношений — подзапрос, содержащий дерево действия правила, и подставляет этот элемент вместо исходного, на который ссылалось представление. Получившееся перезаписанное дерево запроса будет почти таким как дерево запроса:
Однако есть одно различие: в списке отношений подзапроса будут содержаться два дополнительных элемента: shoelace old и shoelace new. Эти элементы не принимают непосредственного участия в запросе, так как они не задействованы в дереве соединения подзапроса и в целевом списке. Механизм перезаписи использует их для хранения информации о проверке прав доступа, которая изначально хранилась в элементе, указывающем на представление. Таким образом, исполнитель будет по-прежнему проверять, имеет ли пользователь необходимые права для доступа к представлению, хотя в перезаписанном запросе это представление не фигурирует непосредственно.
Так было применено первое правило. Система правил продолжит проверку оставшихся элементов списка отношений на верхнем уровне запроса (в данном случае таких элементов нет) и рекурсивно проверит элементы списка отношений в добавленном подзапросе, не ссылаются ли они на представления. (Но old и new разворачиваться не будут — иначе мы получили бы бесконечную рекурсию!) В этом примере для shoelace_data и unit нет правил перезаписи, так что перезапись завершается и результат, полученный выше, передаётся планировщику.
Сейчас мы хотим написать запрос, который выбирает туфли из имеющихся в данный момент, для которых есть подходящие шнурки (по цвету и длине) и число готовых пар больше или равно двум.
На этот раз анализатор запроса выводит такое дерево:
Первое правило применяется к представлению shoe_ready и в результате получается дерево запроса:
Подобным образом, правила для shoe и shoelace подставляются в список отношений, что даёт окончательное дерево запроса:
На практике планировщик будет сворачивать это дерево до двух уровней: команды нижнего уровня SELECT будут «подняты» к среднему SELECT, так как обрабатывать их отдельно нет необходимости. Но средний оператор SELECT не будет совмещён с верхним, так как он содержит агрегатные функции. Если поднять его выше, поведение самого верхнего SELECT изменится нежелательным образом. В целом же, сворачивание дерева запросов — это оптимизация, которая не должна затрагивать работу механизма перезаписи.
38.2.2. Правила представлений не для SELECT
До этого в описании правил представлений не затрагивались два компонента дерева запросов — тип команды и результирующее отношение. На самом деле, тип команды не важен для правил представления, но результирующее отношение может повлиять на работу механизма перезаписи, потому что если это представление, требуются дополнительные операции.
Есть только несколько отличий между деревом запроса для SELECT и деревом для другой команды. Очевидно, у них различные типы команд, и для команды, отличной от SELECT, результирующее отношение указывает на элемент в списке отношений, куда должен попасть результат. Все остальные компоненты в точности те же. Поэтому, например, если взять таблицы t1 и t2 с колонками a и b, деревья запросов для этих операторов:
будут практически одинаковыми. В частности:
Списки отношений содержат элементы для таблиц t1 и t2.
Выходные списки содержат одну переменную, указывающую на колонку b элемента-отношения для таблицы t2.
Выражения условий сравнивают колонки a обоих элементов-отношений на равенство.
Деревья соединений показывают простое соединение между t1 и t2.
Как следствие, для обоих деревьев строятся похожие планы выполнения, с соединением двух таблиц. Для UPDATE планировщик добавляет в выходной список недостающие колонки из t1 и окончательное дерево становится таким:
В результате исполнитель, обрабатывающий соединение, выдаёт тот же результат, что и запрос:
Но с UPDATE есть маленькая проблема: часть плана исполнителя, в которой выполняется соединение, не представляет, для чего предназначены результаты соединения. Она просто выдаёт результирующий набор строк. Фактически есть одна команда SELECT, а другая, UPDATE, обрабатывается исполнителем выше, где он уже знает, что это команда UPDATE и что результат должен попасть в таблицу t1. Но какие из строк таблицы должны заменяться новыми?
Зная всё это, мы можем применять правила представлений абсолютно таким же образом к любой команде — никаких различий нет.
38.2.3. Преимущества представлений в PostgreSQL
Выше было показано, как система правил внедряет определения представлений в исходное дерево запроса. Во втором примере простой запрос SELECT к одному представлению создал окончательное дерево запроса, соединяющее 4 таблицы (таблица unit использовалась дважды с разными именами).
Преимущество реализации представлений через систему правил заключается в том, что планировщик получает в одном дереве запроса всю информацию о таблицах, которые нужно прочитать, о том, как связаны эти таблицы, об условиях в представлениях, а также об условиях, заданных в исходном запросе. И всё это имеет место, когда сам исходный запрос представляет собой соединение представлений. Планировщик должен выбрать лучший способ выполнения запроса и чем больше информации он получит, тем лучше может быть его выбор. И то, как в PostgreSQL реализована система правил, гарантирует, что ему поступает вся информация, собранная о запросе на данный момент.
38.2.4. Изменение представления
Но что произойдёт, если записать имя представления в качестве целевого отношения команды INSERT, UPDATE или DELETE? Если проделать подстановки, описанные выше, будет получено дерево запроса, в котором результирующее отношение указывает на элемент-подзапрос, что не будет работать. Однако PostgreSQL даёт ряд возможностей, чтобы сделать представления изменяемыми.
Если подзапрос выбирает данные из одного базового отношения и он достаточно прост, механизм перезаписи может автоматически заменить его нижележащим базовым отношением, чтобы команды INSERT, UPDATE или DELETE обращались к базовому отношению. Представления, «достаточно простые» для этого, называются автоматически изменяемыми. Подробнее виды представлений, которые могут изменяться автоматически, описаны в CREATE VIEW.
Эту задачу также можно решить, создав триггер INSTEAD OF для представления. В этом случае перезапись будет работать немного по-другому. Для INSERT механизм перезаписи не делает с представлением ничего, оставляя его результирующим отношением запроса. Для UPDATE и DELETE ему по-прежнему придётся разворачивать запрос представления, чтобы получить «старые» строки, которые эта команда попытается изменить или удалить. Поэтому представление разворачивается как обычно, но в запрос добавляется ещё один элемент списка отношений, указывающий на представление в роли результирующего отношения.
Кроме того, пользователь может определить правила INSTEAD, в которых задать действия замены для команд INSERT, UPDATE и DELETE с представлением. Эти правила обычно преобразуют команду в другую команду, изменяющую одну или несколько таблиц, а не представление. Это тема следующего раздела.
Заметьте, что такие правила вычисляются сначала, перезаписывая исходный запрос до того, как он будет планироваться и выполняться. Поэтому, если для представления определены и триггеры INSTEAD OF, и правила для INSERT, UPDATE или DELETE, сначала вычисляются правила, а в зависимости от их действия, триггеры могут не вызываться вовсе.
Автоматическая перезапись запросов INSERT, UPDATE или DELETE с простыми представлениями всегда производится в последнюю очередь. Таким образом, если у представления есть правила или триггеры, они переопределяют поведение автоматически изменяемых представлений.
Если для представления не определены правила INSTEAD или триггеры INSTEAD OF, и запрос не удаётся автоматически переписать в виде обращения к нижележащему базовому отношению, возникает ошибка, потому что исполнитель не сможет изменить такое представление.
Postgres view что и зачем
КОМАНДА CREATE VIEW
Вы создаете представление командой CREATE VIEW. Она состоит из слов CREATE VIEW (СОЗДАТЬ ПРЕДСТАВЛЕНИЕ), имени представления которое нужно создать, слова AS (КАК), и далее запроса, как в следующем примере:
Теперь Вы имеете представление, называемое Londonstaff. Вы можете использовать это представление точно так же как и любую другую таблицу. Она может быть запрошена, модифицирована, вставлена в, удалена из, и соединена с, другими таблицами и представлениями. Давайте сделаем запрос такого представления ( вывод показан в Рисунке 20.1):
20.1 Рисунок 20.1: Представление Londonstaff
Рисунок 20.2: Представление Salesown
МОДИФИЦИРОВАНИЕ ПРЕДСТАВЛЕНИЙ
Представление может теперь изменяться командами модификации DML, но модификация не будет воздействовать на само представление. Команды будут на самом деле перенаправлены к базовой таблице:
Его действие идентично выполнению той же команды в таблице Продавцов. Однако, если значение комиссионных продавца будет обработано командой UPDATE
она будет отвергнута, так как поле comm отсутствует в представлении Salesown. Это важное замечание, показывающее что не все представления могут быть модифицированы. Мы будем исследовать проблемы модификации представлений в Главе 21.
ИМЕНОВАНИЕ СТОЛБЦОВ
В нашем примере, пол наших представлений имеют свои имена, полученные прямо из имен полей основной таблицы. Это удобно. Однако, иногда вам нужно снабжать ваши столбцы новыми именами:
Имена, которые могут стать именами полей, даются в круглых скобках ( ), после имени таблиц. Они не будут запрошены, если совпадают с именами полей запрашиваемой таблицы. Тип данных и размер этих полей будут отличаться от запрашиваемых полей которые «передаются» в них. Обычно вы не указываете новых имен полей, но если вы все таки сделали это, вы должны делать это для каждого пол в представлении.
КОМБИНИРОВАНИЕ ПРЕДИКАТОВ ПРЕДСТАВЛЕНИЙ И ОСНОВНЫХ ЗАПРОСОВ В ПРЕДСТАВЛЕНИЯХ
Если мы выполняем следующий запрос в этом представлении
он такой же как если бы мы выполнили следующее в таблице Продавцов:
Это прекрасно, за исключением того, что появляется возможна проблема с представлением. Имеется возможность комбинации из двух полностью допустимых предикатов и получения предиката который не будет работать. Например, предположим что мы создаем (CREATE) следующее представление:
Это дает нам число заказчиков которые мы имеем для каждого уровня оценки(rating). Вы можете затем сделать запрос этого представления чтобы выяснить, имеется ли какая-нибудь оценка, в настоящее время назначенная для трех заказчиков:
Посмотрим что случится если мы скомбинируем два предиката:
Это недопустимый запрос. Агрегатные функции, такие как COUNT (СЧЕТ), не могут использоваться в предикате. Правильным способом при формировании вышеупомянутого запроса, конечно же будет следующий:
ГРУППОВЫЕ ПРЕДСТАВЛЕНИЯ
Чем конструировать каждый раз сложный запрос, вы можете просто создать следующее представление:
Теперь вы сможете увидеть всю эту информацию с помощью простого запроса:
Как мы видели, SQL запросы могут дать вам полный комплекс возможностей, так что представления обеспечивают вас чрезвычайно гибким и мощным инструментом чтобы определить точно, как ваши данные могут быть использованы. Они могут также делать вашу работу более простой, переформатируя данные удобным для вас способом и исключив двойную работу.
ПРЕДСТАВЛЕНИЯ И ОБЪЕДИНЕНИЯ
Представления не требуют чтобы их вывод осуществлялся из одной базовой таблицы. Так как почти любой допустимый запрос SQL может быть использован в представлении, он может выводить информацию из любого числа базовых таблиц, или из других представлений. Мы можем, например, создать представление которое показывало бы, порядки продавца и заказчика по имени:
Теперь вы можете выбрать (SELECT) все порядки заказчика или продавца ( * ), или можете увидеть эту информацию для любого порядка. Например, чтобы увидеть все порядки продавца Rifkin, вы должны ввести следующий запрос ( вывод показан в 20.3 Рисунке ):
Рисунок 20.3: Порядки Rifkin показанные в Nameorders
Вы можете также объединять представления с другими таблицами, или базовыми таблицами или представлениями, поэтому вы можете увидеть все порядки Axelrodа и значения его комиссионных в каждом порядке:
Вывод для этого запроса показывается в Рисунке 20.4.
Рисунок 20. 4: Объединение основной таблицы с представлением
ПРЕДСТАВЛЕНИЯ И ПОДЗАПРОСЫ
Представления могут также использовать и подзапросы, включая соотнесенные подзапросы. Предположим ваша компания предусматривает премию для тех продавцов которые имеют заказчика с самым высоким порядком для любой указанной даты. Вы можете проследить эту информацию с помощью представления:
Если, с другой стороны, премия будет назначаться только продавцу который имел самый высокий порядок за последние десять лет, вам необходимо будет проследить их в другом представлении основанном на первом:
ЧТО НЕ МОГУТ ДЕЛАТЬ ПРЕДСТАВЛЕНИЯ
УДАЛЕНИЕ ПРЕДСТАВЛЕНИЙ
Синтаксис удаления представления из базы данных подобен синтаксису удаления базовых таблиц:
В этом нет необходимости, однако, сначала надо удалить все содержание как это делается с базовой таблицей, потому что содержание представления не является созданным и сохраняется в течении определенной команды. Базовая таблица из которой представление выводится, не эффективна когда представление удалено. Помните, вы должны являться владельцем представления чтобы иметь возможность удалить его.
РЕЗЮМЕ
Теперь, когда вы можете использовать представления, ваша способность отслеживать и обрабатывать содержание вашей базы данных, значительно расширилась. Любые вещи которые вы можете создать с запросом, вы всегда сможете определить как представление. Запросы этих представлений, фактически, запрос запроса. Использование представлений и для удобства и для защиты, также удобно как и многие возможности представлений для форматирования и получения значений из постоянно меняющегося содержания вашей базы данных. Имеется один главный вывод относительно представлений, это способность к модификации, которую мы выбрали в отличии от можете модифицировать представления также как и базовую таблицу, с помощью изменений применяемых к таблице из которой получается представление, но это не всегда возможно.
Обзор важнейших фич Postgres 9.3: материализованные представления
PostgreSQL 9.3 выйдет с довольно-таки крутой фичей, называющейся материализованные представления. Фича была разработан Кевином Гриттнером и не так давно закоммичена:
Добавлены материализованные представления
У материализованного представления есть правило, так же как и у обычного представления, и куча, а также другие физические свойства, как у таблицы. Правило используется только для наполнения таблицы, ссылки в запросах указывают на материализованные данные.
Реализована минимальная функциональность, но и она может быть полезной во многих случаях. В настоящее время данные загружаются только “по требованию” инструкциями CREATE MATERIALIZED VIEW и REFRESH MATERIALIZED VIEW. Ожидается, что в будущих релизах будут добавлены инкрементальные обновления данных с различными настройками времени обновления, и будет дано более четкое определение самому понятию “свежие” данные. В какой-то момент даже запросы смогут использовать материализованные данные вместо данных самих таблиц, но это требует реализации описанного выше функционала в первую очередь.
Большая часть работы по составлению документации проделал Robert Haas. Ревью: Noah Misch, Thom Brown, Robert Haas, Marko Tiikkaja. Ревью по вопросам безопасности, включающее решение о том, как лучше реализовать sepgsql, ожидается от KaiGai Kohei.
Что такое материализованное представление? Если коротко, то это мутант таблицы и обычного представления. Представление это проекция данных с помощью заданного отношения, не имеющее хранилища. Таблица это… таблица!
Материализованное представление лежит где-то посредине – это проекция табличных данных, имеющее собственное хранилище. Оно использует запрос для получения своих данных, как представление, но данные хранятся как в обычной таблице. Материализованное представление может быть обновлено свежими данными с помощью повторного выполнения запроса, использованного на этапе его создания. Кроме того, оно может быть очищено (truncated). В последнем случае оно остается в состоянии, не допускающем сканирования. Также, так как материализованное представление имеет свое собственное полноценное хранилище, оно может использовать табличные пространства (tablespace) и свои собственные индексы. Обратите внимание, на то, что оно может быть беспротокольным (unlogged) (прим. перев.: то есть данные не пишутся в write-ahead log).
Вместе с данной фичей вводятся 4 новые SQL-команды:
CREATE, ALTER и DROP – в данном случае это привычные DDL-команды для манипулирования определением представления. Наиболее же интересна команда REFRESH (по поводу ее названия были долгие споры внутри комьюнити). Эта команда может быть использована для обновления материализованного представления свежими данными повторным запуском сканирующего запроса. Обратите внимание на то, что REFRESH также может быть использован для очистки данных (truncate), хотя и не настоящей, с помощью запуска с опцией WITH NO DATA.
Материализованные представления имеют множество преимуществ в различных ситуациях: быстрый доступ к данным, которые должны быть получены с удаленного сервера (чтение файла на сервере postgres через file_fdw, и т.д.), использование периодически обновляемых данных (система кеширования), проекция данных с ORDER BY из больших таблиц, периодическое выполнение дорогих “JOIN”-ов в фоне и т.д.
Я уже могу представить себе несколько замечательных комбинаций из процедур обновления данных и фоновых воркеров. Кто вообще сказал, что автоматическое обновление данных в материализованных представлениях невозможно?
А теперь, давайте посмотрим как это работает:
postgres = # CREATE TABLE aa AS SELECT generate_series(1,1000000) AS a;
SELECT 1000000
postgres = # CREATE VIEW aav AS SELECT * FROM aa WHERE a
CREATE VIEW
postgres = # CREATE MATERIALIZED VIEW aam AS SELECT * FROM aa WHERE a
SELECT 500000
Размеры по каждому из отношений:
postgres = # SELECT pg_relation_size(‘aa’) AS tab_size, pg_relation_size(‘aav’) AS view_size, pg_relation_size(‘aam’) AS matview_size;
tab_size | view_size | matview_size
———-+————+—————
36249600 | 0 | 18137088
( 1 row )
Материализованное представление использует хранилище (в данном случае, 18Мб) в объеме, необходимом для хранения данных, выбранных из родительской таблицы (размером 36Мб) во время выполнения запроса на создание представления.
Обновление полученного представления осуществляется очень легко.
postgres = # DELETE FROM aa WHERE a
DELETE 500000
postgres = # SELECT count(*) FROM aam;
count
— 500000
( 1 row )
postgres = # REFRESH MATERIALIZED VIEW aam;
REFRESH MATERIALIZED VIEW
postgres = # SELECT count(*) FROM aam;
count
— 0
( 1 row )
Изменения в родительской таблицы отразились на материализованном представлении только после выполнения команды REFRESH. Обратите внимание, что на момент написания этой статьи, REFRESH использовал эксклюзивную блокировку (эх…).
Материализованное представление может быть переведено в несканируемое состояние с помощь опции WITH NO DATA команды REFRESH.
postgres = # REFRESH MATERIALIZED VIEW aam WITH NO DATA;
REFRESH MATERIALIZED VIEW
postgres = # SELECT count(*) FROM aam;
ERROR: materialized view «aam» has not been populated
HINT: Use the REFRESH MATERIALIZED VIEW command.
Появилась новая системная таблица matviews, которая содержи информацию о текущем состоянии материализованных представлений.
postgres = # SELECT matviewname, isscannable FROM pg_matviews;
matviewname | isscannable
————-+————-
aam | f
( 1 row )
Над материализованным представлением нельзя осуществлять DML-запросы, поскольку данные представления могут не соответствовать текущему значению родительской таблицы. Обычные представления же, наоборот, выполняют соответствующий им запрос каждый раз, когда это необходимо, поэтому через них возможна модификация родительских таблиц (updatable views).
postgres = # INSERT INTO aam VALUES (1);
ERROR: cannot change materialized view «aam»
postgres = # UPDATE aam SET a = 5;
ERROR: cannot change materialized view «aam»
postgres = # DELETE FROM aam;
ERROR: cannot change materialized view «aam»
Сейчас несколько слов об улучшении и ухудшении производительности, которое вы можете получить с использованием материализованных представлений (с учетом того, что вы можете манипулировать и их индексами). Например, можно очень легко улучшить производительность запросов на выборку в материализованных представлениях, совершенно не беспокоясь о схеме данных в родительской таблице:
postgres = # EXPLAIN ANALYZE SELECT * FROM aam WHERE a = 1;
QUERY PLAN
—————————————————————————————————
Seq Scan on aam ( cost =0.00..8464.00 rows = 1 width = 4 ) ( actual time =0.060..155.934 rows = 1 loops = 1 )
Filter: ( a = 1 )
Rows Removed by Filter: 499999
Total runtime: 156.047 ms
( 4 rows )
postgres = # CREATE INDEX aam_ind ON aam (a);
CREATE INDEX
postgres = # EXPLAIN ANALYZE SELECT * FROM aam WHERE a = 1;
QUERY PLAN
——————————————————————————————————————
Index Only Scan using aam_ind on aam ( cost =0.42..8.44 rows = 1 width = 4 ) ( actual time =2.096..2.101 rows = 1 loops = 1 )
Index Cond: ( a = 1 )
Heap Fetches: 1
Total runtime: 2.196 ms
( 4 rows )
Обратите внимание на то, что индексы и ограничения (материализованные представления могут иметь constraints!) родительской таблицы не копируются в материализованные представления. Например, быстрый запрос сканирующий первичный ключ таблицы может закончиться смертельно долгим последовательным перебором, будучи запущенным на материализованном представлении.
postgres = # INSERT INTO bb VALUES (generate_series(1,100000));
INSERT 0 100000
postgres = # EXPLAIN ANALYZE SELECT * FROM bb WHERE a = 1;
QUERY PLAN
——————————————————————————————————————
Index Only Scan using bb_pkey on bb ( cost =0.29..8.31 rows = 1 width = 4 ) ( actual time =0.078..0.080 rows = 1 loops = 1 )
Index Cond: ( a = 1 )
Heap Fetches: 1
Total runtime: 0.159 ms
( 4 rows )
postgres = # CREATE MATERIALIZED VIEW bbm AS SELECT * FROM bb;
SELECT 100000
postgres = # EXPLAIN ANALYZE SELECT * FROM bbm WHERE a = 1;
QUERY PLAN
—————————————————————————————————
Seq Scan on bbm ( cost =0.00..1776.00 rows = 533 width = 4 ) ( actual time =0.144..41.873 rows = 1 loops = 1 )
Filter: ( a = 1 )
Rows Removed by Filter: 99999
Total runtime: 41.935 ms
( 4 rows )