物化视图

参考https://www.modb.pro/db/11330

【测试sql】

1）创建表

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
);

2）插入数据

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 ;

3）创建视图

物化视图

create materialized view mat_view_7 as
select history_order.order_id,
account.first_name ||' ' || last_name as name, history_order.amount
from account left join history_order on
account.id=history_order.account_id
where account.city_code=7;

普通视图

create view view_7 as
select history_order.order_id,
account.first_name ||' ' || last_name as name,
history_order.amount
from account left join history_order on
account.id=history_order.account_id
where account.city_code=7;

【xlog】

创建物化视图163条xlog，其中3条存储

分别是pg_toast_82090_index，pg_toast_82090，mat_view_7三个表

普通视图64条xlog，没有存储

【查询耗时对比】