Performance Analysis of Functions and Procedures: Using EXPLAIN ANALYZE

EXPLAIN ANALYZE helps you get inside PostgreSQL's "head" when it runs your query:

• What steps are taken to process your data.
• How much time each step takes.
• Why a certain query is slow — maybe it's a full table scan (Seq Scan) or a missed index.

The EXPLAIN ANALYZE command actually runs your query and shows you how PostgreSQL optimizes the execution. Imagine taking apart a watch to see how its gears work. That's what EXPLAIN ANALYZE does, but for your SQL queries.

Syntax of EXPLAIN ANALYZE

Let's start simple. Here's the basic command:

EXPLAIN ANALYZE
SELECT * FROM students WHERE age > 20;

This query will run the SELECT and show you how PostgreSQL processes the data.

The EXPLAIN ANALYZE result is a query execution tree. Each level of the tree describes a step PostgreSQL takes:

• Operation Type — the type of operation (like Seq Scan, Index Scan).
• Cost — how "expensive" PostgreSQL thinks this operation is.
• Rows — how many rows are expected and how many actually came out.
• Time — how long the operation took.

Example output:

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

Check out Seq Scan on students. That means PostgreSQL is scanning EVERY row in the students table. If your table is big, this can be REALLY SLOW.

Examples of Using EXPLAIN ANALYZE

Let's break down a few practical examples where you'll learn to spot and fix query problems.

Example 1: Full Table Scan

EXPLAIN ANALYZE
SELECT * FROM students WHERE age > 20;

Output:

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

The problem here is that PostgreSQL is doing a Seq Scan, meaning it's checking every row in the table. If you've got millions of rows, this is gonna be a performance bottleneck.

Solution: let's create an index on the age column.

CREATE INDEX idx_students_age ON students(age);

Now run the same query:

EXPLAIN ANALYZE
SELECT * FROM students WHERE age > 20;

Output:

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

Now we see Index Scan instead of Seq Scan. Nice, now your query flies!

Example 2: Complex Query with JOIN

Let's say we've got two tables: students and courses. We want to get the names of students and the course names they're enrolled in.

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;

The output might look like this:

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

As you can see, PostgreSQL's got it under control: it's using indexes on the enrollments and courses tables, and the execution is fast. But if any of those indexes are missing, you might see a Seq Scan, which will slow things down.

Performance Optimization for Functions

Now imagine we've got a function that returns a list of students older than a certain age:

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;

We can analyze this function's performance with EXPLAIN ANALYZE:

EXPLAIN ANALYZE
SELECT * FROM get_students_older_than(20);

Speeding Up Function Execution

If your function is taking forever, maybe it's doing a full table scan. To fix that:

1. Make sure the column used in filters (age) is indexed.
2. Check how many rows are in the table and think about partitioning if you've got tons of data.

Bottlenecks and How to Fix Them

1. Full table scans (Seq Scan). Use indexes to speed up row lookups. But remember, too many indexes can slow down inserts.

2. Huge result sets. If your query returns millions of rows, think about adding filters (WHERE, LIMIT) or pagination (OFFSET).

3. "Expensive" operations. Some operations, like sorting, aggregation, or joining big tables, can eat up a lot of resources. Use indexes or break your queries into smaller steps.