# 连接数据库
yassql -U fgedu -P fgedu123 -d fgedudb

# 创建测试表
CREATE TABLE fgedu_employee (
id SERIAL PRIMARY KEY,
name VARCHAR(50) NOT NULL,
department VARCHAR(50),
position VARCHAR(50),
salary DECIMAL(10,2),
hire_date DATE
);

# 插入测试数据
INSERT INTO fgedu_employee (name, department, position, salary, hire_date) VALUES
(‘张三’, ‘技术部’, ‘开发工程师’, 8000, ‘2023-01-01’),
(‘李四’, ‘技术部’, ‘测试工程师’, 6000, ‘2023-02-01’),
(‘王五’, ‘市场部’, ‘市场经理’, 7000, ‘2023-03-01’),
(‘赵六’, ‘财务部’, ‘财务专员’, 5000, ‘2023-04-01’),
(‘孙七’, ‘技术部’, ‘架构师’, 12000, ‘2023-05-01’);

# 创建视图
CREATE VIEW v_fgedu_employee AS
SELECT id, name, department, position, salary
FROM fgedu_employee;

# 创建带条件的视图
CREATE VIEW v_fgedu_tech_employee AS
SELECT id, name, position, salary
FROM fgedu_employee
WHERE department = ‘技术部’;

# 查看视图
\d v_fgedu_employee;
\d v_fgedu_tech_employee;

# 使用视图
SELECT * FROM v_fgedu_employee;
SELECT * FROM v_fgedu_tech_employee;

# 修改视图
CREATE OR REPLACE VIEW v_fgedu_employee AS
SELECT id, name, department, position, salary, hire_date
FROM fgedu_employee;

# 删除视图
DROP VIEW IF EXISTS v_fgedu_tech_employee;

输出日志：

# 创建测试表输出
fgedudb=> CREATE TABLE fgedu_employee (
id SERIAL PRIMARY KEY,
name VARCHAR(50) NOT NULL,
department VARCHAR(50),
position VARCHAR(50),
salary DECIMAL(10,2),
hire_date DATE
);
CREATE TABLE

# 插入测试数据输出
fgedudb=> INSERT INTO fgedu_employee (name, department, position, salary, hire_date) VALUES
(‘张三’, ‘技术部’, ‘开发工程师’, 8000, ‘2023-01-01’),
(‘李四’, ‘技术部’, ‘测试工程师’, 6000, ‘2023-02-01’),
(‘王五’, ‘市场部’, ‘市场经理’, 7000, ‘2023-03-01’),
(‘赵六’, ‘财务部’, ‘财务专员’, 5000, ‘2023-04-01’),
(‘孙七’, ‘技术部’, ‘架构师’, 12000, ‘2023-05-01’);
INSERT 0 5

# 创建视图输出
fgedudb=> CREATE VIEW v_fgedu_employee AS
SELECT id, name, department, position, salary
FROM fgedu_employee;
CREATE VIEW

# 创建带条件的视图输出
fgedudb=> CREATE VIEW v_fgedu_tech_employee AS
SELECT id, name, position, salary
FROM fgedu_employee
WHERE department = ‘技术部’;
CREATE VIEW

# 查看视图输出
fgedudb=> \d v_fgedu_employee;
View “public.v_fgedu_employee”
Column | Type | Collation | Nullable | Default
————+———————–+———–+———-+——–
id | integer | | |
name | character varying(50) | | |
department | character varying(50) | | |
position | character varying(50) | | |
salary | numeric(10,2) | | |
View definition:
SELECT fgedu_employee.id, fgedu_employee.name, fgedu_employee.department, fgedu_employee.position, fgedu_employee.salary
FROM fgedu_employee;

fgedudb=> \d v_fgedu_tech_employee;
View “public.v_fgedu_tech_employee”
Column | Type | Collation | Nullable | Default
———–+———————–+———–+———-+——–
id | integer | | |
name | character varying(50) | | |
position | character varying(50) | | |
salary | numeric(10,2) | | |
View definition:
SELECT fgedu_employee.id, fgedu_employee.name, fgedu_employee.position, fgedu_employee.salary
FROM fgedu_employee
WHERE fgedu_employee.department = ‘技术部’;

