49 物化视图

-- A materialized view: aggregate per customer
DROP MATERIALIZED VIEW IF EXISTS customer_spend_mv;
CREATE MATERIALIZED VIEW customer_spend_mv AS
SELECT c.id              AS customer_id,
       c.name            AS customer_name,
       count(o.id)       AS order_count,
       COALESCE(sum(o.total), 0) AS total_spent,
       max(o.id)         AS last_order_id
FROM customers_example c
LEFT JOIN orders_example o ON o.customer_id = c.id
GROUP BY c.id, c.name;

-- Query it like a table
SELECT * FROM customer_spend_mv ORDER BY total_spent DESC;

-- Index it (a matview is a real table, so it supports indexes)
CREATE UNIQUE INDEX customer_spend_mv_pk ON customer_spend_mv (customer_id);
CREATE INDEX customer_spend_mv_total_idx ON customer_spend_mv (total_spent);

-- Add another order, then refresh
INSERT INTO orders_example VALUES (200, 1, 25.00);

-- Plain REFRESH locks the matview against reads for the duration
REFRESH MATERIALIZED VIEW customer_spend_mv;

-- CONCURRENTLY lets readers continue. Requires a UNIQUE index on the matview.
REFRESH MATERIALIZED VIEW CONCURRENTLY customer_spend_mv;

SELECT * FROM customer_spend_mv ORDER BY total_spent DESC;

-- Find matviews and when they were last refreshed (PostgreSQL keeps the relation modification time)
SELECT schemaname, matviewname, hasindexes, ispopulated
FROM pg_matviews
WHERE schemaname = 'public';

DROP MATERIALIZED VIEW IF EXISTS customer_spend_mv;