Query Performance Optimization

Why Performance Matters

A query that runs in 50 milliseconds on a 10,000-row table might take 5 minutes on a 10-million-row table if it's not written efficiently. At scale:

• A 500ms page load becomes a 50-second timeout
• A 1-second report becomes a 15-minute job
• A nightly batch that runs in 2 hours might run in 10 minutes with proper indexes

Most performance problems have simple causes: missing indexes, poorly written queries, or stale statistics. This page teaches you to diagnose and fix the most common issues.

Reading Execution Plans

Before you can optimize a query, you need to understand what the database is actually doing. The execution plan (or explain plan) shows you exactly how the database intends to run your query: which indexes it uses, which joins it performs, how many rows it estimates, and the relative cost of each step.

PostgreSQL: EXPLAIN ANALYZE

-- EXPLAIN shows the plan without running the query
-- EXPLAIN ANALYZE runs it and shows actual times + row counts
EXPLAIN ANALYZE
SELECT e.first_name, e.last_name, d.department_name
FROM   employees e
JOIN   departments d ON e.department_id = d.department_id
WHERE  e.salary > 10000;

-- Sample output:
-- Hash Join  (cost=4.28..12.15 rows=10 width=44) (actual time=0.085..0.132 rows=11 loops=1)
--   Hash Cond: (e.department_id = d.department_id)
--   ->  Seq Scan on employees  (cost=0.00..6.07 rows=10 width=30) (actual time=0.012..0.045 rows=11 loops=1)
--         Filter: (salary > 10000)
--         Rows Removed by Filter: 96
--   ->  Hash  (cost=3.40..3.40 rows=70 width=22) (actual time=0.032..0.033 rows=27 loops=1)
--         ->  Seq Scan on departments  (cost=0.00..3.40 rows=70 width=22) ...
-- Planning Time: 0.5 ms
-- Execution Time: 0.2 ms

Oracle: EXPLAIN PLAN + DBMS_XPLAN

-- Oracle: generate the execution plan (does NOT run the query)
EXPLAIN PLAN FOR
SELECT e.first_name, e.last_name, d.department_name
FROM   employees e
JOIN   departments d ON e.department_id = d.department_id
WHERE  e.salary > 10000;

-- View the plan in formatted output:
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- Sample output:
-- Plan hash value: 1234567890
-- -----------------------------------------------------------------------
-- | Id | Operation                     | Name               | Rows | Cost |
-- -----------------------------------------------------------------------
-- |  0 | SELECT STATEMENT              |                    |   11 |    5 |
-- |  1 |  HASH JOIN                    |                    |   11 |    5 |
-- |  2 |   TABLE ACCESS FULL           | DEPARTMENTS        |   27 |    2 |
-- |* 3 |   TABLE ACCESS BY INDEX ROWID | EMPLOYEES          |   11 |    3 |
-- |* 4 |    INDEX RANGE SCAN           | IDX_EMP_SALARY     |   11 |    1 |
-- -----------------------------------------------------------------------
-- Predicate Information:
-- 3 - filter("E"."SALARY">10000)
-- 4 - access("E"."SALARY">10000)

Execution Plan Glossary

Key Numbers to Watch

Seq Scan on employees (cost=0.00..6.07 rows=107 width=30)
                      ^^^^^^^^^^ ^^^^^^ ^^^^^^^^^^
                      startup    total  estimated  
                      cost       cost   rows

• Cost: relative units (not milliseconds). Higher = more expensive.
• Rows: how many rows the optimizer estimates. If this is wildly wrong, you need to run ANALYZE/GATHER_STATS.
• Actual rows (ANALYZE only): compare to estimated rows. Big differences indicate stale statistics.

The Top 10 Query Optimization Rules

Rule 1: Avoid SELECT *

**SELECT *** fetches every column, even ones you don't need. This wastes I/O, memory, and network bandwidth. It also prevents covering index usage.

-- BEFORE (bad): fetches 20+ columns when you only need 3
SELECT *
FROM   employees
WHERE  department_id = 60;

-- AFTER (good): fetch only what you need
SELECT employee_id, first_name, last_name
FROM   employees
WHERE  department_id = 60;