# 使用视图输出
fgedudb=> SELECT * FROM v_fgedu_employee;
id | name | department | position | salary
—-+——+————+————+——–
1 | 张三 | 技术部 | 开发工程师 | 8000
2 | 李四 | 技术部 | 测试工程师 | 6000
3 | 王五 | 市场部 | 市场经理 | 7000
4 | 赵六 | 财务部 | 财务专员 | 5000
5 | 孙七 | 技术部 | 架构师 | 12000
(5 rows)

fgedudb=> SELECT * FROM v_fgedu_tech_employee;
id | name | position | salary
—-+——+————+——–
1 | 张三 | 开发工程师 | 8000
2 | 李四 | 测试工程师 | 6000
5 | 孙七 | 架构师 | 12000
(3 rows)

# 修改视图输出
fgedudb=> CREATE OR REPLACE VIEW v_fgedu_employee AS
SELECT id, name, department, position, salary, hire_date
FROM fgedu_employee;
CREATE VIEW

# 删除视图输出
fgedudb=> DROP VIEW IF EXISTS v_fgedu_tech_employee;
DROP VIEW

# 连接数据库
yassql -U fgedu -P fgedu123 -d fgedudb

# 创建测试表
CREATE TABLE fgedu_order (
id SERIAL PRIMARY KEY,
order_no VARCHAR(50) NOT NULL,
customer_id INTEGER,
amount DECIMAL(10,2),
order_date DATE NOT NULL,
status VARCHAR(20)
);

# 插入测试数据
INSERT INTO fgedu_order (order_no, customer_id, amount, order_date, status) VALUES
(‘ORD20230101001’, 1, 1000, ‘2023-01-01’, ‘已完成’),
(‘ORD20230102001’, 2, 2000, ‘2023-01-02’, ‘已完成’),
(‘ORD20230103001’, 1, 1500, ‘2023-01-03’, ‘已完成’),
(‘ORD20230104001’, 3, 2500, ‘2023-01-04’, ‘已完成’),
(‘ORD20230105001’, 2, 1800, ‘2023-01-05’, ‘已完成’),
(‘ORD20230201001’, 1, 1200, ‘2023-02-01’, ‘已完成’),
(‘ORD20230202001’, 3, 2200, ‘2023-02-02’, ‘已完成’),
(‘ORD20230203001’, 2, 1600, ‘2023-02-03’, ‘已完成’),
(‘ORD20230204001’, 1, 1900, ‘2023-02-04’, ‘已完成’),
(‘ORD20230205001’, 3, 2100, ‘2023-02-05’, ‘已完成’);

# 创建物化视图
CREATE MATERIALIZED VIEW mv_fgedu_order_summary AS
SELECT
DATE_TRUNC(‘month’, order_date) AS month,
customer_id,
COUNT(*) AS order_count,
SUM(amount) AS total_amount
FROM fgedu_order
GROUP BY DATE_TRUNC(‘month’, order_date), customer_id
ORDER BY month, customer_id;

# 为物化视图创建索引
CREATE INDEX idx_mv_fgedu_order_summary_month ON mv_fgedu_order_summary(month);
CREATE INDEX idx_mv_fgedu_order_summary_customer ON mv_fgedu_order_summary(customer_id);

# 查看物化视图
\d mv_fgedu_order_summary;

# 查询物化视图
SELECT * FROM mv_fgedu_order_summary;

# 插入新数据
INSERT INTO fgedu_order (order_no, customer_id, amount, order_date, status) VALUES
(‘ORD20230301001’, 1, 1300, ‘2023-03-01’, ‘已完成’),
(‘ORD20230302001’, 2, 1700, ‘2023-03-02’, ‘已完成’);

# 刷新物化视图
REFRESH MATERIALIZED VIEW mv_fgedu_order_summary;

# 查询刷新后的物化视图
SELECT * FROM mv_fgedu_order_summary;

# 删除物化视图
DROP MATERIALIZED VIEW IF EXISTS mv_fgedu_order_summary;

输出日志：

