SQL Optimization & Tuning

The reasoning

The three things worth reading, in order. First, access method per table: a full scan where you expected a lookup means the predicate could not be used, and that is a query fix. Second, estimated against actual rows: if they disagree by an order of magnitude, the optimizer chose a plan for a table it thinks is a different size, and the fix is statistics rather than SQL. Third, the blocking operators — sorts, hash builds, spills — because those are where memory and time go.

Join order and join strategy follow from those. A nested loop over a million estimated rows is a symptom of a bad estimate, not a bad decision — the planner would have chosen a hash join if it had known.

What not to do is read the total cost number and try to make it smaller. It is a unitless estimate from the same model that produced the bad plan; on the query you are debugging, it is the thing that is wrong.

The reasoning

The word in the plan is the whole answer, and it is worth learning the vocabulary of the engine you use. A seek walks the index to the rows it needs; a scan reads everything and filters. Both can be correct — a scan is the right plan when you are reading most of the table — but a scan on a query that returns twelve rows out of a million is a bug.

The plan below is a real one. The same table, the same column, the same index, two predicates: the half-open range seeks, and the equivalent expression wrapped in a function scans. Nothing about the data or the index changed.

The second thing to check is *which* index. A plan that says it used an index you did not expect usually means the one you intended has the wrong leading column, and the optimizer found something cheaper to abuse.

The reasoning

The signature is a large gap between estimated and actual rows in the plan. That single comparison tells you whether you have a planning problem or an execution problem, and they have completely different fixes.

The usual causes: a table that grew past the point where the sampled statistics were gathered, a skewed column where the average is meaningless, a correlated pair of predicates the optimizer assumes are independent, or a fresh partition with no statistics at all.

The fix is usually to refresh statistics and re-check the plan before touching the SQL. If it recurs, the deeper answer is either more frequent statistics maintenance or removing the optimizer's need to guess — a more selective index, or splitting a query whose two halves have wildly different cardinality.

The reasoning

Plan caching exists because compiling is expensive. The cost is that the cached plan encodes an assumption about how many rows the parameter selects. A customer id with three orders and one with three million get the same plan, and only one of them is right.

The tells are: fast for most inputs and slow for a few, slow after a restart or a statistics refresh but fast before, and a plan whose estimated rows look reasonable for a *different* parameter than the one you are testing.

The remedies differ by engine and all involve giving up something. Recompiling per execution costs compile time. Optimising for a representative value accepts being wrong for the outliers. Splitting the query so the skewed case takes its own path is the most work and the most reliable. Which to choose depends on how skewed the distribution is and how often the bad case runs.

The reasoning

An index stores the column's values in order. A seek works by navigating that order, which requires the predicate to be expressed in terms of the stored value. Apply any function and the engine can no longer reason about ordering, so it must compute the function for every row — a full scan by definition.

The rewrite is always the same shape: move the transformation from the column to the literal. `substr(ordered_at,1,7) = '2026-03'` becomes `ordered_at >= '2026-03-01' AND ordered_at < '2026-04-01'`. Same rows, and now expressible as a range on the stored value.

The same rule covers `WHERE UPPER(email) = ...`, `WHERE amount * 1.2 > 100`, and `WHERE CAST(id AS TEXT) = '42'`. Where the transformation genuinely cannot be moved, most engines let you index the expression itself — a function-based or computed-column index — which is the escape hatch rather than the first resort.

The reasoning

Think about a dictionary. Finding every word starting with "abc" means opening at one place and reading forward. Finding every word *ending* in "abc" means reading the whole dictionary, because the ordering says nothing about endings.

That is also why `LIKE 'abc%'` on a case-sensitive collation is seekable and the same predicate under a case-insensitive comparison may not be — the ordering the index was built with has to match the comparison the predicate asks for.

The fixes for genuine substring search are all structural rather than clever: a trigram index where the engine has one, a full-text index, or storing the reversed string and searching a prefix of that when you only ever need suffixes. If the search is really "contains", a B-tree is the wrong instrument and no rewrite will change that.

