1) 代码分析摘要
- 功能：按日生成用户维度的汇总报表。步骤包括：筛选用户集(tmp_users) → 统计当日会话(tmp_sessions) → 统计当日订单(tmp_orders) → 获取当日最后一次事件(tmp_last_event) → 左连接入报表表。
- 主要访问模式：
- users：按创建时间与分群(segment)筛选。
- sessions：按起始时间的“某天范围”筛选并按用户聚合。
- orders：按创建时间的“某天范围”筛选并按用户聚合，并带状态条件的聚合过滤。
- events：按发生时间的“某天范围”筛选，按用户取当日最新一条（DISTINCT ON + ORDER BY occurred_at DESC）。
- 当前潜在性能问题集中在：
- 使用函数包裹时间列(date_trunc、date())导致索引无法使用（不可SARGable）。
- 基表上缺少对“时间范围 + user_id（或 segment）”的复合索引支撑。
- DISTINCT ON + ORDER BY + random() 不利于索引顺序使用。
- 临时表用于半连接/连接，但未对 join 键(user_id)建立索引，且统计信息不足。
2) 性能问题诊断
- 不可利用索引的谓词：
- sessions：WHERE date_trunc('day', s.start_time) = p_date → 将导致扫描大量行或全表扫描。
- orders：WHERE date(o.created_at) = p_date → 同上。
- events：WHERE date(e.occurred_at) = p_date → 同上。
- users：WHERE date_trunc('day', u.created_at) <= p_date → 同上。
- 连接/半连接形式与索引匹配：
- sessions/orders/events 使用 EXISTS/IN 关联 tmp_users，没有利用显式连接优化器的 Hash/Semi-Join 优势；且 tmp_users(user_id) 缺少索引。
- DISTINCT ON 取最新事件：
- ORDER BY e.user_id, e.occurred_at DESC, random() 迫使排序包含随机列，基本无法用到“按(user_id, occurred_at DESC)有序”的索引，仅能回退到排序/物化。
- 统计信息与执行计划稳定性：
- 临时表创建后未 ANALYZE，优化器对临时表行数与分布估计可能不准，影响连接算法选择与索引选择。
3) 优化建议（以“索引优化”为主，附必要的查询改写确保索引可用）
A. 将日期函数改写为可走索引的范围谓词
- 用半开区间替代 date_trunc/date：
- s.start_time >= p_date AND s.start_time < p_date + interval '1 day'
- o.created_at >= p_date AND o.created_at < p_date + interval '1 day'
- e.occurred_at >= p_date AND e.occurred_at < p_date + interval '1 day'
- users 创建时间条件改写：
- u.created_at < p_date + interval '1 day' 代替 date_trunc('day', u.created_at) <= p_date
B. 基表索引设计（优先级从高到低）
- sessions（大表、强时间序访问）
- 方案S1（按日过滤选择性高时优先）：CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_sessions_start_time_user ON sessions (start_time, user_id) INCLUDE (duration);
- 优点：高效按日扫描，并就地按 user_id 聚合；INCLUDE 可减少回表（索引仅在可用 index-only 的场景下显著收益）。
- 方案S2（当 tmp_users 很小、按用户过滤更强时考虑）：CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_sessions_user_start_time ON sessions (user_id, start_time) INCLUDE (duration);
- 二选一为主，避免双索引冗余。建议先上线 S1，观察计划与命中率，如 tmp_users 通常很小再评估 S2。
- orders
- 首选：CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_created_user ON orders (created_at, user_id) INCLUDE (status, amount);
- 支撑按日扫描并按用户聚合；INCLUDE 列配合索引仅扫描可减少回表。
- 不建议为 status='PAID' 单独建部分索引（partial index），因为本查询同时需要“全部订单”和“已支付订单”两类聚合，单个扫描难以同时利用两个不同索引。
- events
- 首选：CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_events_user_occ_desc ON events (user_id, occurred_at DESC);
- 与 DISTINCT ON (user_id) ... ORDER BY user_id, occurred_at DESC 完美匹配，可在允许移除 random() 时实现“每用户取最新”索引扫描。
- 如果保留 random() 作为并列时间戳的随机打散，索引仍能过滤时间范围与用户，但无法完全避免排序；仍然显著优于无索引。
- users
- 若 segment 选择性好：CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_users_segment_created ON users (segment, created_at);
- 若常常 segment='all'，优先：CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_users_created ON users (created_at);
- 二者只保留一种，依据实际查询比例选择，避免冗余。
- 辅助建议（可选，面向超大时间序表）
- 如果 sessions/orders/events 物理上基本按时间递增插入，且数据量巨大，可评估 BRIN 索引以节省存储并加速范围过滤（例如：CREATE INDEX ... USING BRIN(start_time)）。需确认表物理顺序与块相关性良好。
C. 临时表索引与统计信息
- 为连接键添加轻量索引（仅在行数较大时执行）：
- CREATE INDEX ON tmp_users(user_id);
- CREATE UNIQUE INDEX ON tmp_sessions(user_id);
- CREATE UNIQUE INDEX ON tmp_orders(user_id);
- CREATE UNIQUE INDEX ON tmp_last_event(user_id);
- 在创建并填充临时表后执行 ANALYZE，提高优化器估计精度：
- ANALYZE tmp_users; ANALYZE tmp_sessions; ANALYZE tmp_orders; ANALYZE tmp_last_event;
D. 连接与半连接写法
- 将 EXISTS/IN 改为显式 JOIN，通常可触发 Hash/Semi-Join 更稳定的计划，并与上述索引更好匹配。
E. 报表目标表（可选）
- 若需要防止重复插入或便于查询：为 daily_user_report 建立唯一索引
- CREATE UNIQUE INDEX IF NOT EXISTS ux_daily_user_report ON daily_user_report(date_key, user_id);
4) 重构代码示例
仅展示与索引生效相关的重写（范围谓词、JOIN、临时表索引与ANALYZE）；业务逻辑保持不变。
-- 推荐的永久索引（一次性部署，使用 CONCURRENTLY 降低锁影响）
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_users_created ON users (created_at);
-- 或：如果 segment 过滤选择性更强，改为：
-- CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_users_segment_created ON users (segment, created_at);
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_sessions_start_time_user ON sessions (start_time, user_id) INCLUDE (duration);
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_created_user ON orders (created_at, user_id) INCLUDE (status, amount);
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_events_user_occ_desc ON events (user_id, occurred_at DESC);
-- 可选（报表成品表避免重复）
-- CREATE UNIQUE INDEX IF NOT EXISTS ux_daily_user_report ON daily_user_report(date_key, user_id);
-- 存储过程（仅展示核心SQL的“前后对比式”重构）
CREATE OR REPLACE PROCEDURE sp_daily_user_report(p_date DATE, p_segment TEXT DEFAULT 'all')
LANGUAGE plpgsql
AS $$
BEGIN
-- 1) tmp_users：改写时间谓词为范围，避免对列使用函数；必要时建索引与ANALYZE
CREATE TEMP TABLE tmp_users AS
SELECT u.id AS user_id, u.created_at, u.segment, u.region
FROM users u
WHERE u.created_at < p_date + interval '1 day'
AND (p_segment = 'all' OR u.segment = p_segment);
CREATE INDEX ON tmp_users(user_id);
ANALYZE tmp_users;
-- 2) tmp_sessions：改为范围谓词 + 显式JOIN
CREATE TEMP TABLE tmp_sessions AS
SELECT s.user_id,
count(*) AS session_cnt,
sum(s.duration) AS total_duration
FROM sessions s
JOIN tmp_users tu ON tu.user_id = s.user_id
WHERE s.start_time >= p_date
AND s.start_time < p_date + interval '1 day'
GROUP BY s.user_id;
CREATE UNIQUE INDEX ON tmp_sessions(user_id);
ANALYZE tmp_sessions;
-- 3) tmp_orders：范围谓词 + JOIN
CREATE TEMP TABLE tmp_orders AS
SELECT o.user_id,
count(*) FILTER (WHERE o.status='PAID') AS paid_cnt,
sum(o.amount) FILTER (WHERE o.status='PAID') AS gm_value,
sum(o.amount) AS order_amt
FROM orders o
JOIN tmp_users tu ON tu.user_id = o.user_id
WHERE o.created_at >= p_date
AND o.created_at < p_date + interval '1 day'
GROUP BY o.user_id;
CREATE UNIQUE INDEX ON tmp_orders(user_id);
ANALYZE tmp_orders;
-- 4) tmp_last_event：范围谓词 + JOIN
-- 若业务允许放弃随机并列打散，可去掉 random()，显著提升利用 idx_events_user_occ_desc 的能力
CREATE TEMP TABLE tmp_last_event AS
SELECT DISTINCT ON (e.user_id)
e.user_id, e.event_name, e.occurred_at
FROM events e
JOIN tmp_users tu ON tu.user_id = e.user_id
WHERE e.occurred_at >= p_date
AND e.occurred_at < p_date + interval '1 day'
ORDER BY e.user_id, e.occurred_at DESC, random(); -- 如可移除 random()，更佳
CREATE UNIQUE INDEX ON tmp_last_event(user_id);
ANALYZE tmp_last_event;
-- 5) 最终装载
INSERT INTO daily_user_report(date_key, user_id, segment, session_cnt, total_duration, paid_orders, gm_value, last_event, last_event_time)
SELECT
p_date,
u.user_id,
u.segment,
COALESCE(s.session_cnt,0),
COALESCE(s.total_duration,0),
COALESCE(o.paid_cnt,0),
COALESCE(o.gm_value,0),
le.event_name,
le.occurred_at
FROM tmp_users u
LEFT JOIN tmp_sessions s ON s.user_id = u.user_id
LEFT JOIN tmp_orders o ON o.user_id = u.user_id
LEFT JOIN tmp_last_event le ON le.user_id = u.user_id;
RAISE NOTICE 'Report loaded for %, segment=%', p_date, p_segment;
DROP TABLE IF EXISTS tmp_last_event;
DROP TABLE IF EXISTS tmp_orders;
DROP TABLE IF EXISTS tmp_sessions;
DROP TABLE IF EXISTS tmp_users;
END;
$$;
5) 预期效果评估
- 范围谓词替代函数谓词：
- sessions/orders/events/users 均可命中时间或(time,user_id)复合索引，避免全表扫描。
- 在典型“当日数据仅占总量极小比例”的电商场景中，扫描行数可从“全表/全分区”降至“当日范围”，I/O 与CPU显著下降（数量级级别）。
- 复合索引与聚合匹配：
- (start_time, user_id) 与 (created_at, user_id) 索引将使聚合前的过滤高效，且便于按 user_id 分组。
- DISTINCT ON 最新事件：
- 移除 random() 时，(user_id, occurred_at DESC) 可实现“索引有序获取每用户首行”，避免排序与海量回表；保留 random() 时仍可利用索引做高效过滤，排序代价也将因输入集显著缩小而降低。
- 临时表索引与 ANALYZE：
- 在 tmp_* 行数较大时，能显著降低最终三次 LEFT JOIN 的成本，并使连接算法更稳定。
综合估计：在数据量较大的在线业务中，上述改造通常可带来数量级的查询时间缩短（例如从秒级/十秒级降至百毫秒-秒级，视数据量与硬件而定），并显著降低缓冲命中压力与I/O。
6) 实施注意事项
- 索引上线方式：
- 生产环境建议使用 CREATE INDEX CONCURRENTLY，避免长时间阻塞写入；上线后执行 ANALYZE 或等待 autovacuum 收敛统计信息。
- 索引选择与冗余控制：
- sessions 上的 S1 与 S2 不建议同时长期保留。先以 S1 为基线，通过 pg_stat_user_indexes 与 EXPLAIN 验证，再决定是否替换为 S2。
- 观察与回归确认：
- 使用 EXPLAIN (ANALYZE, BUFFERS) 对关键子查询在大日期样本上比对前后执行计划与耗时。
- 随机打散的业务确认：
- 如允许去掉 ORDER BY ... random()，可进一步提升 events 子查询性能；否则保持现状以保证业务语义正确。
- 临时表索引的条件执行：
- 若 tmp_* 行数很小（如 < 数万），临时索引的收益可能有限，可按阈值条件化创建，避免不必要的开销。
- 数据增长策略：
- 若数据持续高速增长，后续可评估基于时间字段的分区策略（范围分区），与上述索引方案并行使用，进一步缩小扫描范围。此为后续演进方向，非本次必需。
