Database Query Execution Plan Interpretation and Performance Tuning in Practice

Problem Description:
When a database executes an SQL query, the query optimizer generates one or more execution plans deemed to be the most efficient. Database administrators or developers need to be able to interpret these execution plans, identify performance bottlenecks within them (such as full table scans, high-cost operations, etc.), and accordingly take targeted optimization measures. This article will explain in detail how to obtain and interpret execution plans, and how to perform SQL performance tuning based on the interpretation results.

Solution Process:

Step 1: Obtain the Query Execution Plan

Before any optimization, you first need to see the "battle map" chosen by the query optimizer for you—the execution plan. The method for obtaining it varies slightly among different Database Management Systems (DBMS).

1. In Oracle:

   • Use the EXPLAIN PLAN FOR statement.
   • Example: EXPLAIN PLAN FOR SELECT * FROM employees WHERE department_id = 10;
   • Then use SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); to view the formatted plan.

2. In MySQL (especially InnoDB):

   • Use the EXPLAIN or EXPLAIN FORMAT=JSON keyword.
   • Example: EXPLAIN SELECT * FROM employees WHERE department_id = 10;
   • FORMAT=JSON provides more detailed cost information.

3. In PostgreSQL:

   • Use the EXPLAIN or EXPLAIN (ANALYZE, BUFFERS) keyword.
   • Example: EXPLAIN ANALYZE SELECT * FROM employees WHERE department_id = 10;
   • ANALYZE will actually execute the statement and return the actual runtime. BUFFERS will show cache usage, which is crucial for analysis.

Step 2: Interpret the Key Components of the Execution Plan

The execution plan is usually displayed as a tree structure or in tabular form. You need to focus on the following core elements:

1. Operation Type: Each node in the plan represents an operation. Common operations include:

   • Full Table Scan: Sequentially reads all data in the table. Used when a query needs to process most of the data or the table is small and no usable index exists. Performance Warning: A full table scan on a large table is often a performance bottleneck.
   • Index Scan/Seek: Locates the required data rows through an index. This is usually much more efficient than a full table scan, especially when the required data constitutes only a small portion of the table.
   • Nested Loop Join: Suitable when one data set is small (outer table) and the other has an efficient index access path (inner table).
   • Hash Join: Typically used for equi-joins between large data sets without indexes. It builds a hash table for one table and then probes it with the other.
   • Sort: Executes ORDER BY or prepares data for a merge join. Sorting can consume a lot of memory and CPU if the data volume is large.

2. Cost Estimate: The optimizer estimates a relative cost value (Cost) for each operation step. This value has no absolute unit but can be used to compare the efficiency of different operations or plans. Higher total cost usually means a slower query. Your goal is to identify the operation step contributing the most to the cost.

3. Estimated Rows Returned: The optimizer estimates how many rows of data each operation will return. Key Point: Compare this estimate with the actual number of rows returned (if ANALYZE was used). A huge discrepancy between the estimated and actual values often indicates that the table's statistics are outdated or inaccurate, which can cause the optimizer to choose the wrong execution plan.

4. Data Access Method: Note how data is retrieved from disk or memory. For example, whether it's a direct read from the table (Seq Scan) or via an index (Index Scan).

Step 3: Identify Performance Bottlenecks and Formulate Optimization Strategies

Now, translate the interpreted information into concrete optimization actions.

1. Bottleneck: Full Table Scan

   • Cause: The column in the query condition (WHERE clause) lacks an index, or the index is unusable (e.g., a function is applied to the indexed column).
   • Optimization Strategy:
     • Create an Index: Create an appropriate index (e.g., B-tree index) on columns used for filtering, joining (JOIN), or sorting (ORDER BY). For example, creating an index on the department_id column: CREATE INDEX idx_emp_dept ON employees(department_id);
     • Ensure the Index is Enabled: Check if the query condition causes index invalidation (e.g., WHERE UPPER(name) = 'ALICE' invalidates an index based on name). Rewrite it into an index-friendly form, such as WHERE name = 'Alice'.

2. Bottleneck: High-Cost Join Operation or Sort

   • Cause: The joined tables are too large, or the sort field lacks an index.
   • Optimization Strategy:
     • Optimize Join Conditions: Ensure columns in the join condition (ON clause) have indexes.
     • Reduce the Amount of Data to Sort: Create an index on the sort field so the database may return data directly in index order, avoiding explicit sorting. Alternatively, consider whether SELECT * is truly necessary; selecting only the necessary columns can reduce the amount of data to sort.
     • Adjust Database Configuration: For example, increase work_mem (in PostgreSQL) to allow larger in-memory sorts, avoiding the use of disk temporary files.

3. Bottleneck: Inaccurate Optimizer Statistics

   • Symptom: The estimated number of rows in the execution plan differs from the actual number of rows by orders of magnitude.
   • Optimization Strategy:
     • Update Statistics: Regularly (or after significant data insertion, deletion, or modification) run the database's statistics update command. For example, GATHER_TABLE_STATS procedure in Oracle, ANALYZE TABLE in MySQL, and ANALYZE in PostgreSQL.

Step 4: Practical Case Study

Assume we have a query: SELECT e.name, d.department_name FROM employees e JOIN departments d ON e.department_id = d.id WHERE e.salary > 50000;

1. Obtain the Plan: Execute EXPLAIN FORMAT=JSON ... in MySQL, and find that the plan shows a full table scan on the employees table with a nested loop join.
2. Interpret the Bottleneck: The full table scan on employees is the highest cost operation because the condition salary > 50000 lacks index support.
3. Formulate a Strategy: Create an index on the employees.salary column: CREATE INDEX idx_emp_salary ON employees(salary);
4. Verify the Effect: Run EXPLAIN again and find the execution plan has changed to use the idx_emp_salary index for a range scan, with the total cost significantly reduced. If possible, use EXPLAIN ANALYZE to actually run the query and confirm the query time is indeed reduced.

By systematically obtaining, interpreting, analyzing, and verifying in this way, you can effectively perform performance tuning on database queries. Remember, optimization is an iterative process requiring repeated experimentation and measurement.