The reasoning

The classic case is a numeric parameter compared against a string column, or a narrower type compared against a wider one. Which side gets converted follows the engine's type-precedence rules, and when it is the column side, every row must be converted before it can be compared.

The plan usually shows it — a CONVERT or CAST wrapped around the column in the seek predicate, or simply a scan where you expected a seek. Some engines warn about it explicitly; most just do it.

The fix is to make the types match, ideally by fixing the parameter or the column definition rather than by adding a cast to the query. A cast in the query fixes one call site; the underlying mismatch will be rediscovered by the next person who writes a filter on that column.

The reasoning

A seek navigates one ordering. `a = 1 OR b = 2` asks about two orderings at once, and there is no single index that has both unless you built a composite covering the pair — which does not help either, because a composite index is only seekable on its leading column.

The engine's options are to scan, or to seek both indexes separately and combine the row sets. PostgreSQL will do the latter with a BitmapOr; MySQL has index_merge; several warehouses have neither and simply scan.

The manual rewrite is two branches unioned. It is worth doing only when a plan shows the OR causing a scan on a table large enough to matter — and the second branch needs `AND NOT (first condition)` so a row matching both appears once. Written speculatively it is three times the SQL and a double-counting bug waiting to happen.

The reasoning

Picture the index as a phone book sorted by surname then first name. You can find every Smith, and every Smith called John. You cannot find every John, because the Johns are scattered under every surname.

That gives the design rule: put the column used for equality first, and the range column second. `(customer_id, ordered_at)` serves "this customer, in this date range" perfectly. `(ordered_at, customer_id)` serves "this date range, any customer" and handles the per-customer lookup poorly.

It also means one well-ordered composite index often replaces two single-column ones, which matters because every index is maintained on every write. The plan below shows the seek on the leading column; the same index against a filter on the second column alone falls back to a scan.

The reasoning

A normal index seek finds the matching entries and then fetches each row from the table to get the other columns. That fetch is a random read per row. If the index already carries the columns the SELECT asked for, the engine skips it entirely — plans call this an index-only scan or say the index covers the query.

The economics: for a lookup returning three rows, three extra reads is nothing. For one returning fifty thousand, it is fifty thousand random reads, and covering the query can be the difference between a second and a minute.

The cost is width. Every extra column makes the index bigger, so fewer entries fit per page, so scans of the index itself get slower — and every write maintains all of it. Engines that support an INCLUDE clause let you carry payload columns without adding them to the sort key, which keeps the seek narrow and the coverage wide.

The reasoning

The cost is per write and it compounds: more log volume, more pages dirtied, more page splits, more work for the buffer cache. On a write-heavy table it is entirely possible for index maintenance to dominate.

Start by measuring rather than guessing. Every serious engine tracks index usage — how many seeks and scans each has served since restart. An index with zero reads and millions of writes is a pure cost, and there are usually several.

Then look for redundancy. An index on `(a)` is redundant when `(a, b)` exists, because the composite serves every query the single-column one did. Two indexes differing only in trailing columns are usually one index with an INCLUDE. Consolidating is often a bigger win than dropping.

The one caution: dropping an index is easy to undo and momentarily painful, so verify with usage statistics gathered over a full business cycle. An index used only by the quarterly close will look unused all quarter.

The reasoning

The break-even is about how much of the table the predicate selects. Below a few percent, seek-and-fetch wins; above it, sequential reading wins because it is one large ordered read rather than a million random ones. The exact threshold varies by engine and storage, and the shape of the argument does not.

So an index on a 50/50 boolean is dead weight. An index on `status = 'failed'` where 0.1% of rows have failed is excellent — and the optimizer will use it for the rare value and ignore it for the common one, which is the correct behaviour and often surprises people.

The better tool for exactly that case is a partial or filtered index — one that indexes only the rows matching a predicate. It is small, cheap to maintain, and serves the query you actually run. For the "find the failures" case it is usually the right answer.

The reasoning

Pruning happens before any data is read: the planner compares the predicate against the partition metadata and skips the ones that cannot match. That requires the predicate to be on the partition column, in a form it can evaluate statically.

