Представления и материализованные представления

Андрей Аксенов

Contents

Предварительные требования
Представления
Материализованные представления

Представление — это именованный SQL-запрос, хранящийся в базе данных. Представления позволяют сохранять часто используемые или сложные запросы и использовать их в запросах SELECT так же, как и обычные таблицы. Представления позволяют создать слой абстракции над таблицами: даже если структура таблиц меняется, приложения и пользователи продолжают работать через представление без изменений. Представление можно создать на основе одной или нескольких таблиц, а также других представлений.

Материализованное представление хранит как SQL-запрос, так и результаты его выполнения в структуре, похожей на таблицу. Это может значительно повысить производительность запросов, особенно для ресурсоемких операций. В отличие от обычных представлений, материализованные не обновляются автоматически — их нужно обновлять вручную, чтобы данные оставались актуальными.

ПРИМЕЧАНИЕ

Greengage DB включает встроенные системные представления. Например, представления gp_toolkit.gp_log_* позволяют выполнять SQL-запросы к серверным логам, а pg_catalog.pg_settings отображает параметры конфигурации.

Для выполнения команд, описанных в следующих разделах, подключитесь к мастер-хосту Greengage DB с помощью psql, как описано в статье Подключение к Greengage DB с использованием psql. Создайте новую базу данных и подключитесь к ней:

CREATE DATABASE media_library;
\c media_library

Затем создайте таблицу и заполните ее данными:

Создание таблицы

CREATE TABLE movies
(
    id     INTEGER,
    title  TEXT NOT NULL,
    genre  TEXT NOT NULL,
    year   INT,
    rating NUMERIC(3, 1)
)
    WITH (appendoptimized = true, orientation = row)
    DISTRIBUTED BY (id);

INSERT INTO movies (id, title, genre, year, rating)
SELECT gs                                                                             AS id,
       'Movie ' || gs                                                                 AS title,
       (ARRAY ['Sci-Fi','Crime','Romance','Action','Drama'])[(random() * 4)::int + 1] AS genre,
       (1925 + (random() * 100)::int)                                                 AS year,
       round((7.0 + random() * 3.0)::numeric, 1)                                      AS rating
FROM generate_series(1, 1000) gs;

Чтобы создать представление, используйте команду CREATE VIEW. Чтобы изменить определение существующего представления, используйте ALTER VIEW.

Ниже приведен упрощенный синтаксис команды CREATE VIEW:

CREATE [ TEMP ] VIEW <name>
    AS <query>

Основные выражения и аргументы для определения представления:

TEMP

Создает временное представление, которое существует только во время сессии. Если представление ссылается на временную таблицу, оно создается как временное — независимо от того, указан TEMP или нет.
<name>

Имя представления (при необходимости с указанием схемы), которое будет создано.
<query>

Запрос SELECT или VALUES, который определяет строки и столбцы представления. Этот SQL-запрос формирует данные, которые возвращаются при обращении к представлению.

Эта команда создает представление, которое отображает общее количество фильмов и средний рейтинг для каждого жанра:

CREATE VIEW avg_rating_by_genre AS
SELECT genre,
       COUNT(*)              AS total_movies,
       ROUND(AVG(rating), 2) AS avg_rating
FROM movies
GROUP BY genre;

Вы можете выполнять запросы к этому представлению как к обычной таблице с помощью SELECT:

SELECT *
FROM avg_rating_by_genre;

Результат должен выглядеть следующим образом:

  genre  | total_movies | avg_rating
---------+--------------+------------
 Romance |          265 |       8.49
 Drama   |          129 |       8.45
 Sci-Fi  |          117 |       8.38
 Action  |          240 |       8.59
 Crime   |          249 |       8.51
(5 rows)

Эта команда создает другое представление на основе таблицы movies, которое возвращает общее количество фильмов и средний рейтинг для каждого десятилетия:

CREATE VIEW movie_stats_by_decade AS
SELECT (year / 10) * 10      AS decade,
       COUNT(*)              AS total_movies,
       ROUND(AVG(rating), 2) AS avg_rating
FROM movies
GROUP BY decade
ORDER BY decade;

ПРИМЕЧАНИЕ

Если и CREATE VIEW <view> …, и SELECT … FROM <view> содержат выражение ORDER BY, Greengage DB игнорирует выражение ORDER BY в команде CREATE VIEW.

Следующая команда создает вложенное представление, которое выбирает три жанра с самой высокой средней оценкой на основе ранее определенного представления avg_rating_by_genre:

CREATE VIEW top_3_genres AS
SELECT genre, avg_rating
FROM (SELECT genre,
             avg_rating,
             RANK() OVER (ORDER BY avg_rating DESC) AS genre_rank
      FROM avg_rating_by_genre) ranked
