SQL Performance Optimization: The Complete Guide

Performance optimisation is the practice of making SQL queries run faster and use less resource. It covers indexing, query rewriting, execution plan analysis, partitioning, and any other technique that reduces the time or cost of a query.

The reason it matters is scale. On a table with a few hundred rows, any query is fast regardless of how it is written. On a table with hundreds of millions of rows, a poorly-written query takes minutes, and the same query with the right index takes milliseconds. The gap between optimised and unoptimised grows with the data volume.

-- Inefficient: reads every column and every row
SELECT * FROM employees;

-- Efficient: reads only what the query actually needs
SELECT employee_id, name, department FROM employees;

That example is a small one, but it is the shape of every improvement. Read less, do less, and the query gets faster. The four techniques that drive most of the wins are indexing, query rewriting, schema design, and statistics maintenance.

An index is a data structure that lets the database find rows without scanning the whole table. When a query filters, joins, or sorts on an indexed column, the database reads the index instead of reading every row.

-- Without an index: full table scan
SELECT customer_name FROM customers WHERE customer_id = 'C001';

-- Create an index on the column
CREATE INDEX idx_customers_id ON customers (customer_id);

-- Now the query finds the row via the index

On our small table, the difference is invisible. On a table with a million rows, the scan reads every row and the index lookup reads the ones that match. The gap widens with the table size, which is why indexes matter most on large data.

Indexes matter most for WHERE clauses, join conditions, and ORDER BY / GROUP BY clauses. They matter least for low-cardinality columns where the filter does not narrow the result much.

The cost of indexing is on the write side. Every INSERT, UPDATE, or DELETE has to maintain every index on the table. On a write-heavy table, indexes are pure overhead unless the reads justify them. On a read-heavy table, indexes are usually the single biggest performance lever.

SELECT * reads every column, whether you need them or not. That costs extra I/O, extra network transfer, and extra memory — and it prevents the database from using a covering index.

-- Inefficient
SELECT * FROM orders WHERE order_id = 101;

-- Efficient
SELECT order_date, total_amount FROM orders WHERE order_id = 101;

On a table with a few columns, the difference is small. On a table with fifty columns, or a wide text column, or a BLOB, the difference is enormous — you are reading megabytes of data you never use.

The other problem is stability. A query that lists columns explicitly keeps working when someone adds a new column to the table. A query that used SELECT * starts returning a different shape of data, which can break downstream code that expected a fixed set of columns. I have seen this take down a nightly pipeline.

The rule: list the columns. It is thirty seconds of typing and it pays off every time the schema changes.

An execution plan shows how the database plans to run a query — which tables it will scan, which indexes it will use, in what order it will join them, and how it will filter and sort. EXPLAIN shows you the plan.

-- PostgreSQL
EXPLAIN ANALYZE
SELECT c.customer_name, o.order_amount
FROM   customers c
JOIN   orders    o ON c.customer_id = o.customer_id
WHERE  c.region = 'North';

The output reveals which indexes were used, how many rows each step processed, and how long each step took. It is the fastest way to find why a query is slow.

What to look for:

• Seq Scan / Full Table Scan on a large table — an index is probably missing.
• Row estimate vs actual mismatch — statistics are stale.
• Nested Loop with high outer rows — the inner side runs too many times.
• Sort on a large result — an index could eliminate it.

Reading plans is the skill that separates SQL developers who guess from those who diagnose. Once you know how to read the plan, every slow query is a series of "why is this step expensive" questions with answers in the plan itself.

WHERE filters rows before grouping. HAVING filters groups after grouping. Both can filter, but they run at different stages of the query, and that difference has a performance cost.

-- WHERE runs first, filters rows before grouping
SELECT region, COUNT(*) AS customers
FROM   customers
WHERE  credit_limit IS NOT NULL
GROUP BY region;

-- HAVING runs after grouping, filters the groups
SELECT region, COUNT(*) AS customers
FROM   customers
GROUP BY region
HAVING COUNT(*) > 1;

WHERE is generally faster because it reduces the number of rows the GROUP BY has to process. On a table with a million rows, if WHERE cuts that to ten thousand, the GROUP BY works on ten thousand rows instead of a million.