The usual defeats: a function around the column, the same sargability rule as an index. A filter on a *different* column that happens to correlate with the date — the planner cannot know. Deriving the range from a join or a subquery, so the values are not known until after the scan started. And a non-deterministic expression such as a current-time function that some engines will not fold at plan time.

How to confirm depends on the engine, and every one of them will tell you: the plan reports partitions scanned or bytes scanned. If that number does not fall when you narrow the filter, pruning is not happening — and that measurement is the whole diagnosis.

This one cannot be demonstrated on the embedded engine used to verify the rest of this bank. It is the same reasoning as index sargability applied to files rather than to a B-tree, which is why it belongs next to it.

The reasoning

Partitioning is physical separation: each value gets its own directory or file set, and the planner skips whole partitions. It works when the column has low cardinality and appears in almost every query. A date is the canonical example.

Clustering is ordering within a partition. The engine records min/max statistics per file, and a filter on the clustering column lets it skip files whose range cannot match. It works for high-cardinality columns precisely because it does not create a partition per value.

Partitioning by a high-cardinality column is the classic mistake: a million customers means a million partitions, each holding a handful of rows. Metadata operations become the bottleneck, every query pays a large planning cost, and the files are far too small to read efficiently.

Like the previous question, this is knowledge rather than something the embedded engine can show. The measurement that settles it in production is the same one: bytes or files scanned, before and after.

The reasoning

A columnar file is designed to be read in bulk: the footer holds the schema and statistics, and the reader pays that cost once per file. At a few hundred megabytes it is negligible. At a few kilobytes it is most of the work, and the engine cannot parallelise its way out because the bottleneck is per-file rather than per-byte.

It compounds through the planner too. Listing a partition with ten thousand files is itself slow on object storage, and every one of those files contributes statistics the planner must consider.

Two fixes, and you usually need both. Compact what exists — rewrite groups of small files into target-sized ones, which most table formats provide as an operation. And fix the writer, by batching longer before flushing or by adding a periodic compaction job, or the problem returns tomorrow.

The target size is a trade rather than a constant: large enough to amortise the overhead, small enough to parallelise and to skip. A few hundred megabytes is the usual starting point.

The reasoning

The arithmetic is read count times query cost, against build frequency times build cost. A view is right when reads are rare or the transformation is trivial. A full table is right when the source is small enough that a full rebuild is cheap, and it has the great advantage of being unconditionally correct — every run reproduces the whole table from scratch.

Incremental is right when the source is large and mostly append-only, and it costs you correctness guarantees. You now own a merge key, a watermark, and the question of what happens when a historical row changes after you have already loaded it.

Which is why the honest answer includes the maintenance: an incremental model needs a periodic full refresh to correct drift, and a way to detect late-arriving updates that the watermark missed. Teams that skip both discover months later that the incremental table and a full rebuild disagree.

The reasoning

Columnar storage keeps each column contiguously, so projection is a physical decision: unread columns are never touched. Naming three columns on a two-hundred-column table can be a sixtyfold reduction in bytes read, and on a per-byte pricing model that is the bill.

On a row store the difference is much smaller, because the whole row is on the page anyway — you pay for the width in transfer and parsing rather than in reads. This is why the habit transfers badly: `SELECT *` in a transactional database is mildly wasteful and in a warehouse it is the single largest lever most queries have.

It also breaks things quietly. A view defined with `SELECT *` changes shape when a column is added upstream, and anything consuming it positionally breaks. Naming columns is both the cheaper and the more stable choice.

The reasoning

The blocking property is what makes it disproportionate. A filter passes rows through as it finds them; a sort must see every row before it can emit the first. So a sort in the middle of a pipeline serialises it, and a `LIMIT` above the sort cannot save any work below it.

The everyday sources: `DISTINCT` added defensively to hide a fan-out, which sorts the entire result to remove duplicates that should not have been produced. `UNION` where `UNION ALL` was meant, which sorts to deduplicate. `ORDER BY` inside a subquery or view, which is not guaranteed to survive to the outer query and is pure cost when it does not.