Benefits of naming columns:

• Less data transferred from database to application
• Prevents covering index queries from needing table access
• Makes code self-documenting (you see exactly what data is used)
• Prevents bugs when table structure changes

Rule 2: Filter Early with WHERE

Push filtering as early as possible in the query — the fewer rows you carry through joins and aggregations, the faster everything runs.

-- BEFORE (bad): joins all rows, then filters
SELECT e.first_name, d.department_name, e.salary
FROM   employees e
JOIN   departments d ON e.department_id = d.department_id
WHERE  e.salary > 50000;          -- filters AFTER the join

-- AFTER (better): conceptually equivalent, but the optimizer can filter before joining
-- In practice, the optimizer usually handles this, but explicit filtering helps readability
-- For subqueries, early filtering definitely helps:
SELECT e.first_name, d.department_name, e.salary
FROM   (SELECT * FROM employees WHERE salary > 50000) e   -- filter first
JOIN   departments d ON e.department_id = d.department_id;

Rule 3: Use EXISTS Instead of COUNT for Existence Checks

When you only need to know whether a row exists (not how many), EXISTS is faster than COUNT. EXISTS stops as soon as it finds the first match; COUNT must scan all matching rows.

-- BEFORE (bad): counts ALL matching rows even though you only need to know "any?"
SELECT first_name, last_name
FROM   employees e
WHERE  (SELECT COUNT(*) FROM orders WHERE employee_id = e.employee_id) > 0;

-- AFTER (good): stops at the first match
SELECT first_name, last_name
FROM   employees e
WHERE  EXISTS (
    SELECT 1                           -- the 1 is a convention; value doesn't matter
    FROM   orders
    WHERE  employee_id = e.employee_id
);

-- Same applies to application code:
-- BAD:  if (SELECT COUNT(*) ...) > 0   → use EXISTS
-- GOOD: if EXISTS (SELECT 1 ...)

Rule 4: Avoid Functions on Indexed Columns in WHERE

Wrapping an indexed column in a function breaks the index. The database can't use a sorted index when the values are being transformed.

-- BEFORE (bad): UPPER() on last_name breaks the index on last_name
SELECT * FROM employees
WHERE UPPER(last_name) = 'KING';

-- AFTER option 1: store data consistently (all same case), query without function
SELECT * FROM employees
WHERE last_name = 'King';   -- assumes data is stored in title case

-- AFTER option 2: create a function-based index to match the query pattern
CREATE INDEX idx_emp_last_upper ON employees (UPPER(last_name));
SELECT * FROM employees WHERE UPPER(last_name) = 'KING';   -- now uses the index

-- More examples of index-breaking functions:
-- BAD:  WHERE TRUNC(hire_date) = DATE '2024-01-01'
-- GOOD: WHERE hire_date >= DATE '2024-01-01' AND hire_date < DATE '2024-01-02'

-- BAD:  WHERE TO_CHAR(hire_date, 'YYYY') = '2024'
-- GOOD: WHERE hire_date >= DATE '2024-01-01' AND hire_date < DATE '2025-01-01'

-- BAD:  WHERE salary + 1000 > 60000
-- GOOD: WHERE salary > 59000

Rule 5: Avoid Leading Wildcards in LIKE

A LIKE pattern starting with % cannot use a B-Tree index — the database doesn't know where in the sorted index to start looking.

-- BEFORE (bad): leading % forces full table scan
SELECT * FROM employees WHERE email LIKE '%@company.com';
SELECT * FROM employees WHERE last_name LIKE '%son';

-- AFTER: trailing wildcards can use an index (searches from the start)
SELECT * FROM employees WHERE last_name LIKE 'Jo%';    -- uses index on last_name ✓
SELECT * FROM employees WHERE email LIKE 'jsmith%';    -- uses index on email ✓

-- For genuine "contains" searches, use a full-text index:
-- Oracle: CONTAINS() with Text index
-- PostgreSQL: tsvector / GIN index with @@ operator

Rule 6: Use LIMIT / FETCH FIRST When You Only Need Top N

If you only need the top 10 results, tell the database. There's no reason to compute and transfer all 10 million rows.