The rule: any condition that does not depend on an aggregate belongs in WHERE. Only conditions referencing SUM, COUNT, AVG, MIN, MAX, or another aggregate go in HAVING.

-- Wrong: this is a row-level filter, put it in WHERE
SELECT region, COUNT(*) FROM customers
GROUP BY region
HAVING region IS NOT NULL;

-- Right
SELECT region, COUNT(*) FROM customers
WHERE region IS NOT NULL
GROUP BY region;

Both produce the same output. The second is faster because the filter runs at the row level, before the aggregation.

Denormalisation stores redundant data so queries do not have to join as many tables. It trades write complexity and storage for read speed.

-- Normalised: multiple tables, multiple joins
SELECT employees.name, departments.name
FROM   employees
JOIN   departments ON employees.department_id = departments.department_id;

-- Denormalised: one table, no joins
SELECT employee_name, department_name FROM employee_data;

Same output. The denormalised version reads one table and produces the answer directly. On a data warehouse with hundreds of millions of rows, avoiding a join can turn a ten-second query into a two-hundred-millisecond one.

The cost is on the write side and in consistency. The same data lives in multiple places, which means updates have to be applied everywhere. If the copies go out of sync, reports show the wrong data.

Where denormalisation is used: data warehouses (star schemas and fact tables are denormalised by design), reporting databases, materialised views, and cache tables refreshed on a schedule.

The general advice: keep the transactional tables normalised and build a denormalised copy for reporting. That way the source of truth stays clean and reports stay fast.

A query hint is an instruction that overrides the optimiser's choice — use this index, use that join algorithm, disable parallelism. Hints give you control when the optimiser's default is wrong.

-- Oracle: force an index
SELECT /*+ INDEX(customers idx_customers_region) */
       customer_name
FROM   customers
WHERE  region = 'North';

-- SQL Server: force an index
SELECT customer_name
FROM   customers WITH (INDEX(idx_customers_region))
WHERE  region = 'North';

Hints are a last resort. The optimiser is usually right, and a hint that helps today can hurt in three months when the data distribution changes. Use them only when you have tested both plans and confirmed the hinted one is faster.

Before reaching for a hint, check three things. Are statistics fresh? Are the right indexes in place? Is the query written as cleanly as possible? If all three are yes and the plan is still wrong, then a hint is reasonable.

PostgreSQL deliberately does not support hints. The design philosophy is that the optimiser should be trusted, and bad plans should be fixed by improving statistics or rewriting the query, not by locking in a decision.

When you do use a hint, document why. Leave a comment explaining what problem it solved and when it should be revisited. A hint without a note is a future bug.

Partitioning splits a large table into smaller physical pieces called partitions, usually by date. When a query filters on the partition column, the database reads only the relevant partitions instead of the whole table. That is partition pruning.

CREATE TABLE orders_partitioned (
    order_id     INT,
    order_date   DATE,
    total_amount DECIMAL(12,2)
)
PARTITION BY RANGE (order_date) (
    PARTITION p_2023 VALUES LESS THAN ('2024-01-01'),
    PARTITION p_2024 VALUES LESS THAN ('2025-01-01'),
    PARTITION p_2025 VALUES LESS THAN ('2026-01-01')
);

A query for WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31' reads only p_2024. The other partitions are not touched. On a table with three years of data, that is one-third of the I/O.

Partitioning also helps with maintenance. Dropping a year of old data becomes DROP PARTITION p_2023 — instant instead of a slow DELETE that logs every row.

When not to partition. Small tables — the overhead is not worth it. Tables where queries never filter on the partition column — partitioning does nothing. Tables with constantly-changing partition keys — moving rows between partitions is expensive.

Common strategies: range partitioning (by date), list partitioning (by discrete values like region), and hash partitioning (for even distribution when no natural range applies). The choice depends on how queries filter the data.

A correlated subquery references a column from the outer query, so it cannot run standalone. The database evaluates it once per outer row. On a table with a million rows, that is a million subquery executions.

-- Slow: correlated subquery, one execution per customer
SELECT name FROM employees
WHERE  salary > (SELECT AVG(salary) FROM employees WHERE department_id = employees.department_id);