And the spill. Sorts get a memory budget; exceeding it writes runs to disk and merges them, which is often an order of magnitude slower. That is why a query is fine on a sample and falls over on the full dataset — it crossed the spill threshold, and nothing in the SQL changed.

The reasoning

Inlining is usually the right default, because it lets the optimizer push filters into the CTE and combine it with the outer query. It is exactly wrong when the CTE is expensive and referenced several times, because that pushdown benefit is bought four times over at four times the cost.

PostgreSQL materialized every CTE before version 12 and inlines them now unless they are recursive, referenced more than once, or marked MATERIALIZED. Most warehouses inline. The reliable move is to read the plan and count how many times the expensive operator appears.

The fixes, in order of preference: the engine's explicit MATERIALIZED hint where it exists; a temp table, which additionally gives the optimizer real statistics for everything downstream; or restructuring so the CTE is referenced once.

The temp table has a second benefit worth naming. An inlined subquery is estimated; a temp table has been counted. On a step whose real row count differs wildly from the estimate, materializing can improve every plan downstream of it, quite apart from avoiding the recomputation.

The reasoning

A sort is blocking. To know the ten largest, the engine must consider all of them, so the ORDER BY costs the same with or without the LIMIT. What the LIMIT saves is transfer and any work above it.

It becomes cheap in two ways. If an index provides the ordering, the engine walks it and stops after ten — that is the case worth engineering for, and it is why an index on the sort column can turn this query from minutes into milliseconds. Failing that, most engines implement a top-N sort: keep a heap of ten and discard the rest, which is still a full pass but avoids materialising and sorting everything.

The plan tells you which you got. Look for a top-N or bounded sort operator against a full sort, and for whether the sort appears at all. If the aggregate above the sort is what makes the ordering impossible to serve from an index, the honest answer may be that this query is expensive and should be materialised.

The reasoning

**Too much data scanned.** A full table scan where a range was intended, a partition filter that did not prune, or `SELECT *` on a wide columnar table. The signal is bytes scanned being far larger than the result warrants, and it is the most common cause by a distance.

**Bad join strategy.** Two large tables joined before either is filtered, a join on a column that is neither indexed nor a distribution key, or a key so skewed that one node does most of the work. The signal is a join whose output row count is far larger than either input, or a plan that redistributes both sides.

**Expensive operations.** A `DISTINCT` that is not needed, a large `ORDER BY` with no `LIMIT`, a `GROUP BY` on a high-cardinality expression, or a function wrapped around a filtered column — `WHERE DATE(created_at) = ...` — which makes the predicate unusable for pruning or indexing.

**Poor physical layout.** The wrong partition key, thousands of small files, no clustering or sort order on the column everyone filters by. This is the one that is invisible in the query text and is why the same SQL is fast on one table and slow on another.

**Resource limits.** Spilling to disk because a hash table did not fit, an oversized shuffle, or not enough parallelism because the data is in too few files. The signal is a plan that looks reasonable and an execution that is not.

The reason to hold these as categories rather than a list of fixes is that they are diagnosed differently. The first four are visible in the plan; the fifth is only visible in the execution profile. Knowing which one you are in tells you which artifact to open — and "it got slower without the query changing" points at layout or statistics before it points at anything you wrote.

The reasoning

**1 · Find where the time goes.** Read the plan and the execution profile. Full scan? Large redistribution? A sort? A join whose estimate is wildly off? Everything after this is guided by what you find, and optimizing without it is how people spend a day on the wrong operator.

**2 · Reduce what is read.** Project only the columns you need — on a columnar store this alone is often the whole win. Push filters as early as possible, and make sure the partition filter is a literal predicate on the partition column rather than an expression the engine cannot use. Skip partitions you do not need instead of filtering them out afterwards.

**3 · Fix the joins.** Filter both sides before joining rather than after. Join on the keys the table is actually distributed or indexed on. Broadcast the small side when one is small. If one key dominates, handle the skew explicitly rather than hoping the optimizer notices.

