<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <HTML ><HEAD ><TITLE >Materialized Views</TITLE ><META NAME="GENERATOR" CONTENT="Modular DocBook HTML Stylesheet Version 1.79"><LINK REV="MADE" HREF="mailto:pgsql-docs@postgresql.org"><LINK REL="HOME" TITLE="PostgreSQL 9.3.9 Documentation" HREF="index.html"><LINK REL="UP" TITLE="The Rule System" HREF="rules.html"><LINK REL="PREVIOUS" TITLE="Views and the Rule System" HREF="rules-views.html"><LINK REL="NEXT" TITLE="Rules on INSERT, UPDATE, and DELETE" HREF="rules-update.html"><LINK REL="STYLESHEET" TYPE="text/css" HREF="stylesheet.css"><META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=ISO-8859-1"><META NAME="creation" CONTENT="2015-06-13T20:07:22"></HEAD ><BODY CLASS="SECT1" ><DIV CLASS="NAVHEADER" ><TABLE SUMMARY="Header navigation table" WIDTH="100%" BORDER="0" CELLPADDING="0" CELLSPACING="0" ><TR ><TH COLSPAN="5" ALIGN="center" VALIGN="bottom" ><A HREF="index.html" >PostgreSQL 9.3.9 Documentation</A ></TH ></TR ><TR ><TD WIDTH="10%" ALIGN="left" VALIGN="top" ><A TITLE="Views and the Rule System" HREF="rules-views.html" ACCESSKEY="P" >Prev</A ></TD ><TD WIDTH="10%" ALIGN="left" VALIGN="top" ><A HREF="rules.html" ACCESSKEY="U" >Up</A ></TD ><TD WIDTH="60%" ALIGN="center" VALIGN="bottom" >Chapter 38. The Rule System</TD ><TD WIDTH="20%" ALIGN="right" VALIGN="top" ><A TITLE="Rules on INSERT, UPDATE, and DELETE" HREF="rules-update.html" ACCESSKEY="N" >Next</A ></TD ></TR ></TABLE ><HR ALIGN="LEFT" WIDTH="100%"></DIV ><DIV CLASS="SECT1" ><H1 CLASS="SECT1" ><A NAME="RULES-MATERIALIZEDVIEWS" >38.3. Materialized Views</A ></H1 ><P > Materialized views in <SPAN CLASS="PRODUCTNAME" >PostgreSQL</SPAN > use the rule system like views do, but persist the results in a table-like form. The main differences between: </P><PRE CLASS="PROGRAMLISTING" >CREATE MATERIALIZED VIEW mymatview AS SELECT * FROM mytab;</PRE ><P> and: </P><PRE CLASS="PROGRAMLISTING" >CREATE TABLE mymatview AS SELECT * FROM mytab;</PRE ><P> are that the materialized view cannot subsequently be directly updated and that the query used to create the materialized view is stored in exactly the same way that a view's query is stored, so that fresh data can be generated for the materialized view with: </P><PRE CLASS="PROGRAMLISTING" >REFRESH MATERIALIZED VIEW mymatview;</PRE ><P> The information about a materialized view in the <SPAN CLASS="PRODUCTNAME" >PostgreSQL</SPAN > system catalogs is exactly the same as it is for a table or view. So for the parser, a materialized view is a relation, just like a table or a view. When a materialized view is referenced in a query, the data is returned directly from the materialized view, like from a table; the rule is only used for populating the materialized view.</P ><P > While access to the data stored in a materialized view is often much faster than accessing the underlying tables directly or through a view, the data is not always current; yet sometimes current data is not needed. Consider a table which records sales: </P><PRE CLASS="PROGRAMLISTING" >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 );</PRE ><P> If people want to be able to quickly graph historical sales data, they might want to summarize, and they may not care about the incomplete data for the current date: </P><PRE CLASS="PROGRAMLISTING" >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 ORDER BY seller_no, invoice_date; CREATE UNIQUE INDEX sales_summary_seller ON sales_summary (seller_no, invoice_date);</PRE ><P> This materialized view might be useful for displaying a graph in the dashboard created for salespeople. A job could be scheduled to update the statistics each night using this SQL statement: </P><PRE CLASS="PROGRAMLISTING" >REFRESH MATERIALIZED VIEW sales_summary;</PRE ><P></P ><P > Another use for a materialized view is to allow faster access to data brought across from a remote system, through a foreign data wrapper. A simple example using <TT CLASS="LITERAL" >file_fdw</TT > is below, with timings, but since this is using cache on the local system the performance difference on a foreign data wrapper to a remote system could be greater. Setup: </P><PRE CLASS="PROGRAMLISTING" >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 '/etc/dictionaries-common/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;</PRE ><P> Now let's spell-check a word. Using <TT CLASS="LITERAL" >file_fdw</TT > directly: </P><PRE CLASS="PROGRAMLISTING" >SELECT count(*) FROM words WHERE word = 'caterpiler'; count ------- 0 (1 row)</PRE ><P> The plan is: </P><PRE CLASS="PROGRAMLISTING" > Aggregate (cost=4125.19..4125.20 rows=1 width=0) (actual time=26.013..26.014 rows=1 loops=1) -> Foreign Scan on words (cost=0.00..4124.70 rows=196 width=0) (actual time=26.011..26.011 rows=0 loops=1) Filter: (word = 'caterpiler'::text) Rows Removed by Filter: 99171 Foreign File: /etc/dictionaries-common/words Foreign File Size: 938848 Total runtime: 26.081 ms</PRE ><P> If the materialized view is used instead, the query is much faster: </P><PRE CLASS="PROGRAMLISTING" > Aggregate (cost=4.44..4.45 rows=1 width=0) (actual time=0.074..0.074 rows=1 loops=1) -> Index Only Scan using wrd_word on wrd (cost=0.42..4.44 rows=1 width=0) (actual time=0.071..0.071 rows=0 loops=1) Index Cond: (word = 'caterpiler'::text) Heap Fetches: 0 Total runtime: 0.119 ms</PRE ><P> Either way, the word is spelled wrong, so let's look for what we might have wanted. Again using <TT CLASS="LITERAL" >file_fdw</TT >: </P><PRE CLASS="PROGRAMLISTING" >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)</PRE ><P> </P><PRE CLASS="PROGRAMLISTING" > Limit (cost=2195.70..2195.72 rows=10 width=32) (actual time=218.904..218.906 rows=10 loops=1) -> Sort (cost=2195.70..2237.61 rows=16765 width=32) (actual time=218.902..218.904 rows=10 loops=1) Sort Key: ((word <-> 'caterpiler'::text)) Sort Method: top-N heapsort Memory: 25kB -> Foreign Scan on words (cost=0.00..1833.41 rows=16765 width=32) (actual time=0.046..200.965 rows=99171 loops=1) Foreign File: /etc/dictionaries-common/words Foreign File Size: 938848 Total runtime: 218.966 ms</PRE ><P> Using the materialized view: </P><PRE CLASS="PROGRAMLISTING" > Limit (cost=0.28..1.02 rows=10 width=9) (actual time=24.916..25.079 rows=10 loops=1) -> Index Scan using wrd_trgm on wrd (cost=0.28..7383.70 rows=99171 width=9) (actual time=24.914..25.076 rows=10 loops=1) Order By: (word <-> 'caterpiler'::text) Total runtime: 25.884 ms</PRE ><P> If you can tolerate periodic update of the remote data to the local database, the performance benefit can be substantial.</P ></DIV ><DIV CLASS="NAVFOOTER" ><HR ALIGN="LEFT" WIDTH="100%"><TABLE SUMMARY="Footer navigation table" WIDTH="100%" BORDER="0" CELLPADDING="0" CELLSPACING="0" ><TR ><TD WIDTH="33%" ALIGN="left" VALIGN="top" ><A HREF="rules-views.html" ACCESSKEY="P" >Prev</A ></TD ><TD WIDTH="34%" ALIGN="center" VALIGN="top" ><A HREF="index.html" ACCESSKEY="H" >Home</A ></TD ><TD WIDTH="33%" ALIGN="right" VALIGN="top" ><A HREF="rules-update.html" ACCESSKEY="N" >Next</A ></TD ></TR ><TR ><TD WIDTH="33%" ALIGN="left" VALIGN="top" >Views and the Rule System</TD ><TD WIDTH="34%" ALIGN="center" VALIGN="top" ><A HREF="rules.html" ACCESSKEY="U" >Up</A ></TD ><TD WIDTH="33%" ALIGN="right" VALIGN="top" >Rules on <TT CLASS="COMMAND" >INSERT</TT >, <TT CLASS="COMMAND" >UPDATE</TT >, and <TT CLASS="COMMAND" >DELETE</TT ></TD ></TR ></TABLE ></DIV ></BODY ></HTML >