Each execution may be fast on its own. But a million fast executions are still a million times something. The total time adds up.

-- Faster: join against a grouped subquery, one execution total
WITH dept_avg AS (
    SELECT department_id, AVG(salary) AS avg_sal
    FROM   employees
    GROUP BY department_id
)
SELECT e.name
FROM   employees e
JOIN   dept_avg d ON e.department_id = d.department_id
WHERE  e.salary > d.avg_sal;

Same output. But now the aggregation runs once for the whole table, and the join picks up the result. On large tables, the difference can be minutes versus seconds.

Two patterns are worth distinguishing:

• Correlated subquery — references outer columns. Runs once per row. Usually slow.
• Scalar subquery — no outer references. Runs once and produces one value. Usually fine.

-- Scalar subquery — runs once, fine
SELECT customer_name, credit_limit
FROM   customers
WHERE  credit_limit > (SELECT AVG(credit_limit) FROM customers);

The question to ask yourself: does the subquery reference any column from the outer query? If yes, look for a way to rewrite it as a join. If no, it is fine as is.

One exception: EXISTS is technically correlated but efficient, because it short-circuits as soon as it finds one matching row. EXISTS is often faster than an equivalent IN on large tables.

Narrower data types take less storage and compare faster. On small tables the difference is invisible. On tables with hundreds of millions of rows, it compounds into gigabytes of storage and millions of extra page reads.

-- Wide: VARCHAR for an ID that is always numeric
CREATE TABLE users (user_id VARCHAR(10), name VARCHAR(100));

-- Narrow: INT for the same ID
CREATE TABLE users (user_id INT, name VARCHAR(100));

An INT takes four bytes. A VARCHAR(10) holding the same digits takes at least ten bytes plus a length prefix. On a billion rows, that difference is six gigabytes of storage, and every index built on the column is correspondingly larger.

Smaller indexes fit in memory better, which means faster lookups. Narrower columns also compare faster — integer comparisons are quicker than string comparisons, especially for sorting and joining.

Type recommendations:

• Numeric IDs: INT or BIGINT. Not VARCHAR.
• Money: DECIMAL or NUMERIC. Not FLOAT — floating-point rounding errors are a real problem.
• Short text: VARCHAR with a sensible length. Not VARCHAR(255) as a default.
• Dates and times: DATE or TIMESTAMP. Not VARCHAR formatted as a date.
• Booleans: TINYINT or BOOLEAN depending on the database.

One specific trap: VARCHAR(MAX) or equivalent unlimited-size types. They force the database to store the column out-of-row, which means an extra read whenever you fetch it. Set a real limit and enforce it.

You cannot optimise what you have not measured. Most databases track which queries run and how long they take. Find the offenders, then focus your attention on them.

-- PostgreSQL: top queries by total time
SELECT query, calls, total_time, mean_time
FROM   pg_stat_statements
ORDER BY total_time DESC
LIMIT  10;

-- MySQL: enable slow query log
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;

-- Oracle: check V$SQL
SELECT sql_text, elapsed_time, executions
FROM   v$sql
ORDER BY elapsed_time DESC;

Two metrics matter. First, the mean time per execution — how slow is each run. Second, the total time — how much time across all runs. A query that runs a thousand times per hour at 100 milliseconds each costs more aggregate time than one that runs once a day at one second.

Focus on the queries with the highest total time. Fixing one that runs a thousand times per hour and takes 100 milliseconds is worth far more than fixing one that runs once a day and takes two seconds.

Some databases also track plan changes over time. When a query's plan suddenly shifts and the runtime goes up, that is usually a statistics problem or an environmental change like a new index elsewhere on the table.

Four common causes cover most cases. Data volume grows, statistics go stale, indexes fragment, and the plan changes for reasons that are not obvious from the query text.

Data volume. A query that was fast on ten thousand rows becomes slow on a million. If the query relied on a full scan of a small table, growth breaks that assumption. Adding an index usually fixes it.

Stale statistics. The optimiser's estimates go wrong after a bulk load or a large data change. It picks a plan that made sense for the old data and is wrong for the new. Refreshing statistics often fixes it.