-- BEFORE (bad): fetches all rows, application discards most
SELECT first_name, last_name, salary
FROM   employees
ORDER BY salary DESC;
-- Returns 107 rows; application uses 5

-- AFTER (good): fetch only what you need
-- Oracle (12c+):
SELECT first_name, last_name, salary
FROM   employees
ORDER BY salary DESC
FETCH FIRST 5 ROWS ONLY;

-- PostgreSQL / MySQL:
SELECT first_name, last_name, salary
FROM   employees
ORDER BY salary DESC
LIMIT 5;

-- Oracle (older):
SELECT * FROM (
    SELECT first_name, last_name, salary
    FROM   employees
    ORDER BY salary DESC
)
WHERE ROWNUM <= 5;

Rule 7: Rewrite Correlated Subqueries as JOINs or CTEs

A correlated subquery in SELECT re-executes for every row in the outer query — this is called an N+1 problem and is extremely slow on large tables.

-- BEFORE (bad): correlated subquery runs 107 times (once per employee)
SELECT e.first_name, e.last_name,
       (SELECT d.department_name              -- runs for EACH of the 107 employees!
        FROM   departments d
        WHERE  d.department_id = e.department_id) AS dept_name
FROM   employees e;

-- AFTER (good): single JOIN — much faster
SELECT e.first_name, e.last_name, d.department_name
FROM   employees e
JOIN   departments d ON e.department_id = d.department_id;

-- Another example: correlated subquery for aggregation
-- BEFORE (bad): AVG computed for every row
SELECT e.first_name, e.salary,
       (SELECT AVG(salary) FROM employees e2 WHERE e2.department_id = e.department_id) AS dept_avg
FROM   employees e;

-- AFTER (good): window function computes once per partition
SELECT first_name, salary,
       AVG(salary) OVER (PARTITION BY department_id) AS dept_avg
FROM   employees;

Rule 8: Use Covering Indexes

A covering index contains all the columns your query needs — so the database never has to access the table at all. This eliminates the most expensive part of an index scan: the random I/O of following row pointers.

-- Query: find employees by department, return name and salary
SELECT first_name, last_name, salary
FROM   employees
WHERE  department_id = 60;

-- Regular index on department_id:
-- Step 1: scan index to find rows in dept 60 → gives row pointers
-- Step 2: follow each pointer to the table to get first_name, last_name, salary

-- Covering index (includes all columns from SELECT + WHERE):
CREATE INDEX idx_emp_dept_covering
ON employees (department_id, first_name, last_name, salary);
-- Step 1: scan index to find rows in dept 60 → already has the data!
-- Step 2: NONE — no table access needed!

Rule 9: Use UNION Instead of OR on Different Columns

OR on different columns prevents efficient index usage. UNION allows each branch to use its own index.

-- BEFORE (bad): OR across different columns — may not use either index
SELECT * FROM employees
WHERE  last_name = 'King'
OR     employee_id = 100;

-- AFTER (good): UNION lets each branch use its own index
SELECT * FROM employees WHERE last_name = 'King'
UNION
SELECT * FROM employees WHERE employee_id = 100;
-- Index on last_name used for branch 1
-- Primary key index used for branch 2

-- Note: use UNION ALL if you're sure there's no overlap (faster, no dedup step)
SELECT * FROM employees WHERE last_name = 'King'
UNION ALL
SELECT * FROM employees WHERE employee_id = 100 AND last_name != 'King';

Rule 10: Filter Before JOINing (Reduce Rows Early)

Join smaller result sets, not entire tables. Use subqueries or CTEs to pre-filter before joining.

-- BEFORE (bad): join all employees to all departments, then filter
SELECT e.first_name, e.last_name, d.department_name
FROM   employees e
JOIN   departments d ON e.department_id = d.department_id
WHERE  e.salary > 15000
AND    d.location_id = 1700;

-- AFTER (better): filter each table first, then join (optimizer usually does this,
-- but explicit filtering in subqueries guarantees it for complex cases)
SELECT e.first_name, e.last_name, d.department_name
FROM   (SELECT employee_id, first_name, last_name, department_id
        FROM   employees
        WHERE  salary > 15000) e
