This topic covers advanced techniques for optimizing database queries, including query execution plans, indexing strategies, query tuning, and performance optimization.
Database Query Optimization – Quick Reference Notes
| Concept | Description | Typical Impact |
|---|---|---|
| Cost Model | DBMS estimates I/O, CPU, and memory needed for each execution plan. | Guides the optimizer’s choice. |
| Selectivity | Fraction of rows a predicate returns (rows / total_rows). |
High selectivity → early filtering, index use. |
| Cardinality | Expected number of rows produced by an operation. | Influences join order & join method. |
| Statistics | Histograms, density, distinct values, column correlations. | Accurate stats → accurate cost estimates. |
| Execution Plan | Tree of operators (scan, join, sort, etc.) that the DB will run. | Visual map for tuning. |
| Hints/Directives | Manual overrides (e.g., USE INDEX, FORCE ORDER). |
Fine‑tune when optimizer mis‑chooses. |
Capture the Problem
EXPLAIN/EXPLAIN ANALYZE, SET STATISTICS IO ON, SET STATISTICS TIME ON.Analyze the Execution Plan
Check Statistics
ANALYZE, UPDATE STATISTICS, DBCC UPDATEUSAGE.Apply Tuning Techniques
JOIN hints or sub‑queries).TOP, LIMIT, FETCH FIRST).Validate
EXPLAIN ANALYZE.Monitor in Production
pg_stat_statements (PostgreSQL), V$SQL (Oracle), etc.| Index Type | When to Use | Key Points |
|---|---|---|
| B‑Tree | Equality, range predicates, ordering. | Composite index order matters; include columns to cover the query. |
| Bitmap | Low‑cardinality columns, data warehousing. | Great for many‑to‑many joins, but heavy on concurrent DML. |
| Hash | Exact equality lookups (e.g., in-memory engines). | No ordering; not useful for range queries. |
| Clustered/Primary Key | Defines physical row order. | Good for range scans; only one per table. |
| Covering Index | All columns needed are in the index (key + included). | Eliminates need to read the base table (index‑only scan). |