**4 · Improve the layout.** Columnar format, partitioned on the column the queries filter by, files large enough not to drown the planner, and clustered or sorted on the second most common filter. This is the change that helps every query against the table rather than the one you were asked about.

**5 · Remove expensive patterns.** Drop `DISTINCT` where the grain already guarantees uniqueness, avoid functions on filtered columns, aggregate before a large join rather than after it, and materialise a CTE that is scanned three times.

**6 · Only then tune the engine.** Parallelism, shuffle partitions, caching what is genuinely reused, and refreshing statistics so the optimizer stops guessing wrong. These matter, and they are last because a well-tuned engine reading ten times more data than it needs is still reading ten times more data than it needs.

The reasoning

**Wall clock is the weakest evidence you have.** It moves with cluster load, cache state, and whoever else is querying. A "10× improvement" measured once against a cold baseline and a warm rewrite is not a measurement. Compare **bytes scanned**, **rows processed** and **shuffle volume** — those are properties of the query, not of the afternoon.

**Confirm the plan changed the way you meant.** If you intended a broadcast join, the plan should show one. If you intended partition pruning, the scan should show fewer partitions. A query that got faster for a reason you did not intend will get slower again the moment that reason goes away, and you will not know why.

**Prove the results are identical.** This is the step that matters most and is skipped most. Run both versions and diff them — row counts, and a checksum or per-key comparison of the metrics. A rewrite that drops rows, changes a join to inner, or loses NULLs is faster because it is doing less *work you needed*. "Same answer, less money" is the whole contract of optimization, and the first half is not optional.

**Then check stability.** Run it several times, at different times of day, against a realistic data volume rather than a dev sample. An optimization that only holds when the data fits in memory is not an optimization, it is a threshold you have not found yet.

The reasoning

The distinction in one line: **partitioning eliminates data, indexing locates rows.** A partition predicate lets the engine skip entire files or directories before reading anything. An index lets it jump to specific rows without a sequential scan of what it did read. They compose — you partition by date and index or cluster within a partition.

**Partitioning** is right for a low-cardinality column that nearly every query filters on, where each partition holds a substantial amount of data. Date is the canonical example. It is a storage layout, so it is expensive to change and it affects writes as well as reads.

**Indexing** is right for high-selectivity lookups on a transactional store — find this customer, find this order. It costs write throughput, because every insert and update maintains every index, and it stops helping once a query needs a large fraction of the table, at which point the optimizer will correctly ignore it and scan.

**The wrinkle worth raising**, because it is where the analogy breaks: analytical warehouses mostly do not have indexes in the OLTP sense. Snowflake, BigQuery and Redshift give you clustering keys, sort keys and micro-partition metadata instead — the engine keeps min/max statistics per file and skips files that cannot match. That is closer to partitioning than to indexing, and saying "add an index" in a warehouse interview signals you have only worked on OLTP.

The reasoning

**Low selectivity.** An index lookup is random I/O: find the entry, jump to the row, repeat. Past roughly 5–20% of the table — the exact crossover depends on the storage and the row width — sequential reading of everything beats jumping to most of it. The optimizer is estimating that crossover, and when it estimates correctly the scan is the cheaper plan.

**A small table.** If it fits in a few pages, reading all of it costs less than consulting an index and then reading it anyway.

**The index cannot be used.** A function on the column (`WHERE DATE(created_at) = ...`), an implicit type conversion, a leading wildcard in a `LIKE`, or a composite index whose leading column is not in the predicate. Here the scan is not a choice — the index is simply unusable, and this is the case worth noticing because it is fixable.

**Distributed engines change the sum.** With enough parallelism, a full scan is many machines reading sequentially at once, which is what columnar warehouses are built for. There the question is not "scan or index" but "how many files did we skip", and a full scan of a well-pruned partition is the normal, correct plan.

The diagnostic that matters: when a scan looks wrong, check whether the estimated row count matches reality. **A wildly wrong estimate usually means stale statistics**, and that — not the scan — is the bug. Fix the statistics and let the optimizer choose again rather than forcing the plan with a hint.

