Reading Plans and Improving Slow Queries

Goal

You will be able to read execution plans, confirm directly how the presence of an index, column order, function conditions, and covering change the plan, improve N+1 with a join, and leave a tuning report.

Why it matters

Reading an execution plan is not skimming node names but finding the gap between the optimizer's prediction and the actual result. And cost is a relative score, not time, so a criterion like "a cost over 10000 is dangerous" has no basis. Another important attitude — a full scan is not always bad. For a query that must read most of the rows, a scan is the best, and the reflexive reaction "I see a scan, so let's make an index" only adds indexes and slows writes. This lab is where you make that judgment yourself while looking at the plan.

Steps

1. Create /root/db/tune.db and run /opt/lab/fixtures/dbo/gen.sql to create the SALES table. It must have 200,000 rows. Columns: SALE_ID, CUST_ID, SALE_DT (string YYYYMMDD), PROD_CD, AMT
2. Save the execution plan of the query below (Q1) to /root/db/plan1.txt.

   SELECT SALE_ID, SALE_DT, AMT FROM SALES
    WHERE CUST_ID = 'C000123' AND SALE_DT BETWEEN '20260101' AND '20260630';
   

   The plan must show SCAN. (It must be captured before you create the index. If you capture it after, the plan is different.)
3. Create the index IX_SALES_01 for Q1, and save the plan of the same query to /root/db/plan2.txt. The plan must show IX_SALES_01.
4. Create the index IX_SALES_BAD with the column order reversed, and create /root/db/composite.txt. It has three lines.

   good=<Q1 에 적합한 인덱스의 컬럼 순서, 쉼표 구분>
   bad=<부적합한 순서>
   reason=<한 줄 근거>
   

5. Rewrite the query below (Q2) into a form that can use an index and save it to /root/db/rewrite.sql.

   SELECT COUNT(*) FROM SALES WHERE substr(SALE_DT,1,6) = '202603';
   

   The rewritten query's result must be the same as the original, and when you save the execution plan to /root/db/plan3.txt, it must use an index. (Create an additional index if needed.)
6. Make the query below (Q3) be handled by a covering index alone.

   SELECT SALE_DT, AMT FROM SALES WHERE CUST_ID = 'C000123';
   

   Save the execution plan to /root/db/covering.txt, and the plan must show COVERING INDEX.
7. Using the table that /opt/lab/fixtures/dbo/gen.sql also created, CUSTOMER_M, write a query that finds "the top 10 customers by total sales (including customer name)" in /root/db/join.sql and save the result to /root/db/join-result.txt. The result is three columns CUST_ID,CUST_NM,TOTAL, 10 rows in descending TOTAL order. The query must be written with a single join (no repeated subqueries).
8. Write /root/db/tuning.md. It must have five h2 headings, ## 대상, ## 증상, ## 원인, ## 조치, ## 결과 (in order: target, symptom, cause, action, result), and the body must include the three items below.
   • The difference between the step 2 and step 3 plans (SCAN → index)
   • What ANALYZE does and why it is needed right after a migration
   • Why an index slows INSERT

Notes

• Execution plan: EXPLAIN QUERY PLAN <쿼리>; (the placeholder is the query)
• Refreshing statistics: ANALYZE;
• Measuring time: .timer on
• Common mistake 1: creating an index and not running ANALYZE, so the plan stays the same.
• Common mistake 2: the rewritten query's result differs from the original. substr(SALE_DT,1,6)='202603' means March 1 through March 31.
• Common mistake 3: putting all the columns into the index in the name of making it covering. When the index becomes as large as the table, the benefit disappears.

Create a large table

Create /root/db/tune.db and run /opt/lab/fixtures/dbo/gen.sql to create the SALES table. It must have 200,000 rows. Columns: SALE_ID, CUST_ID, SALE_DT (string YYYYMMDD), PROD_CD, AMT

You can create a large number of rows with a recursive CTE. Check the row count after creating.

Execution plan without an index

Save the execution plan of the query below (Q1) to /root/db/plan1.txt.

SELECT SALE_ID, SALE_DT, AMT FROM SALES
 WHERE CUST_ID = 'C000123' AND SALE_DT BETWEEN '20260101' AND '20260630';

The plan must show SCAN. (It must be captured before you create the index. If you capture it after, the plan is different.)

The starting point of tuning is recording the state before improvement. Pay attention to which words appear in the plan.

Compare after creating the index

Create the index IX_SALES_01 for Q1, and save the plan of the same query to /root/db/plan2.txt. The plan must show IX_SALES_01.

See how the plan of the same query changes. Check whether the index name appears in the plan.

Composite index column order

Create the index IX_SALES_BAD with the column order reversed, and create /root/db/composite.txt. It has three lines.

good=<Q1 에 적합한 인덱스의 컬럼 순서, 쉼표 구분>
bad=<부적합한 순서>
reason=<한 줄 근거>

Equality-condition columns go first and range-condition columns after. If you create both orders and compare the plans, the difference becomes clear.

Remove the function condition

Rewrite the query below (Q2) into a form that can use an index and save it to /root/db/rewrite.sql.

SELECT COUNT(*) FROM SALES WHERE substr(SALE_DT,1,6) = '202603';

The rewritten query's result must be the same as the original, and when you save the execution plan to /root/db/plan3.txt, it must use an index. (Create an additional index if needed.)

If you wrap a column in a function, the index's sort order cannot be used. Try changing it to a range condition that produces the same result.

Covering index

Make the query below (Q3) be handled by a covering index alone.

SELECT SALE_DT, AMT FROM SALES WHERE CUST_ID = 'C000123';

Save the execution plan to /root/db/covering.txt, and the plan must show COVERING INDEX.

If all the columns the query needs are in the index, it does not read the table. An additional specific word appears in the plan.

N+1 into a join

Using the table that /opt/lab/fixtures/dbo/gen.sql also created, CUSTOMER_M,

write a query that finds "the top 10 customers by total sales (including customer name)" in /root/db/join.sql and save the result to /root/db/join-result.txt. The result is three columns CUST_ID,CUST_NM,TOTAL, 10 rows in descending TOTAL order. The query must be written with a single join (no repeated subqueries).

Turn repeated lookups into a single join. It counts as an improvement only if the result set is the same as the original.

Tuning report

Write /root/db/tuning.md. It must have five h2 headings, ## 대상, ## 증상, ## 원인, ## 조치, ## 결과 (in order: target, symptom, cause, action, result), and the body must include the three items below.

• The difference between the step 2 and step 3 plans (SCAN → index)
• What ANALYZE does and why it is needed right after a migration
• Why an index slows INSERT

This is a document you will need when someone tries to delete this index six months from now. Record the symptom, cause, action, and result with numbers.