diff options
Diffstat (limited to 'doc/src/sgml/html/rules-materializedviews.html')
| -rw-r--r-- | doc/src/sgml/html/rules-materializedviews.html | 182 |
1 files changed, 182 insertions, 0 deletions
diff --git a/doc/src/sgml/html/rules-materializedviews.html b/doc/src/sgml/html/rules-materializedviews.html new file mode 100644 index 0000000..0e670e1 --- /dev/null +++ b/doc/src/sgml/html/rules-materializedviews.html @@ -0,0 +1,182 @@ +<?xml version="1.0" encoding="UTF-8" standalone="no"?> +<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>41.3. Materialized Views</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="rules-views.html" title="41.2. Views and the Rule System" /><link rel="next" href="rules-update.html" title="41.4. Rules on INSERT, UPDATE, and DELETE" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">41.3. Materialized Views</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="rules-views.html" title="41.2. Views and the Rule System">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="rules.html" title="Chapter 41. The Rule System">Up</a></td><th width="60%" align="center">Chapter 41. The Rule System</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 15.4 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="rules-update.html" title="41.4. Rules on INSERT, UPDATE, and DELETE">Next</a></td></tr></table><hr /></div><div class="sect1" id="RULES-MATERIALIZEDVIEWS"><div class="titlepage"><div><div><h2 class="title" style="clear: both">41.3. Materialized Views</h2></div></div></div><a id="id-1.8.6.8.2" class="indexterm"></a><a id="id-1.8.6.8.3" class="indexterm"></a><a id="id-1.8.6.8.4" class="indexterm"></a><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; + +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 <code class="literal">file_fdw</code> is below, with timings, + but since this is using cache on the local system the performance + difference compared to access to a remote system would usually be greater + than shown here. Notice we are also exploiting the ability to put an + index on the materialized view, whereas <code class="literal">file_fdw</code> does + not support indexes; this advantage might not apply for other sorts of + foreign data access. +</p><p> + 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 '/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; +</pre><p> + + Now let's spell-check a word. Using <code class="literal">file_fdw</code> directly: + +</p><pre class="programlisting"> +SELECT count(*) FROM words WHERE word = 'caterpiler'; + + count +------- + 0 +(1 row) +</pre><p> + + With <code class="command">EXPLAIN ANALYZE</code>, we see: + +</p><pre class="programlisting"> + 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 +</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.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 +</pre><p> + + Either way, the word is spelled wrong, so let's look for what we might + have wanted. Again using <code class="literal">file_fdw</code> and + <code class="literal">pg_trgm</code>: + +</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=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 +</pre><p> + + Using the materialized view: + +</p><pre class="programlisting"> + 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 +</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 /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="rules-views.html" title="41.2. Views and the Rule System">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="rules.html" title="Chapter 41. The Rule System">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="rules-update.html" title="41.4. Rules on INSERT, UPDATE, and DELETE">Next</a></td></tr><tr><td width="40%" align="left" valign="top">41.2. Views and the Rule System </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 15.4 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 41.4. Rules on <code class="command">INSERT</code>, <code class="command">UPDATE</code>, and <code class="command">DELETE</code></td></tr></table></div></body></html>
\ No newline at end of file |