The reasoning

**The bad news operators.** `DS_DIST_BOTH` means both sides of the join are being redistributed across the cluster — the most expensive thing a plan can say, and it happens when neither table is distributed on the join key. `DS_BCAST_INNER` broadcasts the inner table to every node, which is fine for a small dimension and ruinous for a large one. `DS_DIST_INNER` redistributes one side only. Seeing `DS_DIST_NONE` means both sides were already co-located and nothing moves — that is the goal.

**Merge join is the cheapest join available**, and it requires three things to hold at once: both tables distributed on the join column, both sorted on that same column, and an equality join predicate. Miss any one and Redshift falls back to a hash join, which has to build a hash table and often redistribute first.

**What this means for design.** Distribution key and sort key are chosen for the joins and filters the table will serve, not for the table in isolation — that is the mental shift. Fact and dimension co-distributed on the join key turn the most expensive operator in the plan into no operator at all. `DISTSTYLE ALL` on a small dimension achieves the same by replicating it everywhere.

**Portability caveat, worth saying out loud in an interview**: this is Redshift-specific vocabulary. Snowflake and BigQuery do not expose distribution styles at all — they cluster and prune micro-partitions instead — so the concept transfers but the operator names do not. Knowing which of your knowledge is engine-specific is itself a signal.

The reasoning

**Separate the two halves first.** A dashboard issues one query per visual, plus one per filter that populates its own value list. Twenty visuals is twenty queries, and a dashboard where each query takes 800 ms is slow for a completely different reason than one where a single query takes 18 seconds. Get the query log, time each statement, and see which shape you are in — this is the step that decides everything after it.

**If individual queries are slow**, it is the ordinary optimization problem: what is being scanned, is the date filter pruning, is a join fanning out, is the dashboard querying the lowest-grain table when it only ever displays a monthly total. **Pre-aggregation is usually the answer** for a dashboard specifically, because dashboards ask the same few questions repeatedly at a fixed grain — a table built at that grain turns a scan of a billion rows into a scan of a few thousand.

**If individual queries are fast and the page is slow**, the problem is the count and the shape of the round trips: too many visuals, filters that each trigger a full refresh, calculated fields evaluated per row in the BI layer rather than pushed into SQL, and cascading filters that fire a query per level. Reducing visuals and pushing calculations down are real fixes and they are invisible from the warehouse side.

**Then decide about caching, deliberately.** An extract or in-memory cache — SPICE, an imported model, a materialized view — takes the warehouse out of the interactive path entirely and is the reliable route to sub-2-second loads. The cost is freshness: the dashboard now shows data as of the last refresh, which is fine for a monthly finance report and wrong for an operations screen. That is a requirements question, not a technical one, and it is worth asking before optimizing.

**Measure both ends.** Time to first byte per query and page load time before and after, so you can say which change bought what. Otherwise you will pre-aggregate, add a cache and remove six visuals at once, and never know which one mattered.

The reasoning

**The mechanism.** A composite index is sorted by the first key, and within each distinct first-key value, by the second. With `(customer_id, created_at)` the engine seeks to the customer, and that customer’s rows are contiguous and in date order — so the date range is a walk from a known start to a known end.

**Reverse the order and that breaks.** `(created_at, customer_id)` is sorted by date; within one exact timestamp it is sorted by customer. A range over a month spans millions of distinct timestamps, so the customer ids are scattered across the whole range and the engine has to filter every row it walks. The index is used and it stops narrowing after the first predicate.

**The general rule is equality columns first, range columns last** — and at most one range column can be seeked, because everything after it is unordered relative to the range. If a query has two ranges, only the first is a seek and the second is a filter.

**Ordering among equality columns matters less than people say.** All of them are seeked either way, so the classic "most selective first" advice mostly affects index size and the ability of *other* queries to reuse the index as a prefix. Choosing the order that serves the most query shapes as a leftmost prefix is usually worth more than a marginal selectivity gain.

