Pre-Aggregation and Caching

DROP MATERIALIZED VIEW IF EXISTS mart.mv_region_group_month;
CREATE MATERIALIZED VIEW mart.mv_region_group_month AS
SELECT date_trunc('month', d.full_date)::date AS month, c.region, b.genre_group,
       p.status, sum(f.gross_amount) AS gross, sum(f.qty) AS copies, count(*) AS lines
FROM mart.fact_sales f JOIN mart.dim_date d USING (date_key)
JOIN mart.dim_customer c USING (customer_key) JOIN mart.dim_book b USING (book_key)
JOIN mart.dim_order_profile p USING (profile_key)
GROUP BY 1, 2, 3, 4;
SELECT count(*) AS mv_rows FROM mart.mv_region_group_month;
\timing on
SELECT c.region, sum(f.gross_amount) FROM mart.fact_sales f            -- from the facts
JOIN mart.dim_date d USING (date_key) JOIN mart.dim_customer c USING (customer_key)
JOIN mart.dim_order_profile p USING (profile_key)
WHERE d.year = 2026 AND p.status <> 'cancelled' GROUP BY 1 ORDER BY 1;
SELECT region, sum(gross) FROM mart.mv_region_group_month               -- from the rollup
WHERE month >= '2026-01-01' AND status <> 'cancelled' GROUP BY 1 ORDER BY 1;

 Americas     | 526089.22
...
Time: 55.583 ms
 Americas     | 526089.22
...
Time: 0.552 ms