JOIN   (SELECT department_id, department_name
        FROM   departments
        WHERE  location_id = 1700) d
    ON e.department_id = d.department_id;

Pagination: The OFFSET Problem

A common pattern for paginating results is OFFSET n LIMIT m. It works but has a serious performance problem at large pages: the database must compute and discard all OFFSET rows.

-- OFFSET pagination — works, but slow for large page numbers
-- Page 1: fast
SELECT employee_id, last_name, salary
FROM   employees
ORDER BY salary DESC
OFFSET 0 LIMIT 20;

-- Page 500: must compute and discard 10,000 rows first!
SELECT employee_id, last_name, salary
FROM   employees
ORDER BY salary DESC
OFFSET 10000 LIMIT 20;   -- reads 10,020 rows, returns 20

Keyset Pagination (Cursor-Based) — The Solution

Instead of skipping rows by position, remember the last value you saw and filter from there:

-- Page 1: no filter needed
SELECT employee_id, last_name, salary
FROM   employees
ORDER BY salary DESC, employee_id DESC   -- tie-break by employee_id for stability
FETCH FIRST 20 ROWS ONLY;
-- Remember: last row had salary = 7200, employee_id = 151

-- Page 2: filter using the last seen values
SELECT employee_id, last_name, salary
FROM   employees
WHERE  (salary, employee_id) < (7200, 151)   -- start from after last seen row
-- PostgreSQL / Oracle row-value comparison
ORDER BY salary DESC, employee_id DESC
FETCH FIRST 20 ROWS ONLY;

-- This is O(1) regardless of which page you're on — always fast!

The N+1 Query Problem

The N+1 problem occurs when code runs 1 query to get a list of N items, then runs N more queries to get related data for each item. This is catastrophic at scale.

-- Application code that causes N+1:
-- Query 1: get all departments (1 query)
-- SELECT department_id, department_name FROM departments;
-- Returns 27 departments

-- Queries 2-28: for each department, get its employees (27 queries!)
-- SELECT * FROM employees WHERE department_id = 10;
-- SELECT * FROM employees WHERE department_id = 20;
-- ... 25 more queries

-- SOLUTION: use a JOIN to get everything in 1 query
SELECT d.department_name, e.first_name, e.last_name, e.salary
FROM   departments d
LEFT JOIN employees e ON d.department_id = e.department_id
ORDER BY d.department_name, e.last_name;
-- 1 query instead of 28!

Temp Tables and CTEs for Complex Queries

When a single query becomes extremely complex, break it into steps using temp tables or CTEs. This improves readability and can improve performance by materializing intermediate results.

-- Complex report: top 3 earners per department with dept stats
-- Single query approach: deeply nested, hard to debug
SELECT ...
FROM (
    SELECT ...,
           RANK() OVER (...) AS rnk,
           AVG(...) OVER (...) AS dept_avg
    FROM (
        SELECT ...
        FROM employees e
        JOIN departments d ON ...
        WHERE ...
    )
)
WHERE rnk <= 3;

-- CTE approach: readable, debuggable step-by-step
WITH
base_data AS (
    SELECT e.employee_id, e.first_name, e.last_name,
           e.salary, e.department_id, d.department_name
    FROM   employees e
    JOIN   departments d ON e.department_id = d.department_id
),
ranked AS (
    SELECT *,
           RANK()       OVER (PARTITION BY department_id ORDER BY salary DESC) AS rnk,
           AVG(salary)  OVER (PARTITION BY department_id) AS dept_avg,
           COUNT(*)     OVER (PARTITION BY department_id) AS dept_headcount
    FROM   base_data
)
SELECT department_name, first_name, last_name, salary, dept_avg, dept_headcount
FROM   ranked
WHERE  rnk <= 3
ORDER BY department_name, rnk;

