Intro to Query Performance Analysis

Let me kick things off with a real story. Imagine you’re a developer working on a big e-commerce platform. Everything’s running smoothly, except for one tiny thing: the monthly sales report loads so slowly that your users have time to place another order before it finishes. You’re stressed, your manager’s stressed, the PostgreSQL server is boiling like a kettle, and it’s all starting to feel a bit apocalyptic. Now imagine you could just find the cause and fix it in one go.

Query performance analysis is like a health checkup for your PostgreSQL. It helps you find “bottlenecks” (pain points) and fix them to improve user experience and save system resources.

How do queries work under the hood in PostgreSQL?

When you write a simple query like:

SELECT * FROM products WHERE price > 100;

PostgreSQL doesn’t just jump in and grab the data. First, it analyzes your query, figures out the best way to run it, and only then gets to work.

Main stages of query execution:

1. Parsing. PostgreSQL checks your query for syntax errors and turns it into an intermediate representation.
2. Optimization. The query optimizer looks at different ways to run your query and picks the “cheapest” one (in terms of time and resources).
3. Execution. The server follows the chosen plan and fetches the data.

What’s a “bottleneck”?

A “bottleneck” is the part of a query that slows everything else down. It could be an operation that unexpectedly eats up most of the time or resources. For example, if PostgreSQL does a full table scan (Seq Scan) instead of a quick index lookup, your query gets slow. Or if there’s way more data than expected, and the server spends ages sorting, joining, or filtering.

These are the moments we call bottlenecks — and they’re exactly what you want to find and optimize first.

Tools for query performance analysis

PostgreSQL has a bunch of powerful tools to help you spot problems in your queries:

1. EXPLAIN and EXPLAIN ANALYZE. These commands show you how PostgreSQL plans to run your query, or even run it to measure real performance.

• EXPLAIN: shows the query execution plan without actually running the query.
• EXPLAIN ANALYZE: runs the query and shows the real execution plan with actual timing metrics.

Example of using EXPLAIN:

EXPLAIN SELECT * FROM products WHERE price > 100;

Output:

Seq Scan on products  (cost=0.00..35.50 rows=5 width=72)
  Filter: (price > 100)

This shows the query is doing a “Seq Scan” — a full table scan, which is super inefficient for big tables.

2. pg_stat_statements. This is an extra extension that keeps track of queries being run. It shows you:

• Which queries are running on the server.
• How much time each query takes.
• How many rows each query returns and how many resources it uses.

To enable pg_stat_statements, you need to:

1. Enable the extension:

CREATE EXTENSION pg_stat_statements;

2. Set up PostgreSQL config: In the postgresql.conf file, add:

   shared_preload_libraries = 'pg_stat_statements'
   pg_stat_statements.track = all

3. Restart PostgreSQL.

Now you can analyze queries:

SELECT * FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 5;

This will show the 5 “heaviest” queries, sorted by total execution time.

3. Slow query logging. You can set up PostgreSQL to log queries that take too long (like more than 1 second) into a log file.

For this, in postgresql.conf set:

log_min_duration_statement = 1000  # Time in milliseconds (1 second)

Now slow queries will be saved in the PostgreSQL logs.

Key metrics for performance analysis

When you’re analyzing query performance, keep an eye on these key metrics:

1. Execution time. The main metric for how long a query takes to run. The faster, the better.
2. Number of rows. If your query returns or scans more rows than you expected, that could be a problem.
3. Index usage. If a query should use an index but does a sequential scan (Seq Scan) instead, that’s a sign you need to optimize.
4. Buffers and disk operations. Queries that hit the disk a lot are slower than those that use data from memory.

How do you use this stuff in real life?

Example 1: Slow query

You write a query to select all products with price over 100:

SELECT * FROM products WHERE price > 100;

You notice the query is taking forever. You use EXPLAIN and see:

Seq Scan on products  (cost=0.00..35.50 rows=5 width=72)
  Filter: (price > 100)

The problem: the query is doing a full table scan because there’s no index on the price column.

Solution:

Create an index:

CREATE INDEX idx_price ON products(price);

Now the query uses Index Scan:

Index Scan using idx_price on products  (cost=0.15..8.25 rows=5 width=72)
  Index Cond: (price > 100)

Example 2: Finding slow queries with pg_stat_statements

With the command:

SELECT query, total_exec_time, calls
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 1;

You spot a query that’s eating up a ton of time. You open it up with EXPLAIN ANALYZE, fix it, and it gets faster.

Once you start using EXPLAIN, pg_stat_statements, and other tools, your queries will run faster and your PostgreSQL server will run like clockwork. In the next lecture, we’ll dive into the details of EXPLAIN parameters like cost, rows, and width, so you can start reading query plans like an open book.