Entity Framework+PostgreSQL百万级数据下API性能异常缓慢问题排查

我正在开发一个百万级数据规模的项目，用LINQ做数据操作，确保只在最后阶段才把数据映射到模型。.NET API处理小查询时没问题，但执行大查询要2-3秒；同时发起4-5个这类查询的话，服务器响应时间直接涨到12-20秒，明显存在性能瓶颈。之前用SQL Server没出现过这种问题，怀疑和PostgreSQL有关。已经试过PgBouncer连接池、执行Vacuum清理、调整work_mem参数、给表加索引这些优化手段，但都没效果。

以下是LINQ生成的查询语句，单条查询最长耗时2.8秒，部分查询速度更快：

SELECT count(*)::int
FROM smartboard.mvw_user_survey_and_profile AS m
WHERE m.received_at IS NOT NULL

SELECT avg(COALESCE(t.c, 0)::double precision)
FROM (
    SELECT NULL AS empty
) AS e
LEFT JOIN (
    SELECT count(*)::int AS c
    FROM smartboard.mvw_user_survey_and_profile AS m
    WHERE m.received_at IS NOT NULL
    GROUP BY m.survey_id
) AS t ON TRUE

SELECT avg(COALESCE(date_part('epoch', t.time_to_response) / 60.0, 0.0))
FROM (
    SELECT NULL AS empty
) AS e
LEFT JOIN (
    SELECT m.time_to_response
    FROM smartboard.mvw_user_survey_and_profile AS m
    WHERE m.time_to_response IS NOT NULL
) AS t ON TRUE

SELECT m.date_hour, m.week_day, round(avg(date_part('epoch', m.time_to_response) / 60.0)::numeric, 2)
FROM smartboard.mvw_user_survey_and_profile AS m
WHERE m.time_to_response IS NOT NULL
GROUP BY m.week_day, m.date_hour
ORDER BY m.week_day, m.date_hour

SELECT COALESCE(m.week_seniority::text, ''), round(CAST((count(*) FILTER (WHERE m.received_at IS NOT NULL)::int::double precision * 100.0) AS numeric) / count(*)::int::numeric, 2)
FROM smartboard.mvw_user_survey_and_profile AS m
WHERE m.week_seniority > 0
GROUP BY m.week_seniority
ORDER BY m.week_seniority

排查方向与优化建议 #

• 视图底层逻辑检查
  所有查询都依赖smartboard.mvw_user_survey_and_profile视图，先查看视图定义：如果是基于多表复杂关联、嵌套子查询或者实时计算逻辑，会导致每次查询都要全量扫描计算。用EXPLAIN ANALYZE执行这些查询，看执行计划里的扫描类型、行数预估是否准确、索引是否被正确命中。
• 清理LINQ生成的冗余查询结构
  第二条和第三条查询里的LEFT JOIN (SELECT NULL AS empty) AS e ON TRUE完全冗余，PostgreSQL可能无法自动优化掉这种无意义关联，带来额外开销。调整LINQ逻辑，直接查询子查询的聚合结果即可。
• 并发资源配置优化
  单条查询2-3秒，4-5条并发就翻倍耗时，说明是并发资源争抢问题。检查PostgreSQL的max_connections、shared_buffers、effective_cache_size等配置是否匹配服务器硬件；同时监控服务器CPU、内存、磁盘IO在并发时的使用率，如果磁盘IO跑满，需优化存储或增加缓存层。
• 聚合查询的索引优化
  针对分组聚合的查询，在视图对应的基表上创建复合索引，比如：

  CREATE INDEX idx_survey_received ON 基表名 (survey_id, received_at);
  CREATE INDEX idx_time_week_date ON 基表名 (week_day, date_hour, time_to_response);
  CREATE INDEX idx_seniority_received ON 基表名 (week_seniority, received_at);
  

  如果是物化视图，要定期刷新并在物化视图上建索引；普通视图的索引需建在基表上。
• 减少数据类型转换开销
  查询里大量的::int、::double precision类型转换，百万级数据下累计开销不可忽视。尽量在视图定义或基表中就保持合适的数据类型，避免查询时重复转换。
• 合并并发查询减少重复扫描
  把多个独立的聚合查询合并成一条SQL，用CTE一次性计算所有结果，减少数据库连接开销和重复扫描。示例如下：

  WITH survey_stats AS (
      SELECT 
          COUNT(*) FILTER (WHERE received_at IS NOT NULL) AS total_received,
          AVG(COALESCE(date_part('epoch', time_to_response)/60.0, 0.0)) AS avg_time_to_response
      FROM smartboard.mvw_user_survey_and_profile
  ),
  survey_avg_count AS (
      SELECT AVG(COALESCE(count(*),0)::double precision) AS avg_survey_count
      FROM smartboard.mvw_user_survey_and_profile
      WHERE received_at IS NOT NULL
      GROUP BY survey_id
  ),
  time_by_hour AS (
      SELECT 
          date_hour, 
          week_day, 
          ROUND(AVG(date_part('epoch', time_to_response)/60.0)::numeric, 2) AS avg_time
      FROM smartboard.mvw_user_survey_and_profile
      WHERE time_to_response IS NOT NULL
      GROUP BY week_day, date_hour
  ),
  seniority_stats AS (
      SELECT 
          COALESCE(week_seniority::text, '') AS week_seniority,
          ROUND(CAST((COUNT(*) FILTER (WHERE received_at IS NOT NULL)::int::double precision * 100.0) AS numeric)/COUNT(*)::int::numeric, 2) AS received_rate
      FROM smartboard.mvw_user_survey_and_profile
      WHERE week_seniority > 0
      GROUP BY week_seniority
  )
  SELECT 
      (SELECT total_received FROM survey_stats) AS total_received,
      (SELECT avg_survey_count FROM survey_avg_count) AS avg_survey_count,
      (SELECT avg_time_to_response FROM survey_stats) AS avg_time_to_response,
      (SELECT json_agg(t) FROM time_by_hour t) AS time_by_hour,
      (SELECT json_agg(s) FROM seniority_stats s) AS seniority_stats;
  

  这样只需要扫描视图一次，就能获取所有统计结果，并发时的资源占用会大幅降低。

内容的提问来源于stack exchange，提问作者John Leon