P17 · SQL & Querying
SQL performance — why your filter scans it all
How SQL filters use an index — and the trap where wrapping a column in a function returns the right rows but quietly scans the whole table.
Start with the basics
A query's speed is mostly about how much data it has to read. An index lets the database skip straight to the rows you want instead of scanning the whole table.
But an index only helps if the filter compares the column directly. Wrap the column in a function, and the database can no longer use the index — it must read every row and compute the function.
The answer is identical either way, which is exactly why these bugs hide: the query is correct and slow, and correctness checks never catch it.
One that works
| id | ts |
|---|---|
| 1 | 2024-03-05 08:00 |
| 2 | 2024-03-06 01:00 |
SELECT COUNT(*) AS n
FROM logins
WHERE ts >= TIMESTAMP '2024-03-05'
AND ts < TIMESTAMP '2024-03-06';| n |
|---|
| 1 |
Comparing the column directly with a range lets the index prune — fast and correct.
An index can only skip rows if the filter compares the column directly — wrap the column in a function and the database must scan every row.
When to use it
- Care about this once tables are large enough that a full scan hurts.
- Keep the column bare on one side of the filter so the index can be used.
- Read the query plan to check whether it's pruning or scanning everything.
That one requirement — compare the column directly — is what a wrapped column breaks. Put the column inside a function and the database can't use the index, so it reads every row. Here is the day that bites.
See it happen
The filter that hides from the index — a wrapped column
You filter logins to one day by converting the timestamp to a date. It returns exactly the right rows — but the wrapped column hides from the index, so it scans every row.
| id | ts |
|---|---|
| 1 | 2024-03-05 08:00 |
| 2 | 2024-03-05 22:00 |
| 3 | 2024-03-06 01:00 |
wrapped column — scans every row
SELECT id FROM logins
WHERE CAST(ts AS DATE) = DATE '2024-03-05'
ORDER BY id;| id |
|---|
| 1 |
| 2 |
bare column range — prunes with the index
SELECT id FROM logins
WHERE ts >= TIMESTAMP '2024-03-05'
AND ts < TIMESTAMP '2024-03-06'
ORDER BY id;| id |
|---|
| 1 |
| 2 |
Both return the same two logins — but wrapping the column in a function hides it from the index, so the query scans every row instead of pruning to the day.
Walk the full example in the unit →The rest of the family
More ways a query gets slow
A full scan where you expected pruning, and the wrong join strategy, are the two big things to spot when reading a query plan.
On a column store, reading every column is the bill — selecting only what you need is cheaper.
When a sort or join outgrows memory it spills to disk, which is why ordering everything is expensive.
A LIMIT can change how the query runs, and offset paging re-scans while keyset paging does not.
Where the database computes a named subquery up front, it becomes a fence the optimizer can't see across.
Self-check
How to spot it in your own SQL
- A filtered query is slow even though it returns few rows.
- You wrapped a filtered column in a function or a cast.
- The plan shows a full scan where you expected an index.
- A wide SELECT * is slow on a large columnar table.
What people miss
Performance bugs don't change the answer, so they pass every correctness check — the query returns the right rows while quietly scanning the whole table, until the data grows and it falls over.
Go deeper
Ask Colearn
Instant answers, grounded in the same verified material the diagnostic grades against.
Why is my filtered query slow when it returns few rows?
Probably the filtered column is wrapped in a function or cast, which stops the database from using the index — so it reads every row and applies the function.
Rewrite it so the column is compared directly (a range on the raw column), and the index can prune.
from the unit →What should I look for when reading a query plan?
Two big smells: a full scan where you expected the index to prune, and the wrong join strategy for the data sizes.
Both mean the engine is doing far more work than the row counts justify.
from the unit →Does SELECT * really cost more?
On a columnar store, yes — it reads every column off disk, and projection is most of the bill.
Select only the columns you need.
from the unit →Why does my ORDER BY get slow on big data?
Sorting more data than fits in memory spills to disk, which is much slower — the habit of ordering everything is expensive at scale.
Sort only what you need, and page with keyset pagination rather than large offsets.
from the unit →That’s the free taste