答案：优化PostgreSQL子查询需用JOIN替代相关子查询、建立合适索引、利用CTE物化结果、避免深层嵌套。1. 相关子查询效率低，应改写为窗口函数或JOIN；2. 为子查询字段创建索引（如department_id, salary）；3. 使用MATERIALIZED CTE缓存高成本子查询；4. 用EXISTS替代IN，减少嵌套，提升可读性与执行效率。
PostgreSQL 中子查询的性能问题常见于复杂查询场景，尤其是嵌套层次深、数据量大或未合理使用索引时。优化子查询的核心在于减少扫描行数、避免重复执行以及将子查询重写为更高效的连接形式。
1. 尽量用 JOIN 替代相关子查询
相关子查询（Correlated Subquery）会对外表每一行执行一次，效率较低。若逻辑允许，应将其改写为 JOIN 或 LEFT JOIN。
例如，查找每个部门工资最高的员工：
低效写法（相关子查询）：
SELECT *
FROM employees e1
WHERE salary = (
SELECT MAX(salary)
FROM employees e2
WHERE e2.department_id = e1.department_id
);
高效写法（使用窗口函数 + JOIN）：
WITH ranked_employees AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) as rn
FROM employees
)
SELECT * FROM ranked_employees WHERE rn = 1;
这种改写避免了逐行执行子查询，大幅提升性能。
2. 确保子查询中使用索引
子查询涉及的字段（特别是 WHERE、JOIN、ORDER BY 条件中的列）应建立合适的索引。
- 对子查询中的过滤字段创建索引，如
department_id、status等。 - 复合索引需考虑查询顺序和选择性。
- 使用 EXPLAIN ANALYZE 查看执行计划，确认是否走索引。
示例：
CREATE INDEX idx_employees_dept_salary ON employees(department_id, salary DESC);
该复合索引可显著提升按部门取最高工资的子查询效率。
3. 使用 CTE 或临时表缓存结果
若子查询被多次引用或计算成本高，可用 WITH 子句（CTE）将其结果物化（尤其在 PostgreSQL 12+ 中支持 MATERIALIZED 提示）。
WITH dept_max_salary AS MATERIALIZED (
SELECT department_id, MAX(salary) as max_sal
FROM employees
GROUP BY department_id
)
SELECT e.*
FROM employees e
JOIN dept_max_salary d
ON e.department_id = d.department_id AND e.salary = d.max_sal;
显式物化避免重复执行，适合大数据集场景。
4. 避免不必要的子查询嵌套
深层嵌套子查询不仅难以维护，还可能阻碍优化器生成最优执行计划。
- 尽量扁平化结构，合并多个子查询为单层查询。
- 使用窗口函数替代多层聚合子查询。
- 利用 EXISTS 替代 IN 子查询（尤其当只需判断存在性时）。
例如，判断是否有员工工资高于某部门平均值：
推荐使用 EXISTS：
SELECT *
FROM employees e1
WHERE EXISTS (
SELECT 1
FROM departments d
JOIN employees e2 ON d.id = e2.department_id
WHERE d.id = e1.department_id
GROUP BY d.id
HAVING AVG(e2.salary) < e1.salary
);
EXISTS 在找到第一行后即停止，比 IN 更高效。
基本上就这些。关键在于理解执行计划、善用索引、减少重复计算，并灵活使用 JOIN 和 CTE 替代传统子查询模式。不复杂但容易忽略。
