Hello, this is Rudi.
In this blog, I'll share with you some knowledge about Material View in PostgreSQL 11.3.
If you have any questions, please leave your commit and I'll get back to you.
1 What is View
A view is actually a query given a name. The name can be used like a table name in a query.
When PostgreSQL receives a SQL query and sees a view name, it replaces the view name with the actually query and continues to do further processing.
A view, actually a query, defines a set of data, the data comes from one or more tables.
Here is an example. Given a table 'employee_info'，a view can be defined as follwing,
create view employee_contact as select employee_id, first_name ||' '|| last_name as name, mobile_num, email from employee_info;
The name of this view is 'employee_contact'.
The view chooses 5 columns from table 'employee_info', while the result data has 4 columns ( 'first_name' and 'last_name' are combined to become 'name'). The result set is used as a contact list of all employees in a company.
Here is the definition of table 'employee_info'.
create table employee_info
(
employee_id integer not null,
first_name varchar(100),
last_name varchar(100),
email varchar(50),
mobile_num varchar(20),
hire_date timestamp without time zone not null,
salary int,
bank_number varchar(20),
department_id smallint not null,
supervisor_id integer,
password varchar(50)
);When an application needs to read the contact list, it just queries view 'employee_contact' like this,
select * from employee_contact;
To the application, it does not matter if 'employee_contact' is a table, or a view.
Following is another example. In this example, view 'sales_dept_contact' correponds to the contact list of the sales department.
create view sales_dept_contact
as select employee_id,
first_name ||' '|| last_name as name,
mobile_num,
email
from employee_info
where department_id=6;2 What is a Materialized View
The Materialized View is similar to View; they are both defined by a <name, sql> pair.
A Materialized View stores the result data set of its query in an internel table and this is the key difference between a View and a Materialized View.
When a query againt a Materialzed View comes in, instread of scaning the real tables, PostgreSQL scans the internel table to produce the result.
In the following example, two Materialize Views are defined：mat_employee_contact, mat_sales_dept_contact.
create materialized view mat_employee_contact
as select employee_id,
first_name ||' '|| last_name as name,
mobile_num,
email
from employee_info;
create materialized view mat_sales_dept_contact
as select employee_id,
first_name ||' '|| last_name as name,
mobile_num,
email
from employee_info
where department_id=6;The view name can be used in a query, like table name.
select * from mat_employee_contact; select * from mat_sales_dept_contact;
3 Materailzed View example with two tables
The SQL query of a View, or Materialize View, could be a join of multiple tables.
In the following example, we define 2 tables, 'account' and 'history_order' are used. Table 'account' stores information of all users of an online store while 'history_order' is a table for the history orders.
create table account (
id int,
first_name varchar(100),
last_name varchar(100),
address varchar(100),
mobile_phone varchar(50),
email varchar(100),
city_code int
);
create table history_order (
order_id int,
account_id int,
amount numeric(13,2),
status smallint
);Define a View 'view_7' and aMaterialized View 'mat_view_7'; Use them to static all history transactions of city #7.
create view view_7 as
select history_order.order_id,
account.first_name ||' ' || last_name as name,
history_order.order_id,
history_order.amount
from account left join history_order on account.id=history_order.account_id
where account.city_code=7;create materialized view mat_view_7 as
select history_order.order_id,
account.first_name ||' ' || last_name as name,
history_order.order_id,
history_order.amount
from account left join history_order on account.id=history_order.account_id
where account.city_code=7;Now, lets populate 1 thousand users and 1 million history orders and do some testing.
insert into account
select generate_series(1,1000) ,
'first-' || MD5(random()::text) ,
'last-' || MD5(random()::text) ,
'address-' || MD5(random()::text) ,
'('
|| (random() * 9)::INT
|| (random() * 9)::INT
|| (random() * 9)::INT
|| ') '
|| (random() * 9)::INT
|| (random() * 9)::INT
|| (random() * 9)::INT
|| '-'
|| (random() * 9)::INT
|| (random() * 9)::INT
|| (random() * 9)::INT
|| (random() * 9)::INT ,
'person' || generate_series(1,10) || '@example.com' ,
(random() * 1000)::INT;
insert into history_order
select generate_series(1,1000000) ,
floor(random() * 100) ,
(random() * 10000) ,
(random() *10)::INT ;Run query agains both view_7 and mat_view_7 to see the resonse time.
As we can see, querying view_7 is slower than querying mat_view_7.
postgres=# select sum(amount) from view_7;
sum
-------------
49982781.69
(1 row)
Time: 201.071 ms
postgres=# select sum(amount) from mat_view_7;
sum
-------------
49982781.69
(1 row)
Time: 11.014 ms4 Other features of Materialized Views
4.1 Synchronizing data of a Materailized View
If the data in the tables is changed, data in the Materialized view should be updated.
To update data of a Materialized View, use the following command.
refresh materialized view mat_view_7;
This command rebuilds the data in a Materialized View. There is no way to do increamental update at this time.
4.2 Materialize View support index
create index mat_index mat_view_7 ( order_id );
4.3 It's not possible to insert data into a Materialized View
Following comand doesn't work.
insert into mat_view_7 values (...);
5 Materialize View can work with fdw
Suppose there is a foreign table, 'fdw_employee_info', in a PostgreSQL cluster. The data may be in a PostgreSQL cluster on another server, or in an LDAP server, or in a csv file, or a xml file, etc.
Reading such a table would be slow.
select employee_id, first_name ||' '|| last_name as name, mobile_num, email from fdw_employee_info;
One way to make the query faster would be to create a Materialized View that loads data from the foreign table and stores the result set in an internel table.
create materailized local_employee_contact as select employee_id, first_name ||' '|| last_name as name, mobile_num, email from fdw_employee_info; select * from local_employee_contact;
Author: Rudi
Created: 2019-09-24 Tue 19:59