In this lesson, you will learn how to look "under the hood" of your database using execution plans to see exactly why a query is taking longer than expected.
Peering Inside the Database Engine
When you write a SQL statement, you describe what data you want, but the database engine must figure out how to get it. Sometimes, the engine chooses an inefficient path, leading to slow performance. To diagnose this, we use the EXPLAIN command (or EXPLAIN ANALYZE in many systems).
By prefixing your query with EXPLAIN, the database returns an execution plan. This is a step-by-step roadmap showing how the engine retrieves your data.
Spotting the Bottleneck
An execution plan reveals several critical details. Here is what to look for:
- Table Scans: If you see
Seq Scan(Sequential Scan) orTable Scan, the engine is reading every single row in the table from start to finish. If your table has millions of rows, this is almost always the source of a slowdown. - Index Usage: You want to see
Index ScanorIndex Seek. This confirms the database is using the indexes we defined earlier to jump directly to the relevant data without checking irrelevant rows. - Cost Estimates: The plan often provides "costs" or "execution times" for each step. Look for the step with the highest value—that is your primary performance bottleneck.
Consider this example:
EXPLAIN SELECT * FROM orders WHERE customer_id = 55;
If the output says Seq Scan on orders, it means you are missing an index on customer_id. The database is forced to search the entire orders table just to find one customer's records. Once you add an index on that column, a subsequent EXPLAIN will likely show an Index Scan, which is significantly faster.
By reviewing these plans, you can stop guessing why a query is sluggish and start making targeted improvements, such as adding indexes or refining your filtering logic.
Key Takeaway: Use the EXPLAIN command to view your query's execution plan and identify if the database is performing costly full-table scans instead of efficient index lookups.