Материализованные представления в Digital Q.DataBase используют систему правил аналогично обычным представлениям, но сохраняют результаты в виде физической таблицы. Основные различия между:

CREATE MATERIALIZED VIEW mymatview AS SELECT * FROM mytab;

и:

CREATE TABLE mymatview AS SELECT * FROM mytab;

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

REFRESH MATERIALIZED VIEW mymatview;

Информация о материализованном представлении в Digital Q.DataBase системных каталогах точно такая же, как и для таблицы или представления. Таким образом, для анализатора материализованное представление является отношением, так же как таблица или представление. Когда в запросе имеется ссылка на материализованное представление, данные возвращаются непосредственно из материализованного представления, как из таблицы; правило используется только для наполнения материализованного представления.

Хотя доступ к данным, хранящимся в материализованном представлении, часто осуществляется гораздо быстрее, чем обращение к базовым таблицам напрямую или через представление, данные не всегда являются актуальными; однако иногда актуальные данные не требуются. Рассмотрим таблицу, в которой регистрируются продажи:

CREATE TABLE invoice (
    invoice_no    integer        PRIMARY KEY,
    seller_no     integer,       -- ID продавца
    invoice_date  date,          -- дата продажи
    invoice_amt   numeric(13,2)  -- сумма продажи
);

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

CREATE MATERIALIZED VIEW sales_summary AS
  SELECT
      seller_no,
      invoice_date,
      sum(invoice_amt)::numeric(13,2) as sales_amt
    FROM invoice
    WHERE invoice_date < CURRENT_DATE
    GROUP BY
      seller_no,
      invoice_date;

CREATE UNIQUE INDEX sales_summary_seller
  ON sales_summary (seller_no, invoice_date);

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

REFRESH MATERIALIZED VIEW sales_summary;

Другое применение материализованного представления заключается в обеспечении более быстрого доступа к данным, получаемым из удаленной системы через обертку сторонних данных. Ниже приведен простой пример использования file_fdw с замерами времени, но поскольку здесь используется локальное кэширование, разница в производительности по сравнению с доступом к удаленной системе обычно будет существеннее, чем показано здесь. Обратите внимание, что мы также используем возможность создания индекса для материализованного представления, в то время как file_fdw не поддерживает индексы; это преимущество может быть неприменимо к другим видам доступа к сторонним данным.

Настройка:

CREATE EXTENSION file_fdw;
CREATE SERVER local_file FOREIGN DATA WRAPPER file_fdw;
CREATE FOREIGN TABLE words (word text NOT NULL)
  SERVER local_file
  OPTIONS (filename '/usr/share/dict/words');
CREATE MATERIALIZED VIEW wrd AS SELECT * FROM words;
CREATE UNIQUE INDEX wrd_word ON wrd (word);
CREATE EXTENSION pg_trgm;
CREATE INDEX wrd_trgm ON wrd USING gist (word gist_trgm_ops);
VACUUM ANALYZE wrd;

Теперь выполним проверку орфографии слова. Используя команду file_fdw напрямую:

SELECT count(*) FROM words WHERE word = 'caterpiler';

 count
-------
     0
(1 строка)

С помощью команды EXPLAIN ANALYZEмы увидим следующее:

 Aggregate  (cost=21763.99..21764.00 rows=1 width=0) (actual time=188.180..188.181 rows=1 loops=1)
   ->  Foreign Scan on words  (cost=0.00..21761.41 rows=1032 width=0) (actual time=188.177..188.177 rows=0 loops=1)
         Filter: (word = 'caterpiler'::text)
         Rows Removed by Filter: 479829
         Foreign File: /usr/share/dict/words
         Foreign File Size: 4953699
 Planning time: 0.118 ms
 Execution time: 188.273 ms

Если же использовать материализованное представление, запрос будет выполняться значительно быстрее:

 Агрегатная функция  (cost=4.44..4.45 rows=1 width=0) (actual time=0.042..0.042 rows=1 loops=1)
   ->  Index Only Scan с использованием wrd_word по wrd  (cost=0.42..4.44 rows=1 width=0) (actual time=0.039..0.039 rows=0 loops=1)
         Условие индекса: (word = 'caterpiler'::text)
         Выборок из кучи (Heap Fetches): 0
 Planning time: 0.164 мс
 Execution time: 0.117 мс

В любом случае слово написано с ошибкой, поэтому поищем то, что могло иметься в виду. Снова воспользуемся командой file_fdw и pg_trgm:

SELECT word FROM words ORDER BY word <-> 'caterpiler' LIMIT 10;

     word
---------------
 cater
 caterpillar
 Caterpillar
 caterpillars
 caterpillar's
 Caterpillar's
 caterer
 caterer's
 caters
 catered
(10 строк)

 Limit  (cost=11583.61..11583.64 rows=10 width=32) (actual time=1431.591..1431.594 rows=10 loops=1)
   ->  Сортировка  (cost=11583.61..11804.76 rows=88459 width=32) (actual time=1431.589..1431.591 rows=10 loops=1)
         Sort Key: ((word <-> 'caterpiler'::text))
         Sort Method: top-N heapsort  Память: 25 кБ
         ->  Foreign Scan on words  (cost=0.00..9672.05 rows=88459 width=32) (actual time=0.057..1286.455 rows=479829 loops=1)
               Foreign File: /usr/share/dict/words
               Foreign File Size: 4953699
 Planning time: 0.128 мс
 Execution time: 1431.679 мс

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

 Limit  (cost=0.29..1.06 rows=10 width=10) (actual time=187.222..188.257 rows=10 loops=1)
   ->  сканирование индекса с использованием wrd_trgm по wrd  (cost=0.29..37020.87 rows=479829 width=10) (actual time=187.219..188.252 rows=10 loops=1)
         Order By: (word <-> 'caterpiler'::text)
 Planning time: 0.196 мс
 Execution time: 198.640 мс

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