-- Temp table approach (Oracle): for expensive intermediate results
CREATE GLOBAL TEMPORARY TABLE tmp_ranked ON COMMIT DELETE ROWS AS
SELECT * FROM ranked;   -- (Oracle doesn't support CREATE TEMP TABLE AS SELECT directly in all versions)

Statistics and the Query Planner

The query optimizer makes decisions based on statistics: the number of rows in each table, the distribution of values in each column, and the number of distinct values. If statistics are stale (outdated), the optimizer makes bad decisions.

-- Oracle: gather statistics for a table
EXEC DBMS_STATS.GATHER_TABLE_STATS('HR', 'EMPLOYEES');

-- Oracle: gather statistics for an entire schema
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('HR');

-- Oracle: check when statistics were last gathered
SELECT table_name, last_analyzed, num_rows
FROM   user_tables
WHERE  table_name = 'EMPLOYEES';

-- PostgreSQL: update statistics
ANALYZE employees;
ANALYZE;   -- entire database

-- PostgreSQL: view table statistics
SELECT relname, n_live_tup, n_dead_tup, last_analyze
FROM   pg_stat_user_tables
WHERE  relname = 'employees';

When to gather statistics:

• After a bulk INSERT or MERGE that significantly changes row count
• After a TRUNCATE and reload
• When queries suddenly slow down without obvious cause
• On a regular schedule for OLTP tables (nightly or weekly)

Query Optimization Checklist

Use this checklist when a query is running slowly:

Before/After Summary

-- ❌ Slow query: all 10 anti-patterns at once
SELECT *                                                      -- Rule 1: SELECT *
FROM employees e, departments d                               -- old-style implicit join
WHERE e.department_id = d.department_id
AND   UPPER(e.last_name) LIKE '%SON'                         -- Rule 4+5: func + leading %
AND   (SELECT COUNT(*) FROM orders o                         -- Rule 3: COUNT for existence
       WHERE o.employee_id = e.employee_id) > 0              -- Rule 7: correlated subquery
ORDER BY e.salary
OFFSET 10000 ROWS FETCH NEXT 20 ROWS ONLY;                   -- Rule (pagination)

-- ✅ Optimized version
SELECT e.employee_id, e.first_name, e.last_name,             -- Rule 1: specific columns
       e.salary, d.department_name
FROM   employees e
JOIN   departments d ON e.department_id = d.department_id    -- explicit JOIN
WHERE  e.last_name LIKE 'Jon%'                               -- Rule 5: trailing wildcard
-- (if must do suffix search, use full-text index, not LIKE '%son')
AND    EXISTS (                                               -- Rule 3: EXISTS
    SELECT 1 FROM orders WHERE employee_id = e.employee_id   -- Rule 7: not correlated agg
)
AND    (e.salary, e.employee_id) < (prev_salary, prev_id)    -- keyset pagination
ORDER BY e.salary DESC, e.employee_id DESC
FETCH FIRST 20 ROWS ONLY;

Common Errors

Interview Corner

-- Each of these is a different SQL text → 3 hard parses
SELECT * FROM employees WHERE employee_id = 100;
SELECT * FROM employees WHERE employee_id = 101;
SELECT * FROM employees WHERE employee_id = 102;

-- One hard parse, then soft parses for every subsequent execution
SELECT * FROM employees WHERE employee_id = :emp_id;

SELECT s.sid, s.serial#, s.username, s.status,
       q.sql_text, q.elapsed_time/1e6 AS elapsed_sec,
       q.disk_reads, q.buffer_gets
FROM   v$session s
JOIN   v$sql     q ON s.sql_id = q.sql_id
WHERE  s.status = 'ACTIVE'
ORDER BY q.elapsed_time DESC;

SELECT sql_id, ROUND(elapsed_time/1e6) AS elapsed_sec,
       executions, ROUND(elapsed_time/NULLIF(executions,0)/1e6,2) AS avg_sec,
       disk_reads, buffer_gets,
       SUBSTR(sql_text,1,80) AS sql_preview
FROM   v$sql
WHERE  executions > 0
ORDER BY elapsed_time DESC
FETCH FIRST 10 ROWS ONLY;

Related Topics

• Indexes — the primary tool for reducing physical I/O in slow queries
• Explain Plan — read and interpret execution plans step by step
• Partitioning — partition pruning to limit scans to relevant data segments
• Window Functions — window functions can be expensive; understand their plan operations
• Transactions & Locks — lock contention is a common performance bottleneck