函数和过程的性能分析：用EXPLAIN ANALYZE

EXPLAIN ANALYZE能帮你搞懂PostgreSQL在执行你的查询时到底“怎么想”的：

• 处理数据时都做了哪些步骤。
• 每一步花了多少时间。
• 为啥某个查询慢——比如全表扫描（英文Seq Scan）或者没用上索引。

EXPLAIN ANALYZE其实会真的执行一下你的查询然后展示PostgreSQL是怎么优化执行的。你可以想象成拆开手表看看里面的机械怎么转的。EXPLAIN ANALYZE就是这么干的，只不过是对你的SQL查询。

EXPLAIN ANALYZE的语法

先来点简单的。最基础的命令长这样：

EXPLAIN ANALYZE
SELECT * FROM students WHERE age > 20;

这个查询会执行SELECT，然后展示PostgreSQL是怎么处理数据的。

EXPLAIN ANALYZE的结果是个查询执行树。树的每一层都描述了PostgreSQL执行的一个步骤：

• Operation Type — 操作类型（比如Seq Scan、Index Scan）。
• Cost — PostgreSQL觉得执行这个操作有多“贵”。
• Rows — 预计有多少行，实际结果有多少行。
• Time — 这个操作花了多少时间。

输出示例：

Seq Scan on students  (cost=0.00..35.50 rows=1000 width=64) (actual time=0.023..0.045 rows=250 loops=1)
  Filter: (age > 20)
Planning Time: 0.123 ms
Execution Time: 0.234 ms

注意Seq Scan on students。这说明PostgreSQL把students表的所有行都扫了一遍。如果表很大，这会超级慢。

EXPLAIN ANALYZE的用法例子

来看看几个实战例子，教你怎么找出并解决查询里的问题。

例子1：全表扫描

EXPLAIN ANALYZE
SELECT * FROM students WHERE age > 20;

输出：

Seq Scan on students  (cost=0.00..35.50 rows=1000 width=64) (actual time=0.023..0.045 rows=250 loops=1)
  Filter: (age > 20)
Planning Time: 0.123 ms
Execution Time: 0.234 ms

问题在于PostgreSQL用了Seq Scan，也就是把表里所有行都过了一遍。如果表有几百万行，这就成了性能瓶颈。

解决办法：给age字段建个索引。

CREATE INDEX idx_students_age ON students(age);

现在再执行同样的查询：

EXPLAIN ANALYZE
SELECT * FROM students WHERE age > 20;

输出：

Index Scan using idx_students_age on students  (cost=0.29..12.30 rows=250 width=64) (actual time=0.005..0.014 rows=250 loops=1)
  Index Cond: (age > 20)
Planning Time: 0.123 ms
Execution Time: 0.045 ms

你会看到Index Scan替代了Seq Scan。爽，现在查询飞快！

例子2：带JOIN的复杂查询

假设我们有两张表：students和courses。我们想查学生名字和他们选的课程名。

EXPLAIN ANALYZE
SELECT s.name, c.course_name
FROM students s
JOIN enrollments e ON s.id = e.student_id
JOIN courses c ON e.course_id = c.id;

输出可能长这样：

Nested Loop  (cost=1.23..56.78 rows=500 width=128) (actual time=0.123..2.345 rows=500 loops=1)
  -> Seq Scan on students s  (cost=0.00..12.50 rows=1000 width=64) (actual time=0.023..0.045 rows=1000 loops=1)
  -> Index Scan using idx_enrollments_student_id on enrollments e  (cost=0.29..2.345 rows=3 width=64) (actual time=0.005..0.014 rows=3 loops=1000)
  -> Index Scan using idx_courses_id on courses c  (cost=0.29..2.345 rows=3 width=64) (actual time=0.005..0.014 rows=3 loops=1000)
Execution Time: 2.456 ms

你看，PostgreSQL全都安排得明明白白：enrollments和courses表都用上了索引，执行很快。但如果某个索引没建，你就会看到Seq Scan，那就慢了。

函数性能优化

现在假设我们有个函数，返回大于某个年龄的学生列表：

CREATE OR REPLACE FUNCTION get_students_older_than(min_age INT)
RETURNS TABLE(id INT, name TEXT) AS $$
BEGIN
  RETURN QUERY
  SELECT id, name
  FROM students
  WHERE age > min_age;
END;
$$ LANGUAGE plpgsql;

我们可以用EXPLAIN ANALYZE分析这个函数的性能：

EXPLAIN ANALYZE
SELECT * FROM get_students_older_than(20);

让函数跑得更快

如果函数执行很慢，可能是因为全表扫描。要解决这个问题：

1. 确保你在过滤用的字段（age）上建了索引。
2. 查查表里有多少行，如果数据太多可以考虑分区。

瓶颈和解决办法

1. 全表扫描（Seq Scan）。 用索引加速查找。但注意，索引太多会拖慢插入速度。

2. 查询结果行数太多。 如果查询返回几百万行，考虑加点过滤条件（WHERE、LIMIT）或者分页（OFFSET）。

3. “贵”的操作。 有些操作，比如排序、聚合或者大表JOIN，会吃掉很多资源。用索引，或者把查询拆成几步来做。