begriffs open source - ai-pg/blob - full-docs/html/rules-materializedviews.html

   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>39.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="39.2. Views and the Rule System" /><link rel="next" href="rules-update.html" title="39.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">39.3. Materialized Views</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="rules-views.html" title="39.2. Views and the Rule System">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="rules.html" title="Chapter 39. The Rule System">Up</a></td><th width="60%" align="center">Chapter 39. The Rule System</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 18.0 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="rules-update.html" title="39.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">39.3. Materialized Views <a href="#RULES-MATERIALIZEDVIEWS" class="id_link">#</a></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
  64 CREATE UNIQUE INDEX sales_summary_seller
  65   ON sales_summary (seller_no, invoice_date);
  66 </pre><p>
  67
  68     This materialized view might be useful for displaying a graph in the
  69     dashboard created for salespeople.  A job could be scheduled to update
  70     the statistics each night using this SQL statement:
  71
  72 </p><pre class="programlisting">
  73 REFRESH MATERIALIZED VIEW sales_summary;
  74 </pre><p>
  75 </p><p>
  76     Another use for a materialized view is to allow faster access to data
  77     brought across from a remote system through a foreign data wrapper.
  78     A simple example using <code class="literal">file_fdw</code> is below, with timings,
  79     but since this is using cache on the local system the performance
  80     difference compared to access to a remote system would usually be greater
  81     than shown here.  Notice we are also exploiting the ability to put an
  82     index on the materialized view, whereas <code class="literal">file_fdw</code> does
  83     not support indexes; this advantage might not apply for other sorts of
  84     foreign data access.
  85 </p><p>
  86     Setup:
  87
  88 </p><pre class="programlisting">
  89 CREATE EXTENSION file_fdw;
  90 CREATE SERVER local_file FOREIGN DATA WRAPPER file_fdw;
  91 CREATE FOREIGN TABLE words (word text NOT NULL)
  92   SERVER local_file
  93   OPTIONS (filename '/usr/share/dict/words');
  94 CREATE MATERIALIZED VIEW wrd AS SELECT * FROM words;
  95 CREATE UNIQUE INDEX wrd_word ON wrd (word);
  96 CREATE EXTENSION pg_trgm;
  97 CREATE INDEX wrd_trgm ON wrd USING gist (word gist_trgm_ops);
  98 VACUUM ANALYZE wrd;
  99 </pre><p>
 100
 101     Now let's spell-check a word.  Using <code class="literal">file_fdw</code> directly:
 102
 103 </p><pre class="programlisting">
 104 SELECT count(*) FROM words WHERE word = 'caterpiler';
 105
 106  count
 107 -------
 108      0
 109 (1 row)
 110 </pre><p>
 111
 112     With <code class="command">EXPLAIN ANALYZE</code>, we see:
 113
 114 </p><pre class="programlisting">
 115  Aggregate  (cost=21763.99..21764.00 rows=1 width=0) (actual time=188.180..188.181 rows=1.00 loops=1)
 116    -&gt;  Foreign Scan on words  (cost=0.00..21761.41 rows=1032 width=0) (actual time=188.177..188.177 rows=0.00 loops=1)
 117          Filter: (word = 'caterpiler'::text)
 118          Rows Removed by Filter: 479829
 119          Foreign File: /usr/share/dict/words
 120          Foreign File Size: 4953699
 121  Planning time: 0.118 ms
 122  Execution time: 188.273 ms
 123 </pre><p>
 124
 125     If the materialized view is used instead, the query is much faster:
 126
 127 </p><pre class="programlisting">
 128  Aggregate  (cost=4.44..4.45 rows=1 width=0) (actual time=0.042..0.042 rows=1.00 loops=1)
 129    -&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.00 loops=1)
 130          Index Cond: (word = 'caterpiler'::text)
 131          Heap Fetches: 0
 132          Index Searches: 1
 133  Planning time: 0.164 ms
 134  Execution time: 0.117 ms
 135 </pre><p>
 136
 137     Either way, the word is spelled wrong, so let's look for what we might
 138     have wanted.  Again using <code class="literal">file_fdw</code> and
 139     <code class="literal">pg_trgm</code>:
 140
 141 </p><pre class="programlisting">
 142 SELECT word FROM words ORDER BY word &lt;-&gt; 'caterpiler' LIMIT 10;
 143
 144      word
 145 ---------------
 146  cater
 147  caterpillar
 148  Caterpillar
 149  caterpillars
 150  caterpillar's
 151  Caterpillar's
 152  caterer
 153  caterer's
 154  caters
 155  catered
 156 (10 rows)
 157 </pre><p>
 158
 159 </p><pre class="programlisting">
 160  Limit  (cost=11583.61..11583.64 rows=10 width=32) (actual time=1431.591..1431.594 rows=10.00 loops=1)
 161    -&gt;  Sort  (cost=11583.61..11804.76 rows=88459 width=32) (actual time=1431.589..1431.591 rows=10.00 loops=1)
 162          Sort Key: ((word &lt;-&gt; 'caterpiler'::text))
 163          Sort Method: top-N heapsort  Memory: 25kB
 164          -&gt;  Foreign Scan on words  (cost=0.00..9672.05 rows=88459 width=32) (actual time=0.057..1286.455 rows=479829.00 loops=1)
 165                Foreign File: /usr/share/dict/words
 166                Foreign File Size: 4953699
 167  Planning time: 0.128 ms
 168  Execution time: 1431.679 ms
 169 </pre><p>
 170
 171     Using the materialized view:
 172
 173 </p><pre class="programlisting">
 174  Limit  (cost=0.29..1.06 rows=10 width=10) (actual time=187.222..188.257 rows=10.00 loops=1)
 175    -&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.00 loops=1)
 176          Order By: (word &lt;-&gt; 'caterpiler'::text)
 177          Index Searches: 1
 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" title="39.2. Views and the Rule System">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="rules.html" title="Chapter 39. The Rule System">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="rules-update.html" title="39.4. Rules on INSERT, UPDATE, and DELETE">Next</a></td></tr><tr><td width="40%" align="left" valign="top">39.2. Views and the Rule System </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 18.0 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 39.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>