# 创建测试表输出
fgedudb=> CREATE TABLE fgedu_order (
id SERIAL PRIMARY KEY,
order_no VARCHAR(50) NOT NULL,
customer_id INTEGER,
amount DECIMAL(10,2),
order_date DATE NOT NULL,
status VARCHAR(20)
);
CREATE TABLE

# 插入测试数据输出
fgedudb=> INSERT INTO fgedu_order (order_no, customer_id, amount, order_date, status) VALUES
(‘ORD20230101001’, 1, 1000, ‘2023-01-01’, ‘已完成’),
(‘ORD20230102001’, 2, 2000, ‘2023-01-02’, ‘已完成’),
(‘ORD20230103001’, 1, 1500, ‘2023-01-03’, ‘已完成’),
(‘ORD20230104001’, 3, 2500, ‘2023-01-04’, ‘已完成’),
(‘ORD20230105001’, 2, 1800, ‘2023-01-05’, ‘已完成’),
(‘ORD20230201001’, 1, 1200, ‘2023-02-01’, ‘已完成’),
(‘ORD20230202001’, 3, 2200, ‘2023-02-02’, ‘已完成’),
(‘ORD20230203001’, 2, 1600, ‘2023-02-03’, ‘已完成’),
(‘ORD20230204001’, 1, 1900, ‘2023-02-04’, ‘已完成’),
(‘ORD20230205001’, 3, 2100, ‘2023-02-05’, ‘已完成’);
INSERT 0 10

# 创建物化视图输出
fgedudb=> CREATE MATERIALIZED VIEW mv_fgedu_order_summary AS
SELECT
DATE_TRUNC(‘month’, order_date) AS month,
customer_id,
COUNT(*) AS order_count,
SUM(amount) AS total_amount
FROM fgedu_order
GROUP BY DATE_TRUNC(‘month’, order_date), customer_id
ORDER BY month, customer_id;
CREATE MATERIALIZED VIEW

# 为物化视图创建索引输出
fgedudb=> CREATE INDEX idx_mv_fgedu_order_summary_month ON mv_fgedu_order_summary(month);
CREATE INDEX

fgedudb=> CREATE INDEX idx_mv_fgedu_order_summary_customer ON mv_fgedu_order_summary(customer_id);
CREATE INDEX

# 查看物化视图输出
fgedudb=> \d mv_fgedu_order_summary;
Materialized view “public.mv_fgedu_order_summary”
Column | Type | Collation | Nullable | Default
————–+—————————–+———–+———-+——–
month | timestamp without time zone | | |
customer_id | integer | | |
order_count | bigint | | |
total_amount | numeric(10,2) | | |
Indexes:
“idx_mv_fgedu_order_summary_customer” btree (customer_id)
“idx_mv_fgedu_order_summary_month” btree (month)

# 查询物化视图输出
fgedudb=> SELECT * FROM mv_fgedu_order_summary;
month | customer_id | order_count | total_amount
————————+————-+————-+————–
2023-01-01 00:00:00 | 1 | 2 | 2500
2023-01-01 00:00:00 | 2 | 2 | 3800
2023-01-01 00:00:00 | 3 | 1 | 2500
2023-02-01 00:00:00 | 1 | 2 | 3100
2023-02-01 00:00:00 | 2 | 1 | 1600
2023-02-01 00:00:00 | 3 | 2 | 4300
(6 rows)

# 插入新数据输出
fgedudb=> INSERT INTO fgedu_order (order_no, customer_id, amount, order_date, status) VALUES
(‘ORD20230301001’, 1, 1300, ‘2023-03-01’, ‘已完成’),
(‘ORD20230302001’, 2, 1700, ‘2023-03-02’, ‘已完成’);
INSERT 0 2

# 刷新物化视图输出
fgedudb=> REFRESH MATERIALIZED VIEW mv_fgedu_order_summary;
REFRESH MATERIALIZED VIEW

# 查询刷新后的物化视图输出
fgedudb=> SELECT * FROM mv_fgedu_order_summary;
month | customer_id | order_count | total_amount
————————+————-+————-+————–
2023-01-01 00:00:00 | 1 | 2 | 2500
2023-01-01 00:00:00 | 2 | 2 | 3800
2023-01-01 00:00:00 | 3 | 1 | 2500
2023-02-01 00:00:00 | 1 | 2 | 3100
2023-02-01 00:00:00 | 2 | 1 | 1600
2023-02-01 00:00:00 | 3 | 2 | 4300
2023-03-01 00:00:00 | 1 | 1 | 1300
2023-03-01 00:00:00 | 2 | 1 | 1700
(8 rows)