-- Refresh statistics
ANALYZE orders;   -- PostgreSQL
UPDATE STATISTICS orders;   -- SQL Server

Index fragmentation. As rows are inserted and updated, index pages fragment. Reads become slower because the database visits more pages than necessary. Rebuilding the index fixes it.

-- Rebuild fragmented indexes
ALTER INDEX idx_orders_customer_id REBUILD;

A new index changed the plan. Adding an index elsewhere on the table can change the optimiser's choices for unrelated queries. Usually the new plan is better, but occasionally it is worse.

The diagnostic: run EXPLAIN and compare the current plan against what you saw when the query was fast. If it changed, one of the four above is usually the reason. Check statistics first, then index health, then data volume.

Multi-table queries live and die by indexing and join order. Four habits cover most of what matters.

Index every join column. Every foreign key column on the "many" side of a one-to-many relationship. A join without an index on the join column scans the whole table for every row of the outer query.

CREATE INDEX idx_orders_customer_id ON orders (customer_id);

Filter early. A tight WHERE clause reduces the row count before any joins. On a table with a million rows, filtering to a thousand before joining is the difference between a plan that works and one that times out.

-- Better: filter before the join
SELECT c.customer_name, o.order_id
FROM   customers c
JOIN   orders    o ON c.customer_id = o.customer_id
WHERE  c.region = 'North'
  AND  o.order_status = 'Delivered';

Select only the columns you need. Listing columns explicitly lets the database use narrower indexes and reduces the amount of data moving through the pipeline.

Reduce the number of tables. If a report only needs three tables, do not join six. Every additional join multiplies the potential row count and complicates the plan.

The diagnostic tool is EXPLAIN. Look at the row count at each step of the plan. If a step that should produce a hundred rows produces a million, the filter is in the wrong place or the join condition is wrong.

A full table scan reads every row in the table. An index scan uses the index to find specific rows. Usually the index scan is faster, but not always.

-- Full scan: reads every row
Seq Scan on customers

-- Index scan: reads matching rows via the index
Index Scan using idx_customers_region on customers

A full scan wins when the query returns a large fraction of the table. If you are reading 90% of the rows, jumping between the index and the table is slower than just reading the table straight through. The optimiser knows this and often chooses a scan for low-selectivity filters.

A full scan also wins when the index does not exist. On a table with no index on the filter column, the only option is scanning.

And a full scan wins on tiny tables. If the table fits on a single page, the overhead of looking up the index is more than the cost of reading the page.

Where a scan loses: on a large table with a selective filter. Reading a million rows to find ten is expensive; using an index to find those ten directly is cheap.

