Baoding-FenShuaJiang
/
Green_GoodERP18_FSJ_Standard
關註 1
收藏 0
Wiki 捐赠
gooderp18绿色标准版
You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
分支: master
分支列表 標籤列表
${ item.name }
Create branch ${ searchTerm }
from 'master'
${ noResults }
Green_GoodERP18_FSJ_Standard/PostgreSQL/doc/postgresql/html/rules-materializedviews.html

184 line
8.9KB
原始文件 永久連結 Blame 文件歷史 

  1. <?xml version="1.0" encoding="UTF-8" standalone="no"?>

  2. <!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>40.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 V1.79.1" /><link rel="prev" href="rules-views.html" title="40.2. Views and the Rule System" /><link rel="next" href="rules-update.html" title="40.4. Rules on INSERT, UPDATE, and DELETE" /></head><body><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">40.3. Materialized Views</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="rules-views.html" title="40.2. Views and the Rule System">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="rules.html" title="Chapter 40. The Rule System">Up</a></td><th width="60%" align="center">Chapter 40. The Rule System</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 12.4 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="rules-update.html" title="40.4. Rules on INSERT, UPDATE, and DELETE">Next</a></td></tr></table><hr></hr></div><div class="sect1" id="RULES-MATERIALIZEDVIEWS"><div class="titlepage"><div><div><h2 class="title" style="clear: both">40.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>

  3.     Materialized views in <span class="productname">PostgreSQL</span> use the

  4.     rule system like views do, but persist the results in a table-like form.

  5.     The main differences between:

  6. 

  7. </p><pre class="programlisting">

  8. CREATE MATERIALIZED VIEW mymatview AS SELECT * FROM mytab;

  9. </pre><p>

  10. 

  11.     and:

  12. 

  13. </p><pre class="programlisting">

  14. CREATE TABLE mymatview AS SELECT * FROM mytab;

  15. </pre><p>

  16. 

  17.     are that the materialized view cannot subsequently be directly updated

  18.     and that the query used to create the materialized view is stored in

  19.     exactly the same way that a view's query is stored, so that fresh data

  20.     can be generated for the materialized view with:

  21. 

  22. </p><pre class="programlisting">

  23. REFRESH MATERIALIZED VIEW mymatview;

  24. </pre><p>

  25. 

  26.     The information about a materialized view in the

  27.     <span class="productname">PostgreSQL</span> system catalogs is exactly

  28.     the same as it is for a table or view. So for the parser, a

  29.     materialized view is a relation, just like a table or a view.  When

  30.     a materialized view is referenced in a query, the data is returned

  31.     directly from the materialized view, like from a table; the rule is

  32.     only used for populating the materialized view.

  33. </p><p>

  34.     While access to the data stored in a materialized view is often much

  35.     faster than accessing the underlying tables directly or through a view,

  36.     the data is not always current; yet sometimes current data is not needed.

  37.     Consider a table which records sales:

  38. 

  39. </p><pre class="programlisting">

  40. CREATE TABLE invoice (

  41.     invoice_no    integer        PRIMARY KEY,

  42.     seller_no     integer,       -- ID of salesperson

  43.     invoice_date  date,          -- date of sale

  44.     invoice_amt   numeric(13,2)  -- amount of sale

  45. );

  46. </pre><p>

  47. 

  48.     If people want to be able to quickly graph historical sales data, they

  49.     might want to summarize, and they may not care about the incomplete data

  50.     for the current date:

  51. 

  52. </p><pre class="programlisting">

  53. CREATE MATERIALIZED VIEW sales_summary AS

  54.   SELECT

  55.       seller_no,

  56.       invoice_date,

  57.       sum(invoice_amt)::numeric(13,2) as sales_amt

  58.     FROM invoice

  59.     WHERE invoice_date &lt; CURRENT_DATE

  60.     GROUP BY

  61.       seller_no,

  62.       invoice_date

  63.     ORDER BY

  64.       seller_no,

  65.       invoice_date;

  66. 

  67. CREATE UNIQUE INDEX sales_summary_seller

  68.   ON sales_summary (seller_no, invoice_date);

  69. </pre><p>

  70. 

  71.     This materialized view might be useful for displaying a graph in the

  72.     dashboard created for salespeople.  A job could be scheduled to update

  73.     the statistics each night using this SQL statement:

  74. 

  75. </p><pre class="programlisting">

  76. REFRESH MATERIALIZED VIEW sales_summary;

  77. </pre><p>

  78. </p><p>

  79.     Another use for a materialized view is to allow faster access to data

  80.     brought across from a remote system through a foreign data wrapper.

  81.     A simple example using <code class="literal">file_fdw</code> is below, with timings,

  82.     but since this is using cache on the local system the performance

  83.     difference compared to access to a remote system would usually be greater

  84.     than shown here.  Notice we are also exploiting the ability to put an

  85.     index on the materialized view, whereas <code class="literal">file_fdw</code> does

  86.     not support indexes; this advantage might not apply for other sorts of

  87.     foreign data access.

  88. </p><p>

  89.     Setup:

  90. 

  91. </p><pre class="programlisting">

  92. CREATE EXTENSION file_fdw;

  93. CREATE SERVER local_file FOREIGN DATA WRAPPER file_fdw;

  94. CREATE FOREIGN TABLE words (word text NOT NULL)

  95.   SERVER local_file

  96.   OPTIONS (filename '/usr/share/dict/words');

  97. CREATE MATERIALIZED VIEW wrd AS SELECT * FROM words;

  98. CREATE UNIQUE INDEX wrd_word ON wrd (word);

  99. CREATE EXTENSION pg_trgm;

  100. CREATE INDEX wrd_trgm ON wrd USING gist (word gist_trgm_ops);

  101. VACUUM ANALYZE wrd;

  102. </pre><p>

  103. 

  104.     Now let's spell-check a word.  Using <code class="literal">file_fdw</code> directly:

  105. 

  106. </p><pre class="programlisting">

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

  108. 

  109.  count 

  110. -------

  111.      0

  112. (1 row)

  113. </pre><p>

  114. 

  115.     With <code class="command">EXPLAIN ANALYZE</code>, we see:

  116. 

  117. </p><pre class="programlisting">

  118.  Aggregate  (cost=21763.99..21764.00 rows=1 width=0) (actual time=188.180..188.181 rows=1 loops=1)

  119.    -&gt;  Foreign Scan on words  (cost=0.00..21761.41 rows=1032 width=0) (actual time=188.177..188.177 rows=0 loops=1)

  120.          Filter: (word = 'caterpiler'::text)

  121.          Rows Removed by Filter: 479829

  122.          Foreign File: /usr/share/dict/words

  123.          Foreign File Size: 4953699

  124.  Planning time: 0.118 ms

  125.  Execution time: 188.273 ms

  126. </pre><p>

  127. 

  128.     If the materialized view is used instead, the query is much faster:

  129. 

  130. </p><pre class="programlisting">

  131.  Aggregate  (cost=4.44..4.45 rows=1 width=0) (actual time=0.042..0.042 rows=1 loops=1)

  132.    -&gt;  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)

  133.          Index Cond: (word = 'caterpiler'::text)

  134.          Heap Fetches: 0

  135.  Planning time: 0.164 ms

  136.  Execution time: 0.117 ms

  137. </pre><p>

  138. 

  139.     Either way, the word is spelled wrong, so let's look for what we might

  140.     have wanted.  Again using <code class="literal">file_fdw</code>:

  141. 

  142. </p><pre class="programlisting">

  143. SELECT word FROM words ORDER BY word &lt;-&gt; 'caterpiler' LIMIT 10;

  144. 

  145.      word     

  146. ---------------

  147.  cater

  148.  caterpillar

  149.  Caterpillar

  150.  caterpillars

  151.  caterpillar's

  152.  Caterpillar's

  153.  caterer

  154.  caterer's

  155.  caters

  156.  catered

  157. (10 rows)

  158. </pre><p>

  159. 

  160. </p><pre class="programlisting">

  161.  Limit  (cost=11583.61..11583.64 rows=10 width=32) (actual time=1431.591..1431.594 rows=10 loops=1)

  162.    -&gt;  Sort  (cost=11583.61..11804.76 rows=88459 width=32) (actual time=1431.589..1431.591 rows=10 loops=1)

  163.          Sort Key: ((word &lt;-&gt; 'caterpiler'::text))

  164.          Sort Method: top-N heapsort  Memory: 25kB

  165.          -&gt;  Foreign Scan on words  (cost=0.00..9672.05 rows=88459 width=32) (actual time=0.057..1286.455 rows=479829 loops=1)

  166.                Foreign File: /usr/share/dict/words

  167.                Foreign File Size: 4953699

  168.  Planning time: 0.128 ms

  169.  Execution time: 1431.679 ms

  170. </pre><p>

  171. 

  172.     Using the materialized view:

  173. 

  174. </p><pre class="programlisting">

  175.  Limit  (cost=0.29..1.06 rows=10 width=10) (actual time=187.222..188.257 rows=10 loops=1)

  176.    -&gt;  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)

  177.          Order By: (word &lt;-&gt; 'caterpiler'::text)

  178.  Planning time: 0.196 ms

  179.  Execution time: 198.640 ms

  180. </pre><p>

  181. 

  182.     If you can tolerate periodic update of the remote data to the local

  183.     database, the performance benefit can be substantial.

  184. </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">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="rules.html">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="rules-update.html">Next</a></td></tr><tr><td width="40%" align="left" valign="top">40.2. Views and the Rule System </td><td width="20%" align="center"><a accesskey="h" href="index.html">Home</a></td><td width="40%" align="right" valign="top"> 40.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>
上海开阖软件有限公司 沪ICP备12045867号-1