materialized

create material view to increase database speed

-- if required, drop to create new
DROP MATERIALIZED VIEW user_purchase_summary;

-- create table
CREATE MATERIALIZED VIEW user_purchase_summary_view AS SELECT
  u.id as user_id,
  create_time,
  COUNT(*) as total_purchases,
  SUM(CASE when p.status = 'cancelled' THEN 1 ELSE 0 END) as cancelled_purchases
FROM users u
JOIN purchases p ON p.user_id = u.id
WITH DATA / WITH NO DATA
;

-- create index
CREATE UNIQUE INDEX idx_upsv_id ON user_purchase_summary_view("user_id");
CREATE INDEX idx_upsv_ct ON public.user_purchase_summary_view USING btree ("create_time");

-- load data
REFRESH MATERIALIZED view user_purchase_summary_view;

-- speedup the view refresh using CONCURRENTLY
REFRESH MATERIALIZED view CONCURRENTLY user_purchase_summary_view;

-- read the data
select * from user_purchase_summary_view limit 10;