Материализованные представления
Эта страница переведена при помощи нейросети GigaChat.
Материализованные представления в PostgreSQL используют систему правил так же, как и обычные представления, но сохраняют результаты в форме, похожей на таблицу. Основные различия между:
CREATE MATERIALIZED VIEW mymatview AS SELECT * FROM mytab;
и:
CREATE TABLE mymatview AS SELECT * FROM mytab;
заключаются в том, что материализованное представление не может быть впоследствии обновлено напрямую и что запрос, используемый для создания материализованного представления, хранится точно так же, как хранится запрос представления, чтобы свежие данные могли быть сгенерированы для материализованного представления с помощью:
REFRESH MATERIALIZED VIEW mymatview;
Информация о материализованном представлении в системных каталогах PostgreSQL точно такая же, как и для таблицы или представления. Таким образом, для парсера материализованное представление является отношением, как таблица или представление. Когда материализованное представление упоминается в запросе, данные возвращаются непосредственно из материализованного представления, как из таблицы; правило используется только для заполнения материализованного представления.
Хотя доступ к данным, хранящимся в материализованном представлении, часто осуществляется намного быстрее, чем прямой доступ к основным таблицам или через представление, данные не всегда актуальны; однако иногда текущие данные не нужны. Рассмотрим таблицу, которая регистрирует продажи:
CREATE TABLE invoice (
invoice_no integer PRIMARY KEY,
seller_no integer, -- ID of salesperson
invoice_date date, -- date of sale
invoice_amt numeric(13,2) -- amount of sale
);
Если люди хотят иметь возможность быстро строить графики исторических данных о продажах, они могут захотеть обобщить их, и им может быть все равно на неполные данные за текущий день:
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 row)
С 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
Если используется материализованное представление, запрос выполняется гораздо быстрее:
Aggregate (cost=4.44..4.45 rows=1 width=0) (actual time=0.042..0.042 rows=1 loops=1)
-> Index Only Scan using wrd_word on wrd (cost=0.42..4.44 rows=1 width=0) (actual time=0.039..0.039 rows=0 loops=1)
Index Cond: (word = 'caterpiler'::text)
Heap Fetches: 0
Planning time: 0.164 ms
Execution time: 0.117 ms
В любом случае, слово написано неправильно, поэтому давайте попробуем найти то, что имелось в виду. Снова используя 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 rows)
Limit (cost=11583.61..11583.64 rows=10 width=32) (actual time=1431.591..1431.594 rows=10 loops=1)
-> Sort (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 Memory: 25kB
-> 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 ms
Execution time: 1431.679 ms
Использование материализованного представления:
Limit (cost=0.29..1.06 rows=10 width=10) (actual time=187.222..188.257 rows=10 loops=1)
-> Index Scan using wrd_trgm on 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 ms
Execution time: 198.640 ms
Если можно смириться с периодическим обновлением удаленных данных в локальной базе данных, то выигрыш в производительности может быть значительным.