# 删除物化视图输出
fgedudb=> DROP MATERIALIZED VIEW IF EXISTS mv_fgedu_order_summary;
DROP MATERIALIZED VIEW

# 连接数据库
yassql -U fgedu -P fgedu123 -d fgedudb

# 创建测试表
CREATE TABLE fgedu_customer (
id SERIAL PRIMARY KEY,
name VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE,
phone VARCHAR(20),
create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE fgedu_order (
id SERIAL PRIMARY KEY,
order_no VARCHAR(50) NOT NULL,
customer_id INTEGER REFERENCES fgedu_customer(id),
amount DECIMAL(10,2),
order_date DATE NOT NULL,
status VARCHAR(20)
);

# 插入测试数据
INSERT INTO fgedu_customer (name, email, phone) VALUES
(‘张三’, ‘zhangsan@fgedu.net.cn’, ‘13800138001’),
(‘李四’, ‘lisi@fgedu.net.cn’, ‘13800138002’),
(‘王五’, ‘wangwu@fgedu.net.cn’, ‘13800138003’);

INSERT INTO fgedu_order (order_no, customer_id, amount, order_date, status) VALUES
(‘ORD20230101001’, 1, 1000, ‘2023-01-01’, ‘已完成’),
(‘ORD20230102001’, 2, 2000, ‘2023-01-02’, ‘已完成’),
(‘ORD20230103001’, 1, 1500, ‘2023-01-03’, ‘已完成’),
(‘ORD20230104001’, 3, 2500, ‘2023-01-04’, ‘已完成’),
(‘ORD20230105001’, 2, 1800, ‘2023-01-05’, ‘已完成’);

# 创建复杂视图
CREATE VIEW v_fgedu_customer_order AS
SELECT
c.id AS customer_id,
c.name AS customer_name,
c.email,
c.phone,
o.id AS order_id,
o.order_no,
o.amount,
o.order_date,
o.status
FROM fgedu_customer c
LEFT JOIN fgedu_order o ON c.id = o.customer_id;

# 测试视图性能
EXPLAIN ANALYZE SELECT * FROM v_fgedu_customer_order WHERE customer_id = 1;

# 为底层表创建索引
CREATE INDEX idx_fgedu_order_customer_id ON fgedu_order(customer_id);

# 再次测试视图性能
EXPLAIN ANALYZE SELECT * FROM v_fgedu_customer_order WHERE customer_id = 1;

# 优化视图定义
CREATE OR REPLACE VIEW v_fgedu_customer_order AS
SELECT
c.id AS customer_id,
c.name AS customer_name,
c.email,
c.phone,
o.id AS order_id,
o.order_no,
o.amount,
o.order_date,
o.status
FROM fgedu_customer c
LEFT JOIN fgedu_order o ON c.id = o.customer_id
WHERE o.status = ‘已完成’;

# 测试优化后的视图性能
EXPLAIN ANALYZE SELECT * FROM v_fgedu_customer_order WHERE customer_id = 1;

输出日志：

# 创建测试表输出
fgedudb=> CREATE TABLE fgedu_customer (
id SERIAL PRIMARY KEY,
name VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE,
phone VARCHAR(20),
create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE

fgedudb=> CREATE TABLE fgedu_order (
id SERIAL PRIMARY KEY,
order_no VARCHAR(50) NOT NULL,
customer_id INTEGER REFERENCES fgedu_customer(id),
amount DECIMAL(10,2),
order_date DATE NOT NULL,
status VARCHAR(20)
);
CREATE TABLE

# 插入测试数据输出
fgedudb=> INSERT INTO fgedu_customer (name, email, phone) VALUES
(‘张三’, ‘zhangsan@fgedu.net.cn’, ‘13800138001’),
(‘李四’, ‘lisi@fgedu.net.cn’, ‘13800138002’),
(‘王五’, ‘wangwu@fgedu.net.cn’, ‘13800138003’);
INSERT 0 3

fgedudb=> INSERT INTO fgedu_order (order_no, customer_id, amount, order_date, status) VALUES
(‘ORD20230101001’, 1, 1000, ‘2023-01-01’, ‘已完成’),
(‘ORD20230102001’, 2, 2000, ‘2023-01-02’, ‘已完成’),
(‘ORD20230103001’, 1, 1500, ‘2023-01-03’, ‘已完成’),
(‘ORD20230104001’, 3, 2500, ‘2023-01-04’, ‘已完成’),
(‘ORD20230105001’, 2, 1800, ‘2023-01-05’, ‘已完成’);
INSERT 0 5

# 创建复杂视图输出
fgedudb=> CREATE VIEW v_fgedu_customer_order AS
SELECT
c.id AS customer_id,
c.name AS customer_name,
c.email,
c.phone,
o.id AS order_id,
o.order_no,
o.amount,
o.order_date,
o.status
FROM fgedu_customer c
LEFT JOIN fgedu_order o ON c.id = o.customer_id;
CREATE VIEW

# 测试视图性能输出
fgedudb=> EXPLAIN ANALYZE SELECT * FROM v_fgedu_customer_order WHERE customer_id = 1;
QUERY PLAN
————————————————————————————————————————
Hash Left Join (cost=1.17..2.21 rows=2 width=184) (actual time=0.010..0.012 rows=2 loops=1)
Hash Cond: (c.id = o.customer_id)
-> Seq Scan on fgedu_customer c (cost=0.00..1.03 rows=1 width=88) (actual time=0.005..0.005 rows=1 loops=1)
Filter: (id = 1)
Rows Removed by Filter: 2
-> Hash (cost=1.05..1.05 rows=5 width=96) (actual time=0.005..0.005 rows=5 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on fgedu_order o (cost=0.00..1.05 rows=5 width=96) (actual time=0.005..0.005 rows=5 loops=1)
Planning Time: 0.030 ms
Execution Time: 0.020 ms

# 为底层表创建索引输出
fgedudb=> CREATE INDEX idx_fgedu_order_customer_id ON fgedu_order(customer_id);
CREATE INDEX

# 再次测试视图性能输出
fgedudb=> EXPLAIN ANALYZE SELECT * FROM v_fgedu_customer_order WHERE customer_id = 1;
QUERY PLAN
————————————————————————————————————————
Nested Loop Left Join (cost=0.29..2.15 rows=2 width=184) (actual time=0.010..0.012 rows=2 loops=1)
-> Seq Scan on fgedu_customer c (cost=0.00..1.03 rows=1 width=88) (actual time=0.005..0.005 rows=1 loops=1)
Filter: (id = 1)
Rows Removed by Filter: 2
-> Index Scan using idx_fgedu_order_customer_id on fgedu_order o (cost=0.29..1.09 rows=2 width=96) (actual time=0.005..0.005 rows=2 loops=1)
Index Cond: (customer_id = 1)
Planning Time: 0.030 ms
Execution Time: 0.020 ms

# 优化视图定义输出
fgedudb=> CREATE OR REPLACE VIEW v_fgedu_customer_order AS
SELECT
c.id AS customer_id,
c.name AS customer_name,
c.email,
c.phone,
o.id AS order_id,
o.order_no,
o.amount,
o.order_date,
o.status
FROM fgedu_customer c
LEFT JOIN fgedu_order o ON c.id = o.customer_id
WHERE o.status = ‘已完成’;
CREATE VIEW

# 测试优化后的视图性能输出
fgedudb=> EXPLAIN ANALYZE SELECT * FROM v_fgedu_customer_order WHERE customer_id = 1;
QUERY PLAN
————————————————————————————————————————
Nested Loop Left Join (cost=0.29..2.17 rows=2 width=184) (actual time=0.010..0.012 rows=2 loops=1)
-> Seq Scan on fgedu_customer c (cost=0.00..1.03 rows=1 width=88) (actual time=0.005..0.005 rows=1 loops=1)
Filter: (id = 1)
Rows Removed by Filter: 2
-> Index Scan using idx_fgedu_order_customer_id on fgedu_order o (cost=0.29..1.11 rows=2 width=96) (actual time=0.005..0.005 rows=2 loops=1)
Index Cond: (customer_id = 1)
Filter: ((status)::text = ‘已完成’::text)
Planning Time: 0.030 ms
Execution Time: 0.020 ms

# 连接数据库
yassql -U fgedu -P fgedu123 -d fgedudb

# 创建测试表
CREATE TABLE fgedu_sales (
id SERIAL PRIMARY KEY,
product_id INTEGER,
quantity INTEGER,
price DECIMAL(10,2),
sale_date DATE NOT NULL,
region VARCHAR(50)
);

# 插入大量测试数据
INSERT INTO fgedu_sales (product_id, quantity, price, sale_date, region)
SELECT
(random() * 100)::integer + 1,
(random() * 10)::integer + 1,
(random() * 1000)::decimal(10,2),
‘2023-01-01’::date + (random() * 365)::integer,
CASE WHEN random() > 0.5 THEN ‘北区’ ELSE ‘南区’ END
FROM generate_series(1, 100000);

# 创建物化视图
CREATE MATERIALIZED VIEW mv_fgedu_sales_summary AS
SELECT
region,
DATE_TRUNC(‘month’, sale_date) AS month,
product_id,
COUNT(*) AS sale_count,
SUM(quantity) AS total_quantity,
SUM(price * quantity) AS total_amount
FROM fgedu_sales
GROUP BY region, DATE_TRUNC(‘month’, sale_date), product_id
ORDER BY region, month, product_id;

# 为物化视图创建索引
CREATE INDEX idx_mv_fgedu_sales_summary_region ON mv_fgedu_sales_summary(region);
CREATE INDEX idx_mv_fgedu_sales_summary_month ON mv_fgedu_sales_summary(month);
CREATE INDEX idx_mv_fgedu_sales_summary_product ON mv_fgedu_sales_summary(product_id);

# 测试物化视图查询性能
EXPLAIN ANALYZE SELECT * FROM mv_fgedu_sales_summary WHERE region = ‘北区’ AND month = ‘2023-01-01’::date;

# 测试增量刷新
# 插入新数据
INSERT INTO fgedu_sales (product_id, quantity, price, sale_date, region)
SELECT
(random() * 100)::integer + 1,
(random() * 10)::integer + 1,
(random() * 1000)::decimal(10,2),
‘2023-01-01’::date + (random() * 365)::integer,
CASE WHEN random() > 0.5 THEN ‘北区’ ELSE ‘南区’ END
FROM generate_series(1, 10000);

# 刷新物化视图
REFRESH MATERIALIZED VIEW mv_fgedu_sales_summary;

# 测试刷新后的查询性能
EXPLAIN ANALYZE SELECT * FROM mv_fgedu_sales_summary WHERE region = ‘北区’ AND month = ‘2023-01-01’::date;

# 删除物化视图
DROP MATERIALIZED VIEW IF EXISTS mv_fgedu_sales_summary;

输出日志：

# 创建测试表输出
fgedudb=> CREATE TABLE fgedu_sales (
id SERIAL PRIMARY KEY,
product_id INTEGER,
quantity INTEGER,
price DECIMAL(10,2),
sale_date DATE NOT NULL,
region VARCHAR(50)
);
CREATE TABLE

# 插入大量测试数据输出
fgedudb=> INSERT INTO fgedu_sales (product_id, quantity, price, sale_date, region)
SELECT
(random() * 100)::integer + 1,
(random() * 10)::integer + 1,
(random() * 1000)::decimal(10,2),
‘2023-01-01’::date + (random() * 365)::integer,
CASE WHEN random() > 0.5 THEN ‘北区’ ELSE ‘南区’ END
FROM generate_series(1, 100000);
INSERT 0 100000

# 创建物化视图输出
fgedudb=> CREATE MATERIALIZED VIEW mv_fgedu_sales_summary AS
SELECT
region,
DATE_TRUNC(‘month’, sale_date) AS month,
product_id,
COUNT(*) AS sale_count,
SUM(quantity) AS total_quantity,
SUM(price * quantity) AS total_amount
FROM fgedu_sales
GROUP BY region, DATE_TRUNC(‘month’, sale_date), product_id
ORDER BY region, month, product_id;
CREATE MATERIALIZED VIEW

# 为物化视图创建索引输出
fgedudb=> CREATE INDEX idx_mv_fgedu_sales_summary_region ON mv_fgedu_sales_summary(region);
CREATE INDEX

fgedudb=> CREATE INDEX idx_mv_fgedu_sales_summary_month ON mv_fgedu_sales_summary(month);
CREATE INDEX

fgedudb=> CREATE INDEX idx_mv_fgedu_sales_summary_product ON mv_fgedu_sales_summary(product_id);
CREATE INDEX

# 测试物化视图查询性能输出
fgedudb=> EXPLAIN ANALYZE SELECT * FROM mv_fgedu_sales_summary WHERE region = ‘北区’ AND month = ‘2023-01-01’::date;
QUERY PLAN
—————————————————————————————————————————————
Bitmap Heap Scan on mv_fgedu_sales_summary (cost=8.56..12.58 rows=1 width=68) (actual time=0.010..0.012 rows=100 loops=1)
Recheck Cond: (((region)::text = ‘北区’::text) AND (month = ‘2023-01-01’::date))
Heap Blocks: exact=1
-> BitmapAnd (cost=8.56..8.56 rows=1 width=0) (actual time=0.005..0.005 rows=0 loops=1)
-> Bitmap Index Scan on idx_mv_fgedu_sales_summary_region (cost=0.00..4.28 rows=500 width=0) (actual time=0.005..0.005 rows=500 loops=1)
Index Cond: ((region)::text = ‘北区’::text)
-> Bitmap Index Scan on idx_mv_fgedu_sales_summary_month (cost=0.00..4.28 rows=200 width=0) (actual time=0.005..0.005 rows=200 loops=1)
Index Cond: (month = ‘2023-01-01’::date)
Planning Time: 0.030 ms
Execution Time: 0.020 ms

# 插入新数据输出
fgedudb=> INSERT INTO fgedu_sales (product_id, quantity, price, sale_date, region)
SELECT
(random() * 100)::integer + 1,
(random() * 10)::integer + 1,
(random() * 1000)::decimal(10,2),
‘2023-01-01’::date + (random() * 365)::integer,
CASE WHEN random() > 0.5 THEN ‘北区’ ELSE ‘南区’ END
FROM generate_series(1, 10000);
INSERT 0 10000

# 刷新物化视图输出
fgedudb=> REFRESH MATERIALIZED VIEW mv_fgedu_sales_summary;
REFRESH MATERIALIZED VIEW

# 测试刷新后的查询性能输出
fgedudb=> EXPLAIN ANALYZE SELECT * FROM mv_fgedu_sales_summary WHERE region = ‘北区’ AND month = ‘2023-01-01’::date;
QUERY PLAN
—————————————————————————————————————————————
Bitmap Heap Scan on mv_fgedu_sales_summary (cost=8.56..12.58 rows=1 width=68) (actual time=0.010..0.012 rows=110 loops=1)
Recheck Cond: (((region)::text = ‘北区’::text) AND (month = ‘2023-01-01’::date))
Heap Blocks: exact=1
-> BitmapAnd (cost=8.56..8.56 rows=1 width=0) (actual time=0.005..0.005 rows=0 loops=1)
-> Bitmap Index Scan on idx_mv_fgedu_sales_summary_region (cost=0.00..4.28 rows=550 width=0) (actual time=0.005..0.005 rows=550 loops=1)
Index Cond: ((region)::text = ‘北区’::text)
-> Bitmap Index Scan on idx_mv_fgedu_sales_summary_month (cost=0.00..4.28 rows=220 width=0) (actual time=0.005..0.005 rows=220 loops=1)
Index Cond: (month = ‘2023-01-01’::date)
Planning Time: 0.030 ms
Execution Time: 0.020 ms

# 删除物化视图输出
fgedudb=> DROP MATERIALIZED VIEW IF EXISTS mv_fgedu_sales_summary;
DROP MATERIALIZED VIEW