WHERE genre_rank <= 3;

Приведенный ниже запрос SELECT возвращает все строки и столбцы из созданного представления:

SELECT *
FROM top_3_genres;

Результат должен выглядеть следующим образом:

  genre  | avg_rating
---------+------------
 Action  |       8.59
 Crime   |       8.51
 Romance |       8.49
(3 rows)

Вы можете использовать метакоманду \dv или \dv+, чтобы получить список представлений, например:

\dv

Результат выглядит следующим образом:

                List of relations
 Schema |         Name          | Type |  Owner
--------+-----------------------+------+---------
 public | avg_rating_by_genre   | view | gpadmin
 public | movie_stats_by_decade | view | gpadmin
 public | top_3_genres          | view | gpadmin
(3 rows)

Метакоманда \d+ возвращает информацию о представлении avg_rating_by_genre:

\d+ avg_rating_by_genre

Результат должен включать определение представления:

              View "public.avg_rating_by_genre"
    Column    |  Type   | Modifiers | Storage  | Description
--------------+---------+-----------+----------+-------------
 genre        | text    |           | extended |
 total_movies | bigint  |           | plain    |
 avg_rating   | numeric |           | main     |
View definition:
 SELECT movies.genre,
    count(*) AS total_movies,
    round(avg(movies.rating), 2) AS avg_rating
   FROM movies
  GROUP BY movies.genre;

Вы можете выполнить запрос к представлению pg_views, чтобы получить список пользовательских представлений:

SELECT schemaname, viewname, definition
FROM pg_views
WHERE schemaname NOT IN ('pg_catalog', 'information_schema', 'gp_toolkit')
ORDER BY schemaname, viewname;

Результат запроса должен включать определение каждого представления:

 schemaname |       viewname        |                                      definition
------------+-----------------------+--------------------------------------------------------------------------------------
 public     | avg_rating_by_genre   |  SELECT movies.genre,                                                               +
            |                       |     count(*) AS total_movies,                                                       +
            |                       |     round(avg(movies.rating), 2) AS avg_rating                                      +
            |                       |    FROM movies                                                                      +
            |                       |   GROUP BY movies.genre;
 public     | movie_stats_by_decade |  SELECT ((movies.year / 10) * 10) AS decade,                                        +
            |                       |     count(*) AS total_movies,                                                       +
            |                       |     round(avg(movies.rating), 2) AS avg_rating                                      +
            |                       |    FROM movies                                                                      +
            |                       |   GROUP BY ((movies.year / 10) * 10)                                                +
            |                       |   ORDER BY ((movies.year / 10) * 10);
 public     | top_3_genres          |  SELECT ranked.genre,                                                               +
            |                       |     ranked.avg_rating                                                               +
            |                       |    FROM ( SELECT avg_rating_by_genre.genre,                                         +
            |                       |             avg_rating_by_genre.avg_rating,                                         +
            |                       |             rank() OVER (ORDER BY avg_rating_by_genre.avg_rating DESC) AS genre_rank+
            |                       |            FROM avg_rating_by_genre) ranked                                         +
            |                       |   WHERE (ranked.genre_rank <= 3);
(3 rows)

Чтобы узнать, какие представления напрямую зависят от таблицы movies, выполните SQL-запрос, который объединяет системные таблицы с информацией о зависимостях и выбирает только представления (relkind = 'v'):
```
SELECT DISTINCT v.oid::regclass      AS view,
                d.refobjid::regclass AS ref_object
FROM pg_depend AS d
         JOIN pg_rewrite AS r ON r.oid = d.objid
         JOIN pg_class AS v ON v.oid = r.ev_class
WHERE v.relkind = 'v'
  AND d.classid = 'pg_rewrite'::regclass
  AND d.deptype = 'n'
  AND d.refclassid = 'pg_class'::regclass
  AND d.refobjid = 'movies'::regclass;
```
DISTINCT используется, чтобы избежать повторов строк, когда представление ссылается на несколько столбцов одной таблицы, так как pg_depend создает запись для каждого столбца. Результат выглядит так:
```
         view          | ref_object
-----------------------+------------
 movie_stats_by_decade | movies
 avg_rating_by_genre   | movies
(2 rows)
```

Вы можете изменить предыдущий запрос, чтобы найти представления, зависящие от конкретного столбца — это полезно, если вы планируете удалить этот столбец:

SELECT v.oid::regclass      AS view,
       d.refobjid::regclass AS ref_object,
       a.attname            AS col_name