The optimiser decides based on cost estimates. If a scan seems wrong for a query that should be selective, the fix is usually an index (if it is missing) or refreshed statistics (if the optimiser's estimate is wrong).

Individual row operations pay a per-statement cost — parsing, planning, logging, committing. Batch operations amortise that cost across many rows. On a million-row load, the difference is dramatic.

-- Slow: one row at a time
INSERT INTO customers (customer_id, customer_name) VALUES ('C008', 'Henry Co');
INSERT INTO customers (customer_id, customer_name) VALUES ('C009', 'Ivan Trading');
-- ... a million more statements

-- Fast: batch the rows
INSERT INTO customers (customer_id, customer_name)
VALUES
    ('C008', 'Henry Co'),
    ('C009', 'Ivan Trading'),
    ('C010', 'Julia Wholesale')
    -- ... hundreds per batch
;

The batch version sends fewer commands to the database, which means less overhead per row. On real workloads, batch inserts regularly run ten to a hundred times faster than equivalent single-row loops.

For very large loads, use the database's bulk loading tool. Every database has one — PostgreSQL has COPY, MySQL has LOAD DATA INFILE, Oracle has SQL*Loader, SQL Server has BULK INSERT. These bypass most of the per-row overhead and can load millions of rows in seconds.

Batch size matters. Too small and the overhead still dominates. Too large and the transaction holds too many locks and might run out of undo space. A few thousand rows per batch is a common starting point.

On very large loads, dropping indexes, loading, and rebuilding the indexes is faster than loading with the indexes in place. The index build reads the sorted data once instead of being updated per row.

ORDER BY on a large result set means the database has to sort. Sorting is expensive — it uses memory, can spill to disk, and scales worse than linear. Three techniques help.

Index the sort column. If the column being sorted has an index, the database reads the rows in sorted order and skips the sort step entirely.

CREATE INDEX idx_orders_amount ON orders (order_amount DESC);

SELECT order_id, order_amount
FROM   orders
ORDER BY order_amount DESC;
-- Uses the index instead of sorting

Reduce the rows before sorting. A tight WHERE clause reduces what has to be sorted. Sorting ten rows is free; sorting ten million is not.

SELECT order_id, order_amount
FROM   orders
WHERE  order_status = 'Delivered'
ORDER BY order_amount DESC;

Limit the result. If the report only needs the top ten, add LIMIT 10. The database can stop after finding ten matching rows in sorted order instead of sorting the entire set.

SELECT order_id, order_amount
FROM   orders
ORDER BY order_amount DESC
LIMIT  10;

The combination that works best: index the sort column, filter to a manageable row count, and add a limit. That turns the query into an index walk with an early stop, which is the fastest shape a sorted query can have.

Eight patterns come up again and again. None of them are exotic, and all of them are easy to avoid once you know them.

• SELECT * in production code.
• Functions on indexed columns — WHERE UPPER(name) = 'ALICE' prevents the use of an index on name.
• Leading wildcards in LIKE — WHERE name LIKE '%smith' cannot use an index.
• Correlated subqueries that could be joins.
• Implicit type conversion — comparing a VARCHAR column to an INT, which forces a conversion and blocks the index.
• NOT IN with subqueries that return NULL — slow and often wrong.
• OR conditions on different columns — usually not index-friendly. Consider UNION ALL instead.
• Ordering by a column that is not indexed on a large result set.

The unifying theme: anything that prevents the database from using an index, or that forces it to do more work than necessary. Every anti-pattern above either makes the plan worse or forces the database to read more data than it needs to.

The fix is almost always the same: rewrite the query so the column is bare, so the comparison uses the right type, so the subquery becomes a join. Once you learn to spot these patterns, your queries get faster without any other change.

Caching skips the query entirely and returns a pre-computed result. It is the fastest optimisation technique available because it does nothing — the answer is already sitting somewhere.

Several kinds of caching help in different situations.

Buffer cache. The database keeps recently-read pages in memory. A query that hits the buffer cache avoids disk entirely. Almost every database has this, and it is why the second run of a query is often faster than the first.

Query result cache. Some databases cache the full result of a query. If the same query runs again with the same inputs and the underlying data has not changed, the cached result is returned. MySQL has the query cache (though it was deprecated), and other databases have their own mechanisms.

Application-level cache. Redis, Memcached, or an in-process cache stores the result of expensive queries. The application checks the cache before hitting the database. This is the most common approach in modern applications.

Materialised views. The database stores the result of a slow query in a physical table and refreshes on a schedule. Reads are fast, but the data can be stale.

-- Application-level cache pseudocode
result = cache.get('top_customers')
if result is None:
    result = db.query('SELECT ... slow query ...')
    cache.set('top_customers', result, ttl=300)
return result

The trade-off in every case is freshness versus speed. The faster the cache, the more likely the data is stale. For dashboards, a few minutes of staleness is usually fine. For transactional data, it is not — the cache has to be invalidated on every write.

Sharding splits a table horizontally — different rows live on different servers. Each server holds a subset of the data and only has to handle queries against its subset.

Where partitioning is a single-server technique, sharding is a multi-server technique. Both split data, but sharding distributes it across physical machines, which means each machine has less data, less work, and can be sized independently.

Common sharding strategies:

• Range sharding. Customers with ids A–F on server 1, G–M on server 2, etc. Simple but can create hot spots if the ranges are uneven.
• Hash sharding. hash(customer_id) % number_of_servers decides where a row lives. Even distribution, but range queries have to hit every shard.
• Directory sharding. A lookup table maps each key to a specific shard. Maximum flexibility, extra lookup cost.

Sharding solves the problem of a table that has outgrown a single server. But it adds real complexity:

• Queries that span shards are slow. A report that needs every customer has to query every shard and combine the results.
• Joins across shards are hard. Two tables on different shards cannot be joined efficiently.
• Transactions across shards need distributed protocols. Two-phase commit, with the complexities that brings.

Most teams reach for sharding only when a single server can no longer handle the load. Before that, indexing, partitioning, denormalisation, and caching usually solve the problem more simply.

An index is a bet — it costs storage and write speed in exchange for read speed. The bet pays off only when the read workload justifies the cost. Four questions help decide.

Is the column filtered or joined frequently? If queries filter on the column often, an index is worth considering. If nobody filters on it, the index does nothing.

Is the column selective? A column with many distinct values filters out more rows per lookup. A column with three values — say a status flag — does not help much. A composite index or a bitmap index might be a better fit for low-cardinality columns.

How often is the table written to? Every index slows every INSERT, UPDATE, and DELETE. On a write-heavy table, adding indexes has a real cost. On a read-heavy table, the read benefit dominates.

Does an existing index already cover the query? A composite index on (region, credit_limit) already covers queries that filter on region alone. Adding a single-column index on region would be redundant.

The test for a new index: create it, run the query with EXPLAIN ANALYZE, and compare timings against the pre-index plan. If the plan changed for the better and the query is measurably faster, keep it. If the plan is the same and the timings are the same, drop it — it is costing you writes for nothing.

Once an index is in production, revisit it periodically. The workload changes, and indexes that were critical six months ago may not be used today. Databases track usage statistics — check them and drop the ones that are dead.

Partition pruning is the optimiser's ability to skip partitions that cannot contain any relevant rows. If a query filters on the partition column with a range, the database reads only the partitions that overlap the range.

CREATE TABLE orders_partitioned (
    order_id     INT,
    order_date   DATE,
    total_amount DECIMAL(12,2)
)
PARTITION BY RANGE (order_date) (
    PARTITION p_2023 VALUES LESS THAN ('2024-01-01'),
    PARTITION p_2024 VALUES LESS THAN ('2025-01-01'),
    PARTITION p_2025 VALUES LESS THAN ('2026-01-01')
);

SELECT * FROM orders_partitioned
WHERE  order_date BETWEEN '2024-01-01' AND '2024-12-31';

That query reads only p_2024. Partitions p_2023 and p_2025 are skipped entirely. On a table with three years of data, that is one-third of the I/O.

You can see pruning in the execution plan. A query against a partitioned table that reads fewer partitions than exist shows a "partitions scanned" count that is lower than the total. When the count equals the total, pruning is not happening — either because the filter is not on the partition column, or because the filter uses a function that prevents the optimiser from reasoning about ranges.

-- Pruning works: filter on the partition column with a literal range
WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01'

-- Pruning does NOT work: filter wraps the partition column in a function
WHERE EXTRACT(YEAR FROM order_date) = 2024

The second form prevents pruning because the optimiser cannot map the function result back to partition ranges. Rewriting the filter to compare the raw column restores pruning — and often turns a slow query into a fast one, with no other change.

A query plan can change without any change to the query itself. The optimiser reacts to changes in data volume, statistics, indexes, and configuration. Usually the new plan is fine, but sometimes it is much worse.

The symptom is a query that used to run fast and now runs slowly. The query text has not changed, the schema has not changed, but the runtime has. Something in the environment changed.

Diagnosing plan changes:

• Run EXPLAIN and compare against the previous plan.
• Check whether the optimiser is using a different index, or scanning a table instead.
• Look at the row estimates — are they wildly off from the actuals?
• Check when statistics were last refreshed.

Preventing plan surprises:

• Keep statistics fresh, especially after bulk loads.
• Rebuild fragmented indexes on a schedule.
• Monitor query performance over time so you notice changes quickly.
• Test schema changes with the full production dataset, not a small sample.

Some databases expose plan history directly. Oracle's AWR reports track plan changes. SQL Server's Query Store does the same. PostgreSQL has extensions like pg_stat_statements that can be joined with plan data. When a plan changes, these tools help you see when and why.

The most useful habit: keep an eye on the slowest queries over time. A query that jumps from position ten to position one in the slow query log is a signal that something changed.

Wrapping an indexed column in a function usually disables the index. The database can no longer use the sorted index to look up values because the index stores the raw column, not the function's output.

-- Index on customer_name exists
CREATE INDEX idx_customers_name ON customers (customer_name);

-- This query cannot use the index
SELECT * FROM customers WHERE UPPER(customer_name) = 'ALICE CORP';

-- This query can
SELECT * FROM customers WHERE customer_name = 'Alice Corp';

On a small table the difference is invisible. On a million-row table, the first query scans everything while the second reads three rows.

Three fixes exist. First, rewrite the query to avoid the function on the column:

-- Instead of UPPER(customer_name) = 'ALICE CORP'
WHERE customer_name = 'Alice Corp'

Second, store the transformed value in a separate column and index that:

ALTER TABLE customers ADD customer_name_upper VARCHAR(100);
CREATE INDEX idx_customers_name_upper ON customers (customer_name_upper);
-- Populate with UPDATE, and maintain on insert/update

Third, use a function-based index — a feature some databases support that indexes the function's output directly:

-- Oracle, PostgreSQL: index the function result
CREATE INDEX idx_customers_name_upper ON customers (UPPER(customer_name));

With that index, the original query can use it. The index stores the uppercased value, so the lookup runs against the indexed expression. The cost is the same as any index — extra storage and slower writes — plus the constraint that the query has to use the exact same expression to hit the index.

The general advice: if you find yourself writing functions on indexed columns often, either rewrite the query or add a function-based index. Leaving the function in place and letting the query scan the table is the worst of both worlds.

Performance tuning on a live system requires discipline. Every change has the potential to help or hurt, and the difference is often invisible until production hits the new path.

The approach I use has four phases.

Phase 1 — observe before changing anything. Run the slow queries, capture their plans, and record the timings. Without a baseline you cannot tell whether your change helped.

-- Record the current plan and timing
EXPLAIN ANALYZE SELECT ...;

Phase 2 — make one change. Add one index, or rewrite one query, or refresh statistics on one table. Not three things at once. Isolating the change lets you attribute the outcome.

Phase 3 — measure the change. Run the same query under the same conditions. Compare the new plan and timing against the baseline. If the query got faster without breaking other queries, the change is a win.

Phase 4 — roll back if needed. If the change made things worse, roll it back. Dropping an index, restoring the old query text, or refreshing statistics again are all reversible.

-- Roll back an index change
DROP INDEX idx_customers_region;

Two practical habits make this safer. First, make changes during low-traffic windows when possible. An index rebuild at 3 AM might take a minute; the same rebuild at 3 PM might slow production for ten minutes.

Second, watch the whole system, not just the query you are optimising. Adding an index makes writes slower for everyone. Refreshing statistics can change plans for other queries. The system as a whole is the unit of measure, not just your target query.

Almost every optimisation technique that speeds up reads slows down writes. The trade-off is fundamental, and knowing it helps decide which techniques to apply where.

Read optimisation costs:

• Indexes slow every write because the index has to be maintained.
• Denormalisation slows writes because redundant data has to be kept in sync.
• Materialised views slow writes to the base tables because the view has to be refreshed.
• Caching slows writes because the cache has to be invalidated.

Write optimisation costs:

• Fewer indexes speeds writes but slows reads.
• Batch operations speed writes but increase the transaction size and lock duration.
• Autocommit speeds writes but removes the ability to roll back.

The right choice depends on the read-write ratio of the workload.

Read-heavy workloads — reports, dashboards, analytics — can afford many indexes, denormalised tables, and materialised views. The write cost is amortised across many reads.

Write-heavy workloads — logging, event capture, high-throughput inserts — should minimise indexes and avoid denormalisation. Every write matters and the read benefit is rare.

Mixed workloads — typical OLTP applications — need to find a balance. Index on the columns that are actually queried, denormalise only the hottest read paths, and monitor the ratio over time as the workload evolves.

There is no universally correct answer. The right amount of indexing on a customer table that is read a thousand times per write is different from the right amount on a log table that is written a thousand times per read. Knowing the shape of your workload is what makes the decision tractable.