summaryrefslogtreecommitdiffstats
path: root/doc/src/sgml/html/rules-materializedviews.html
blob: 899c9874483e40d30c25d02aa136a6aa85c3e779 (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
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.5 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 &lt; 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)
   -&gt;  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)
   -&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)
         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 &lt;-&gt; '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)
   -&gt;  Sort  (cost=11583.61..11804.76 rows=88459 width=32) (actual time=1431.589..1431.591 rows=10 loops=1)
         Sort Key: ((word &lt;-&gt; 'caterpiler'::text))
         Sort Method: top-N heapsort  Memory: 25kB
         -&gt;  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)
   -&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)
         Order By: (word &lt;-&gt; '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.5 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>