FROM pg_attribute AS a
         JOIN pg_depend AS d
              ON d.refobjsubid = a.attnum AND d.refobjid = a.attrelid
         JOIN pg_rewrite AS r
              ON r.oid = d.objid
         JOIN pg_class AS v
              ON v.oid = r.ev_class
WHERE v.relkind = 'v'
  AND d.classid = 'pg_rewrite'::regclass
  AND d.refclassid = 'pg_class'::regclass
  AND d.deptype = 'n'
  AND a.attrelid = 'movies'::regclass
  AND a.attname = 'genre';

Результат показывает, что только одно представление зависит от столбца genre в таблице movies:

        view         | ref_object | col_name
---------------------+------------+----------
 avg_rating_by_genre | movies     | genre
(1 row)

Приведенный ниже SQL-запрос выводит информацию о представлениях, которые ссылаются на другие представления. Выражение WITH получает все представления из пользовательских схем, а основной SELECT находит представления, которые ссылаются на другие представления:

WITH views AS (SELECT v.relname  AS view,
                      d.refobjid AS ref_object,
                      v.oid      AS view_oid,
                      ns.nspname AS namespace
               FROM pg_depend AS d
                        JOIN pg_rewrite AS r
                             ON r.oid = d.objid
                        JOIN pg_class AS v
                             ON v.oid = r.ev_class
                        JOIN pg_namespace AS ns
                             ON ns.oid = v.relnamespace
               WHERE v.relkind = 'v'
                 AND ns.nspname NOT IN ('pg_catalog', 'information_schema', 'gp_toolkit')
                 AND d.deptype = 'n'
                 AND NOT (v.oid = d.refobjid))
SELECT DISTINCT views.view,
                views.namespace            AS schema,
                views.ref_object::regclass AS ref_view,
                ref_nspace.nspname         AS ref_schema
FROM views
         JOIN pg_depend as dep
              ON dep.refobjid = views.view_oid
         JOIN pg_class AS class
              ON views.ref_object = class.oid
         JOIN pg_namespace AS ref_nspace
              ON class.relnamespace = ref_nspace.oid
WHERE class.relkind = 'v'
  AND dep.deptype = 'n';

Результат должен включать представление top_3_genres:

     view     | schema |      ref_view       | ref_schema
--------------+--------+---------------------+------------
 top_3_genres | public | avg_rating_by_genre | public
(1 row)

Чтобы удалить представление, используйте команду DROP VIEW:

DROP VIEW movie_stats_by_decade;

Чтобы удалить представление вместе со связанными объектами, например, зависимыми представлениями, укажите параметр CASCADE:

DROP VIEW avg_rating_by_genre CASCADE;

Чтобы создать материализованное представление, используйте команду CREATE MATERIALIZED VIEW. Чтобы изменить определение существующего материализованного представления, используйте ALTER MATERIALIZED VIEW.

Ниже приведен упрощенный синтаксис команды CREATE MATERIALIZED VIEW:

CREATE MATERIALIZED VIEW <name>
    AS <query>
    [ WITH [ NO ] DATA ]
    [ DISTRIBUTED ... ]

Основные выражения и аргументы для определения материализованного представления следующие:

<name>

Имя материализованного представления (при необходимости с указанием схемы), которое будет создано.
<query>

Запрос SELECT или VALUES, который формирует данные, сохраняемые в материализованном представлении.
WITH [ NO ] DATA

Указывает, должно ли материализованное представление быть заполнено данными при создании. WITH DATA используется по умолчанию и заполняет материализованное представление.
DISTRIBUTED

Тип распределения, определяющий, как строки материализованного представления распределяются по сегментам кластера Greengage DB. Узнайте подробнее в статье Распределение данных.

Эта команда создает материализованное представление, которое отображает общее количество фильмов и средний рейтинг для каждого жанра:

CREATE MATERIALIZED VIEW avg_rating_by_genre_mat AS
SELECT genre,
       COUNT(*)              AS total_movies,
       ROUND(AVG(rating), 2) AS avg_rating
FROM movies
GROUP BY genre
DISTRIBUTED BY (genre);

Вы можете выполнять запросы к этому материализованному представлению как к обычной таблице с помощью SELECT:

SELECT *
FROM avg_rating_by_genre_mat
ORDER BY avg_rating DESC;

Результат должен выглядеть так:

  genre  | total_movies | avg_rating
---------+--------------+------------
 Action  |          240 |       8.59
 Crime   |          249 |       8.51
 Romance |          265 |       8.49
 Drama   |          129 |       8.45
 Sci-Fi  |          117 |       8.38
(5 rows)

