Execution Plan Analysis and Optimization

Posted on: February 8, 2025 Posted by: rahulgite Comments: 0

Introduction

When dealing with large stored procedures or complex queries, it is essential to identify which part of the query is taking more time and optimize it. The execution plan is a vital tool in understanding how the database processes a query and helps in pinpointing performance bottlenecks.

1. Execution Plan in MySQL

Using EXPLAIN

To analyze a query’s execution plan in MySQL, use:

EXPLAIN SELECT * FROM person p
JOIN persontracker pt ON p.id = pt.person_id
WHERE p.status = 'active';

This will output details about how MySQL executes the query.

Using EXPLAIN ANALYZE (MySQL 8+)

EXPLAIN ANALYZE SELECT * FROM person p
JOIN persontracker pt ON p.id = pt.person_id
WHERE p.status = 'active';

Output Example:

| id | select_type | table  | type  | possible_keys | key     | key_len | ref  | rows  | Extra               |
|----|------------|--------|-------|---------------|---------|---------|------|-------|---------------------|
|  1 | SIMPLE     | p      | ALL   | idx_status    | NULL    | NULL    | NULL | 10000 | Using where         |
|  1 | SIMPLE     | pt     | ref   | fk_person_id  | fk_pid  | 4       | p.id |  5000 | Using index         |

Key Metrics to Analyze:

  • Type: Shows how the table is accessed.
    • ALL (Full Table Scan) – 🚨 Bad for performance.
    • INDEX (Index Scan) – ✅ Acceptable.
    • REF (Index Lookup) – ✅ Better than full scan.
  • Possible Keys: Suggested indexes.
  • Key Used: The index actually used.
  • Rows: Estimated number of rows read.
  • Extra: Important details like Using temporary or Using filesort indicate possible inefficiencies.

2. Execution Plan in PostgreSQL

Using EXPLAIN ANALYZE

EXPLAIN ANALYZE
SELECT * FROM person p
JOIN persontracker pt ON p.id = pt.person_id
WHERE p.status = 'active';

Output Example:

Nested Loop  (cost=0.15..10500.29 rows=5000 width=64)
  -> Index Scan using person_status_idx on person p  (cost=0.12..2000.05 rows=5000 width=32)
  -> Index Scan using fk_person_id on persontracker pt (cost=0.15..2.00 rows=1 width=32)

Key Metrics to Analyze:

  • Nested Loop: Shows the type of join used.
  • Index Scan: Indicates that an index is being used (good sign).
  • Cost: The lower the cost, the better.
  • Rows: Estimate of processed rows.

3. Execution Plan in SQL Server

Using Graphical Execution Plan (SSMS)

  1. Open SQL Server Management Studio (SSMS).
  2. Run: SET STATISTICS IO ON; SET STATISTICS TIME ON; EXEC YourStoredProcedure;
  3. OR use: SET SHOWPLAN_XML ON;

Output Example:

  • Clustered Index Scan: 🚨 Expensive, avoid if possible.
  • Index Seek: ✅ Faster than a scan.
  • Hash Match vs. Nested Loops: Optimize join type.

4. Execution Plan in Oracle

Using EXPLAIN PLAN

EXPLAIN PLAN FOR
SELECT * FROM person p
JOIN persontracker pt ON p.id = pt.person_id
WHERE p.status = 'active';

Then check the plan:

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Output Example:

| Id  | Operation        | Name        | Rows  | Bytes | Cost |
|-----|-----------------|-------------|-------|-------|------|
|  1  | HASH JOIN       |             |  5000 | 160KB | 100  |
|  2  |  TABLE ACCESS   | PERSON      | 10000 | 320KB |  50  |
|  3  |  INDEX SCAN     | FK_PERSONID |  5000 | 160KB |  20  |

Key Metrics to Analyze:

  • FULL TABLE SCAN: Avoid unless necessary.
  • INDEX RANGE SCAN: Preferred.

5. How to Optimize Queries Based on Execution Plan

✔ Use Indexes

If FULL TABLE SCAN appears in the execution plan, add indexes.

CREATE INDEX idx_person_status ON person (status);

✔ Optimize Joins

Instead of:

SELECT * FROM table1 WHERE id IN (SELECT id FROM table2);

Use:

SELECT t1.* FROM table1 t1 JOIN table2 t2 ON t1.id = t2.id;

✔ Avoid Functions in WHERE Clause

Bad:

SELECT * FROM orders WHERE YEAR(order_date) = 2024;

Better:

SELECT * FROM orders WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01';

✔ Partition Large Tables

If a table has millions of rows, use Partitioning to speed up queries.

ALTER TABLE orders PARTITION BY RANGE (order_date) (
    PARTITION p2023 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD')),
    PARTITION p2024 VALUES LESS THAN (TO_DATE('2025-01-01', 'YYYY-MM-DD'))
);

✔ Reduce SELECT * Usage

Instead of:

SELECT * FROM orders;

Use:

SELECT order_id, customer_id, order_date FROM orders;

6. Final Steps

  1. Run EXPLAIN ANALYZE before and after optimization to verify improvements.
  2. Monitor query execution time using logs.
  3. Re-test performance with production-like data.

Using these strategies, you can pinpoint which part of your stored procedure or big query needs optimization and improve overall database performance! 🚀

Leave a Comment