Query Optimization¶
Query optimization separates data scientists who can write SQL from those who can write SQL that actually works in production. At small scale, any query finishes fast. At millions or billions of rows, the wrong approach can take minutes or hours. Interviewers ask optimization questions to test whether you understand what the database is doing, not just what the query says.
Q1: How does a SQL query actually execute? Walk through the stages.¶
Show answer
Understanding the execution pipeline helps you predict where a query will be slow and how the optimizer can help or hurt.
1. Parsing — the SQL text is checked for syntax errors and converted into a parse tree. This is fast and rarely a bottleneck.
2. Semantic analysis — the parser resolves table names, column names, and data types against the schema (the catalog). Catches errors like referencing a non-existent column.
3. Query rewriting — the database rewrites the logical query: expanding views, flattening subqueries into joins where possible, applying simplification rules. This is where many IN clauses get rewritten as semi-joins.
4. Optimization — the query optimizer generates multiple physical execution plans and estimates the cost of each. It uses table statistics (row counts, column cardinality, data distribution histograms) to estimate which plan is cheapest. The winning plan is chosen.
5. Execution — the query engine executes the chosen plan: reading data from disk or cache, applying filters and joins, computing aggregations, and returning the result.
The optimizer's choices depend on table statistics. Stale statistics (from tables that have grown significantly without a ANALYZE or UPDATE STATISTICS) cause the optimizer to choose bad plans. When a query suddenly gets slow after a data load, outdated statistics are often the culprit.
Q2: What is an index and how does it speed up queries?¶
Show answer
An index is a separate data structure — typically a B-tree — that maps column values to row locations, allowing the database to find matching rows without scanning every row in the table.
Without an index: a WHERE city = 'Berlin' query must read every row and check the city column — a sequential scan or full table scan. Cost grows linearly with table size: O(n).
With an index on city: the database traverses the B-tree to find rows where city = 'Berlin' in O(log n) steps, then reads only those rows from the table. For large tables, this is orders of magnitude faster.
-- Create an index
CREATE INDEX idx_employees_department ON employees (department);
-- The planner can now use this index for:
SELECT * FROM employees WHERE department = 'Engineering';
SELECT * FROM employees WHERE department IN ('Engineering', 'Product');
SELECT * FROM employees ORDER BY department; -- may use index for sort
Index types in PostgreSQL:
- B-tree (default) — supports equality and range queries (=, <, >, BETWEEN, ORDER BY)
- Hash — equality only (=), faster than B-tree for equality but not range-safe
- GIN — for array, JSONB, and full-text search
- GiST — for geometric and geographic data
Indexes have costs: they slow down INSERT, UPDATE, and DELETE (because the index must be updated), and they consume disk space. Add indexes based on actual query patterns, not speculatively.
Q3: When do indexes NOT help?¶
Show answer
Indexes are not universally beneficial. Understanding when they fail to help (or actively hurt) is as important as knowing when to create them.
1. Low-cardinality columns — a column with few distinct values (e.g., gender, is_active with only TRUE/FALSE) makes an index inefficient. If 50% of rows match is_active = TRUE, the database reads half the table anyway — a sequential scan may be faster because it avoids the B-tree traversal overhead.
2. Small tables — for a table with a few hundred rows, a sequential scan is faster than an index lookup. The planner will ignore the index.
3. Function on the indexed column in WHERE — applying a function to an indexed column prevents the index from being used:
-- Index on created_at is NOT used:
WHERE EXTRACT(YEAR FROM created_at) = 2024
-- Index IS used:
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01'
4. Leading wildcard in LIKE — WHERE name LIKE '%smith' cannot use a B-tree index on name because the matching character is unknown at the start. Only LIKE 'smith%' is index-compatible.
5. Implicit type cast — joining or filtering on mismatched types forces an implicit cast on the indexed column, disabling the index:
-- If user_id is INTEGER, this cast prevents index use:
WHERE user_id = '12345' -- string literal coerced to integer
6. NOT conditions — WHERE col != value or WHERE col NOT IN (...) generally cannot use a B-tree index efficiently, because the database must return most rows anyway.
Q4: How do you read EXPLAIN / EXPLAIN ANALYZE output?¶
Show answer
EXPLAIN shows the execution plan the database will use without running the query. EXPLAIN ANALYZE runs the query and shows actual execution times alongside estimates.
EXPLAIN ANALYZE
SELECT e.name, d.dept_name
FROM employees e
JOIN departments d ON e.dept_id = d.id
WHERE e.salary > 80000;
Example output nodes and what they mean:
- Seq Scan — reading every row in the table. Check whether an index would help.
- Index Scan — using a B-tree to find matching rows. Good.
- Index Only Scan — all needed columns are in the index itself (covering index). Best.
- Bitmap Heap Scan — multiple index lookups combined before reading the table. Good for moderate selectivity.
- Hash Join — builds a hash table from the smaller side, probes with the larger. Good for large joins.
- Nested Loop — for each outer row, scans the inner side. Fast when the inner side is small and indexed; very slow when the inner side is large.
- Sort — explicit sort step, often indicating a missing index for ORDER BY or a merge join.
Key numbers to examine:
- rows= (estimate vs actual) — if the estimate is wildly off, statistics need updating (ANALYZE)
- cost= — the optimizer's relative cost estimate. Lower is better. Not wall-clock time.
- actual time= — real execution time in milliseconds. The bottleneck is the node with the highest actual time.
- Buffers: shared hit/read — cache hits vs disk reads. High disk reads indicate I/O-bound queries.
Q5: Why should you avoid SELECT * in production queries?¶
Show answer
SELECT * retrieves every column from every table in the query. In small scripts this is fine; in production it causes real problems.
1. Unnecessary data transfer — fetching 50 columns when you need 3 wastes network bandwidth and memory. On large tables with wide rows (many columns or TEXT/BLOB columns), this is significant.
2. Breaks downstream code — if a column is added, reordered, or removed from the table, SELECT * silently changes the result set. Applications that depend on column order (positional access) break. Explicit column lists make schema changes visible.
3. Prevents covering index use — a covering index (an index that includes all queried columns) allows an index-only scan. SELECT * forces the database to access the heap (the actual table rows) for every result row, defeating the optimisation.
4. Exposes sensitive columns — in a production API or reporting context, returning all columns may leak sensitive data (PII, internal flags) unintentionally.
-- Avoid in production
SELECT * FROM orders;
-- Explicit: only fetch what you need
SELECT order_id, customer_id, total_amount, order_date
FROM orders
WHERE order_date >= '2024-01-01';
The exception: exploratory queries in a local SQL client or notebook where you're investigating schema or data. Even then, LIMIT 10 with SELECT * is acceptable.
Q6: What does "filtering early" mean, and why does it matter?¶
Show answer
Filtering early means applying WHERE conditions as close to the source data as possible — before joins, before aggregations. This reduces the number of rows the database must process in subsequent steps.
Problematic pattern — filter after join:
-- Joins all orders to all customers, then discards most rows
SELECT c.name, o.amount
FROM customers c
JOIN orders o ON c.id = o.customer_id
WHERE o.order_date >= '2024-01-01'
AND c.country = 'Germany';
Modern query planners often push predicates down automatically (predicate pushdown). But complex queries with views, CTEs, or function calls can confuse the planner.
Explicit early filtering — push into a CTE or subquery:
WITH recent_orders AS (
SELECT * FROM orders WHERE order_date >= '2024-01-01'
),
german_customers AS (
SELECT * FROM customers WHERE country = 'Germany'
)
SELECT c.name, o.amount
FROM german_customers c
JOIN recent_orders o ON c.id = o.customer_id;
By filtering before the join, each side of the join is smaller, making the join faster. This matters most when:
- One table is very large and the filter is highly selective
- You're joining multiple large tables — filter each independently first
- The query planner is not pushing your predicates down automatically (verify with EXPLAIN)
Q7: Why does applying a function to an indexed column in WHERE disable the index?¶
Show answer
A B-tree index stores the raw column values in sorted order. When you apply a function to the column, the lookup value is transformed — and the transformed value is not stored in the index. The database cannot use the index to answer the lookup.
-- Creates an index on the raw hire_date value
CREATE INDEX idx_hire_date ON employees (hire_date);
-- NOT index-friendly: EXTRACT transforms hire_date into a different value
SELECT * FROM employees WHERE EXTRACT(YEAR FROM hire_date) = 2023;
-- Index-friendly: range comparison on the raw column value
SELECT * FROM employees
WHERE hire_date >= '2023-01-01' AND hire_date < '2024-01-01';
Other common violations:
-- Disables index on email:
WHERE LOWER(email) = 'user@example.com'
-- Fix: create a functional index
CREATE INDEX idx_email_lower ON users (LOWER(email));
-- Then the query can use it:
WHERE LOWER(email) = 'user@example.com'
-- Disables index on amount (type cast):
WHERE amount::TEXT = '100'
-- Fix: compare against the correct type
WHERE amount = 100
The pattern to remember: anything that transforms the column in the WHERE clause prevents index use on that column. Rewrite the comparison to isolate the raw column on one side.
Q8: What is table partitioning and how does it help analytical queries?¶
Show answer
Partitioning divides a large table into smaller physical segments (partitions) based on a column's value, while presenting them as a single logical table. The most common strategy for analytics is partitioning by date.
-- Create a partitioned table (PostgreSQL)
CREATE TABLE orders (
order_id BIGINT,
order_date DATE NOT NULL,
amount NUMERIC
) PARTITION BY RANGE (order_date);
-- Create individual partitions
CREATE TABLE orders_2023 PARTITION OF orders
FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');
CREATE TABLE orders_2024 PARTITION OF orders
FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');
Partition pruning — when a query filters on the partition key, the planner reads only the relevant partitions:
-- Reads only orders_2024 partition, not the full table
SELECT * FROM orders WHERE order_date >= '2024-01-01';
Benefits:
- Faster queries: only relevant partitions are scanned
- Cheaper maintenance: dropping a partition (e.g., archiving old data) is instant, unlike DELETE
- Better parallelism: partitions can be processed in parallel
When partitioning helps most: - Tables with many years of historical data where most queries touch a narrow time range - Regular archival or deletion of old data - Large tables where full scans are unavoidable for some queries — partition pruning limits the scan
Partitioning does not help queries that do not filter on the partition key.
Q9: What are the most common slow query patterns and how do you fix them?¶
Show answer
These patterns appear repeatedly in slow query investigations:
1. N+1 query problem — running a separate query for each row of a result:
-- Bad: one query per customer to get their order count
-- SELECT COUNT(*) FROM orders WHERE customer_id = X -- run N times
-- Fix: join and aggregate once
SELECT c.id, c.name, COUNT(o.order_id) AS order_count
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.name;
2. Missing index on a foreign key or join column:
-- Check indexes
\d orders -- in psql, shows indexes
-- Add missing index
CREATE INDEX idx_orders_customer_id ON orders (customer_id);
3. Returning far more rows than needed — missing or misplaced filter:
-- Filter on the inner table early, not on the outer query
SELECT c.name, monthly_orders
FROM customers c
JOIN (
SELECT customer_id, COUNT(*) AS monthly_orders
FROM orders
WHERE order_date >= DATE_TRUNC('month', NOW()) -- filter here
GROUP BY customer_id
) recent ON c.id = recent.customer_id;
4. Using DISTINCT to hide a join bug — investigate why duplicates appear; fix the join instead of masking with DISTINCT.
5. Correlated subquery in SELECT — runs once per row; replace with a window function or a joined aggregate.
6. Aggregating before or after the right point — aggregating a huge table then joining is usually worse than joining first and aggregating the smaller result. Profile both with EXPLAIN ANALYZE.
Q10: When does denormalization improve query performance?¶
Show answer
Normalized schemas (3NF, BCNF) eliminate redundancy and ensure data consistency. But for analytical workloads with large tables and complex multi-table joins, denormalization — deliberately storing redundant data — can significantly improve query performance.
When to consider denormalization:
- A query joins 5+ tables and performance is unacceptable
- The same computed column (e.g., customer_country) is joined in nearly every analytical query
- The source tables are write-infrequent but the analytical queries are read-heavy
- You control an analytics-specific layer (data warehouse, materialized view) separate from the operational database
-- Normalized: requires joining orders → customers → regions every query
SELECT r.region_name, SUM(o.amount)
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN regions r ON c.region_id = r.id
GROUP BY r.region_name;
-- Denormalized: region_name stored directly in the orders_fact table
SELECT region_name, SUM(amount)
FROM orders_fact
GROUP BY region_name;
In practice, denormalization is used in: - Data warehouses — star schema (fact table with dimensions pre-joined as keys or directly embedded) - Materialized views — pre-computed joins stored on disk, refreshed on a schedule - Summary tables — pre-aggregated tables at common granularities (daily, monthly)
Trade-off: denormalization introduces data redundancy and update anomalies. Writes that update a dimension must also update all denormalized copies. This is acceptable in read-heavy analytics environments where writes are rare.
Q11: What is a covering index and why is it valuable?¶
Show answer
A covering index includes all columns that a query needs — both the columns in the WHERE/JOIN condition and the columns in the SELECT list. When all queried columns are in the index, the database can answer the query entirely from the index without touching the main table (heap). This is an index-only scan — significantly faster than an index scan that must look up each row in the heap.
-- Query: find recent high-value orders for a customer
SELECT order_id, order_date, amount
FROM orders
WHERE customer_id = 42 AND order_date >= '2024-01-01';
-- Standard index only covers the lookup:
CREATE INDEX idx_orders_cust ON orders (customer_id);
-- Covering index: includes the columns needed in SELECT and WHERE
CREATE INDEX idx_orders_cust_covering
ON orders (customer_id, order_date)
INCLUDE (order_id, amount);
-- PostgreSQL 11+: INCLUDE adds columns to the leaf but not the B-tree key
With the covering index, the query performs an index-only scan:
- Traverse the B-tree on (customer_id, order_date)
- Read order_id and amount from the index leaf pages
- No heap access needed
Without it, the database must:
- Use the index to find matching row locations
- Look up each row in the heap to retrieve order_id and amount
For high-frequency queries on large tables, covering indexes can reduce query time by 10x or more. The cost is extra storage and slower writes.
Q12: What is the difference between EXPLAIN and EXPLAIN ANALYZE, and what should you look for first?¶
Show answer
EXPLAIN shows the query plan the optimizer chose and its estimated costs. The query does not run.
EXPLAIN ANALYZE actually executes the query and shows both estimated and actual row counts and times.
-- Estimate only (fast, safe for expensive queries)
EXPLAIN SELECT * FROM orders WHERE customer_id = 42;
-- Execute and measure (use on queries you're willing to run)
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;
-- Full diagnostic output
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE customer_id = 42;
What to look for first (in priority order):
-
Seq Scan on large tables — if a large table has a sequential scan and you expected an index scan, the index is missing, unused (due to a function on the column), or the planner judged a scan cheaper.
-
Estimate vs actual row count mismatch — if the estimate says 10 rows but actual is 1,000,000, the optimizer is making bad decisions based on stale statistics. Run
ANALYZE table_name. -
Nested Loop on large tables — a nested loop joining two large tables is nearly always a mistake. Look for missing indexes on the inner side of the loop.
-
High actual time on a specific node — the node with the highest actual time is your bottleneck. Focus optimization effort there.
-
Sort operations — an explicit Sort node on large data is slow. An index on the ORDER BY column can eliminate it.
Workflow: run EXPLAIN ANALYZE, find the slowest node, diagnose the cause (missing index, stale stats, bad join order), fix it, and re-run to verify improvement.
Q13: How does JOIN order affect query performance?¶
Show answer
In theory, relational algebra allows joins in any order and the results are identical. In practice, the order determines how many intermediate rows the database must carry through subsequent steps — and that can vary by orders of magnitude.
A general principle: join the most selective condition first to reduce the intermediate row count as early as possible.
-- Less efficient: start with two large tables, then filter
SELECT *
FROM orders o -- 100M rows
JOIN customers c ON o.customer_id = c.id -- 5M rows
JOIN regions r ON c.region_id = r.id -- 200 rows
WHERE r.region_name = 'APAC';
-- More efficient (logically equivalent, planner may choose this):
-- Filter regions to 1 row first, then join customers, then orders
WITH apac_region AS (
SELECT id FROM regions WHERE region_name = 'APAC'
),
apac_customers AS (
SELECT c.id FROM customers c JOIN apac_region r ON c.region_id = r.id
)
SELECT o.*
FROM orders o
JOIN apac_customers ac ON o.customer_id = ac.id;
Modern optimizers (PostgreSQL, SQL Server) reorder joins automatically using their cost model. But the optimizer can only reorder joins it recognises as equivalent — complex queries with many joins, subqueries, or CTEs can confuse the planner. In those cases, manually structuring the query to filter early and join small tables first is effective.
You can force or hint join order in some databases (PostgreSQL's join_collapse_limit, SQL Server's FORCE ORDER hint), but this should be a last resort after verifying the planner's automatic choice is suboptimal.