Чтобы обновить материализованное представление, используйте команду REFRESH MATERIALIZED VIEW. Можно настроить автоматическое обновление представления с использованием этой команды.

Команда ниже вставляет дополнительные данные в исходную таблицу movies, а именно фильмы с рейтингами от 9.0 до 10.0:

INSERT INTO movies (id, title, genre, year, rating)
SELECT gs                                                                             AS id,
       'Movie ' || gs                                                                 AS title,
       (ARRAY ['Sci-Fi','Crime','Romance','Action','Drama'])[(random() * 4)::int + 1] AS genre,
       (1925 + (random() * 100)::int)                                                 AS year,
       round((7.0 + random() * 3.0)::numeric, 1)                                      AS rating
FROM generate_series(1, 1000) gs;

Это должно повысить средний рейтинг для каждого жанра примерно с 8.5 до 9.0. Используйте оператор SELECT для выполнения запроса к материализованному представлению:

SELECT *
FROM avg_rating_by_genre_mat
ORDER BY avg_rating DESC;

Результат должен показать, что значения в столбце avg_rating не изменились:

  genre  | total_movies | avg_rating
---------+--------------+------------
 Action  |          240 |       8.59
 Crime   |          249 |       8.51
 Romance |          265 |       8.49
 Drama   |          129 |       8.45
 Sci-Fi  |          117 |       8.38
(5 rows)

Обновите данные материализованного представления с помощью REFRESH MATERIALIZED VIEW:

REFRESH MATERIALIZED VIEW avg_rating_by_genre_mat;

Затем снова выполните запрос к материализованному представлению:

SELECT *
FROM avg_rating_by_genre_mat
ORDER BY avg_rating DESC;

Результаты показывают, что значения avg_rating обновились и теперь близки к 9.0:

  genre  | total_movies | avg_rating
---------+--------------+------------
 Action  |          483 |       9.05
 Crime   |          517 |       9.02
 Drama   |          262 |       8.96
 Romance |          490 |       8.96
 Sci-Fi  |          248 |       8.94
(5 rows)

Используйте метакоманду \dm или \dm+, чтобы получить список материализованных представлений:

\dm+

Результат выглядит следующим образом:

                                   List of relations
 Schema |          Name           |       Type        |  Owner  |  Size  | Description
--------+-------------------------+-------------------+---------+--------+-------------
 public | avg_rating_by_genre_mat | materialized view | gpadmin | 256 kB |
(1 row)

Метакоманда \d+ выводит информацию о материализованном представлении avg_rating_by_genre_mat:

\d+ avg_rating_by_genre_mat;

Результат включает определение материализованного представления:

             Materialized view "public.avg_rating_by_genre_mat"
    Column    |  Type   | Modifiers | Storage  | Stats target | Description
--------------+---------+-----------+----------+--------------+-------------
 genre        | text    |           | extended |              |
 total_movies | bigint  |           | plain    |              |
 avg_rating   | numeric |           | main     |              |
View definition:
 SELECT movies.genre,
    count(*) AS total_movies,
    round(avg(movies.rating), 2) AS avg_rating
   FROM movies
  GROUP BY movies.genre;
Distributed by: (genre)

Выполните запрос к представлению pg_matviews, чтобы получить список материализованных представлений:

SELECT matviewname, definition
FROM pg_matviews;

Результат выглядит следующим образом:

       matviewname       |                   definition
-------------------------+------------------------------------------------
 avg_rating_by_genre_mat |  SELECT movies.genre,                         +
                         |     count(*) AS total_movies,                 +
                         |     round(avg(movies.rating), 2) AS avg_rating+
                         |    FROM movies                                +
                         |   GROUP BY movies.genre;
(1 row)

ПРИМЕЧАНИЕ

Вы можете получить информацию о зависимостях материализованных представлений так же, как и для обычных представлений; единственное отличие в том, что relkind должен быть равен 'm'.

Чтобы удалить материализованное представление, используйте команду DROP MATERIALIZED VIEW:

DROP MATERIALIZED VIEW avg_rating_by_genre_mat;

Чтобы удалить материализованное представление вместе со связанными объектами, например, зависимыми материализованными представлениями, укажите параметр CASCADE.

Индексы

Обзор команды SELECT

Представления и материализованные представления

Предварительные требования

Представления

Обзор синтаксиса

Создание представления

Создание вложенного представления

Получение информации о представлениях

Метакоманды psql

Системные каталоги

Удаление представления

Материализованные представления

Обзор синтаксиса

Создание материализованного представления

Обновление материализованного представления

Получение информации о материализованных представлениях

Метакоманды psql

Системные каталоги

Удаление материализованного представления