Query Optimization Based on Plan Analysis: EXPLAIN ANALYZE

Here’s the moment of truth: SQL queries aren’t just lines of code—they’re a real conversation with your database. If you whisper a sweet “SELECT *”, the database might get you and run the command without a fuss. But if you throw a messy SQL novel at it, the database might start thinking… and then start lagging.

Query optimization is all about speaking to your database in a clear and concise language. When your query is sharp and efficient, it runs fast, doesn’t overload the system, and doesn’t slow down other processes. But a poorly written query can drag the whole system down: the database starts eating up more CPU and memory, the disk subsystem gets busy with extra reads and writes, and even the apps using the database start to lag.

EXPLAIN ANALYZE helps you spot these trouble spots and figure out exactly where your query is “struggling.” It’s like diagnostics—you can’t really fix performance without it.

Common Query Issues and How to Spot Them

Now it’s time to meet the usual suspects behind performance drops. For this, we’ll arm ourselves with the EXPLAIN ANALYZE command.

Problem 1: Sequential Scan (Seq Scan)

Seq Scan (sequential scan) is when PostgreSQL looks for data by checking every row in the table. That’s fine if your table is tiny, but on big tables, this approach can be painful.

How do you know if Seq Scan is being used? Just run an analysis with EXPLAIN ANALYZE. Example:

EXPLAIN ANALYZE
SELECT * 
FROM students 
WHERE student_id = 123;

The result might look like this (check out the Seq Scan):

Seq Scan on students  (cost=0.00..35.50 rows=1 width=72) (actual time=0.010..0.015 rows=1 loops=1)

How do you fix it?

Create an index on student_id if you don’t have one:

CREATE INDEX idx_student_id ON students(student_id);

After that, run EXPLAIN ANALYZE again. You should see Index Scan instead of Seq Scan.

Problem 2: Low Selectivity Conditions

Selectivity is about how many rows you need to process to find what you want. If your filter covers almost the whole table, an index won’t save you.

Example of a low selectivity query:

EXPLAIN ANALYZE
SELECT * 
FROM students 
WHERE program = 'Computer Science';

If 90% of students in the table are in Computer Science, the query might use Seq Scan even if there’s an index on program.

How to improve the query?

1. Rethink your query logic: maybe you need to make the filter more specific by adding extra conditions.
2. Make sure your table stats are up to date (this helps PostgreSQL estimate selectivity correctly):

ANALYZE students;

3. If the query is using an index when it really shouldn’t, you can try to force PostgreSQL to use it:

SET enable_seqscan = OFF;

Problem 3: Extra Sorting Operations

Sorting (Sort) can be expensive, especially if the data doesn’t fit in RAM. A typical case that needs sorting is ORDER BY.

Example of the problem:

EXPLAIN ANALYZE
SELECT * 
FROM students
ORDER BY last_name;

You might see something like this:

Sort  (cost=123.00..126.00 rows=300 width=45) (actual time=1.123..1.234 rows=300 loops=1)

How can you speed up sorting? If you often sort by a certain column, you can create an index:

CREATE INDEX idx_last_name ON students(last_name);

Now PostgreSQL can use the index to pull data out in sorted order, skipping the extra sort operation.

Problem 4: No Limits (LIMIT)

When you run a SELECT without limiting the number of rows returned, the query might process the whole table, even if you only need the first row.

What it looks like:

EXPLAIN ANALYZE
SELECT * 
FROM students
WHERE gpa > 3.5;

If your database has a million rows and the gpa > 3.5 filter returns 80% of the table, you’ll probably be waiting a while.

If you only need the top 10 students, use LIMIT:

SELECT *
FROM students
WHERE gpa > 3.5
ORDER BY gpa DESC
LIMIT 10;

Also, you can use LIMIT with OFFSET to do pagination.

Managing Execution Parameters: SET

The SET command in PostgreSQL is used to change session or query parameters. It’s like a temporary setting that affects the database’s behavior only for the current connection.

In plain English, SET is a way to tweak PostgreSQL’s “mood” on the fly without changing global settings.

Where is this used?

• Change the language or date format before running a report.
• Increase memory for one heavy query.
• Turn off logging during a big data load.
• Temporarily change the schema search path (search_path).
• Manage security (like temporarily lowering user privileges).

General syntax

SET parameter = value;

To see the current value of a parameter, use:

SHOW parameter;

To reset to the default value:

RESET parameter;

Example of Complex Optimization

Let’s say you have a task: find the last 10 students with the highest GPA who are in Computer Science. Here’s the original query:

SELECT *
FROM students
WHERE program = 'Computer Science'
ORDER BY gpa DESC
LIMIT 10;

1. Query analysis: First, run EXPLAIN ANALYZE:

   EXPLAIN ANALYZE
   SELECT * 
   FROM students
   WHERE program = 'Computer Science'
   ORDER BY gpa DESC
   LIMIT 10;
   

   If you see sequential scan and sorting, that’s a sign you need to optimize.

2. Index on filter and sort:

   Create a composite index that includes both columns:

   CREATE INDEX idx_program_gpa
   ON students(program, gpa DESC);
   

3. Check for improvements:

   Run EXPLAIN ANALYZE again. Now the query should use the index above, skipping sorting and sequential scan.

Query Optimization Methodology

1. Start by analyzing the current execution plan. Use EXPLAIN ANALYZE to spot problem operations.

2. Identify bottlenecks. Find plan nodes that take the most time or use a lot of resources.

3. Set up indexes. Check which columns are used for filtering and sorting, and create the necessary indexes.

4. Minimize data volume. Use LIMIT, OFFSET, and precise filter conditions.

5. Update statistics. Run ANALYZE to make sure PostgreSQL has fresh info about your data distribution.

6. Test your changes. After optimizing, run EXPLAIN ANALYZE again to make sure performance actually improved.

What’s Next?

You just went through a crash course in query optimization. Congrats! The more you play with EXPLAIN ANALYZE, the better you’ll get at understanding PostgreSQL’s inner workings. And remember: no magic index will save you if your query is too complicated or vaguely written. SQL, like any other language, loves clarity.