**One more benefit of the right order:** `WHERE customer_id = ? ORDER BY created_at DESC LIMIT 10` gets the sort for free, because the index already holds that customer’s rows in date order. That is often the larger win and it disappears entirely with the columns reversed.

The reasoning

**Where the cost comes from.** An insert into a table with six indexes is seven write operations, not one. An update that touches an indexed column deletes and re-inserts the entry in each affected index. A covering index carries extra payload columns to make an index-only scan possible, so it is physically larger and every one of those maintenance writes moves more bytes.

**Why it took two months to notice.** The read win is visible immediately in one dashboard; the write cost is a few hundred microseconds spread across millions of statements, and it only surfaces when the table grows enough that the index no longer fits comfortably in cache. Index maintenance that was buffered starts causing real IO.

**What to check, in order.** First, the index list against usage counts: unread indexes are pure cost and are often the real problem rather than the new one. Second, prefix-redundant indexes — a single-column index on the leading key of a composite gives nothing the composite does not. Third, whether the covering index needs all its payload columns, or whether a narrower key with fewer included columns still avoids the table lookup for the query that mattered.

**And check the obvious non-index causes** before rewriting the schema: the table has two months more data, autovacuum or statistics maintenance may be behind, and the write pattern itself may have changed. An index audit is cheap and reversible, which is why it is a good first move, not because it is always the answer.

**The rule worth adopting:** pair every index addition with a removal, or at least with a review. Over-indexing is always built one reasonable decision at a time, and nobody is ever assigned the job of taking one away.

The reasoning

**Cause one: the predicate is not sargable.** `WHERE date_trunc('month', created_at) = ?` or `WHERE LOWER(email) = ?` asks about a function of the column, and the index stores the column. Symptom: rows examined is the whole table. Fix: rewrite as a range, or build an index on the expression.

**Cause two: the column is not selective enough.** An index on a six-value status column points at a third of the table for a common value, and following it row by row costs more than a sequential scan. The planner is right to refuse. Symptom: the plan shows a deliberate scan and the statistics show few distinct values. Fix: a partial index on the rare value, or accept it.

**Cause three: the statistics are stale.** Planners choose from statistics, not from data. After a bulk load the planner may still believe the table has a thousand rows, or that a column has three distinct values when it now has a million. Symptom: the plan’s estimated row count differs wildly from the actual. Fix: ANALYZE, then look again.

**How to separate them quickly.** Read the plan and compare estimated against actual rows — a large mismatch is cause three. If they agree and the whole table was read, look at the predicate for a function or a leading wildcard: that is cause one. If the predicate is clean and the planner still scanned, check distinct values against row count: that is cause two.

**Two rarer causes worth knowing.** A type mismatch — comparing a `varchar` column to a number — forces an implicit cast on the column and behaves exactly like cause one. And on a small table, a scan genuinely is faster; the planner is not wrong, and there is nothing to fix.

The reasoning

**What the two things are.** A view stores nothing: the engine substitutes its definition into your query and plans the whole thing, so you pay the full cost on every read and can never be stale. A materialized view stores the result, so reads are a scan of a small table and the price is that the answer is as old as the last refresh.

**The first question is staleness.** For a daily revenue tile, an hour old is fine. For a fraud check or an inventory count at checkout, it is not, and no amount of speed makes a wrong number acceptable.

**The second is the read-to-change ratio.** Materialization pays when the result is read far more often than the underlying data changes. A dashboard read twice a day over a table that changes every second is the case where you pay refresh cost continuously to save two queries.

**The third is ownership, and it is the one that gets skipped.** A materialized view has a refresh that can fail. When it does, queries keep succeeding and return last week’s figures — a silent failure, which is the worst kind. It needs a freshness check (max timestamp against now) alerting independently of the refresh job, and a person who is on call for it.

**And consider the third option.** If the logic is complex, or the refresh needs to be incremental in a way the engine will not do for you, build a table in the pipeline instead. It is the same work, made explicit, with the same monitoring as everything else you own — rather than a database object that quietly behaves like a pipeline.