In an EXPLAIN plan, what is the difference between an Index Cond and a Filter?
answer
- one narrows the scan, one prunes afterwards
- where does the reading stop?
- cost per row read versus per row returned
- look for how many rows were discarded
- the selective predicate wants to position the seek
basics
~20 sAn index condition positions the index scan, so only matching entries are visited. A filter is evaluated on every row the scan already produced, and rows failing it are thrown away — work you paid for and discarded.
solid answer
~50 sThe split tells a query author which parts of their `WHERE` clause the access method could use and which it could not. A predicate reported as an **index condition** narrows the scan itself: the engine seeks into the index and walks only the matching range. A predicate reported as a **filter** is applied afterwards, to every row the scan produced, so its cost is proportional to rows read rather than rows returned. PostgreSQL prints these as `Index Cond:` and `Filter:`, with `Rows Removed by Filter:` telling you exactly how much work was wasted; MySQL's `EXPLAIN` says `Using index condition` when the predicate is pushed down to the index and `Using where` when rows are filtered after retrieval. The practical reading: whichever of your predicates is most selective wants to be an index condition, and a predicate stuck under `Filter` with a large removed-rows count is the one to reshape or to index.
code
sql · 6 lines-- customer_id can position an index scan; the function-wrapped
-- email predicate cannot, so it is evaluated per fetched row
SELECT order_id, status
FROM orders
WHERE customer_id = 42
AND LOWER(contact_email) LIKE 'a%';go deeper
Know that a plan can show two kinds of predicate: one that decides which index entries are visited, and one applied to rows already fetched. Be able to point at each in a short plan fragment.
Explain the cost difference — work proportional to rows read versus rows returned — and use the rows-removed counter to judge whether a filtered predicate actually matters.
Turn the plan into a decision: reshape the predicate, request an index, or accept it, with the volumes to justify the choice, and verify the predicate moved after the change.
Push the practice upstream: hot queries should be reviewed with their plans attached, so predicate shape and the indexes the workload actually needs are decided together rather than discovered in an incident.
## Two places a predicate can be applied Every predicate in your `WHERE` clause ends up in one of two roles. - **Access condition (index condition).** The engine uses it to decide *where to start and stop reading*. With a B-tree index on `customer_id`, the predicate `customer_id = 42` lets the scan jump straight to the matching entries and stop when they run out. Non-matching rows are never visited. - **Filter (residual predicate).** The engine has a row in hand and evaluates the predicate to decide whether to keep it. Failing rows were still read, decoded and discarded. Both produce the same result set. They differ enormously in how much work is done to get there. ## What the plan text looks like A PostgreSQL node might read: ```text Index Scan using idx_orders_customer on orders Index Cond: (customer_id = 42) Filter: (status = 'PAID') Rows Removed by Filter: 9800 ``` That says: the index found the customer's orders, then 9,800 of those rows were read and thrown away because their status was not `PAID`, and only the survivors were returned. In MySQL, the corresponding signals live in the `Extra` column: `Using index condition` means the predicate was handed to the storage engine and applied while walking the index, while `Using where` means rows were fetched and then filtered. The `key` column names the index actually chosen and `rows` carries the estimate. ## Why a predicate ends up as a filter Only some predicates can position a scan. Typical reasons a predicate lands under `Filter`: - No index covers that column at all, so there is nothing to seek on. - The column is in an index but not in a position the scan can use for seeking, given the other predicates. - The predicate is written in a shape the access method cannot seek with — the column wrapped in a function, a pattern with a leading wildcard, a comparison against a value of a different type. The first two are index-design questions. The third is yours as the author, and the plan is the evidence: if the predicate you consider most selective shows up under `Filter`, look at how you wrote it before blaming the database. ## Rows removed is the number that matters A filter costs nothing when it removes nothing. The signal to act on is a big gap between rows read and rows returned — in PostgreSQL, a large `Rows Removed by Filter`. Reading a million rows to return fifty means the access path is doing almost entirely wasted work; reading sixty to return fifty means the filter is harmless and your time is better spent elsewhere in the plan. ## What the author does about it Three responses, in order of cheapness: 1. **Rewrite the predicate** so the column appears bare and comparable, if the shape is what blocked it. 2. **Reconsider which predicate you are relying on**: sometimes a second, more selective condition exists in the query and simply has no index behind it, which is a request to file rather than a query bug. 3. **Accept it.** If the scan returns few rows anyway, a filter that removes a handful of them is not worth a change. After any of these, re-read the plan: the confirmation you want is the predicate moving out of `Filter` and into the index condition, and the removed-rows count collapsing. ## Portability of the labels The concept — access condition versus residual predicate — is universal, but the wording is not. PostgreSQL says `Index Cond` / `Filter` / `Recheck Cond`; MySQL uses `Extra` values such as `Using index condition`, `Using where` and `Using index`; SQL Server shows seek predicates versus predicates on the operator; Oracle distinguishes `access` from `filter` predicates. Learn the distinction once and look up the spelling per engine. ## How to answer Define both roles in one sentence each, say why the difference is cost-proportional-to-rows-read versus rows-returned, name the removed-rows counter as the thing you look at, and finish with the author's action: get the selective predicate into the index condition, or accept the filter when the volumes are small.
- Is a predicate shown as a Filter always a problem?No. A filter costs work only in proportion to the rows the scan already produced. If the access path returns sixty rows and the filter removes ten, it is irrelevant. The signal is a large gap between rows read and rows returned — that is when the filtered predicate deserves attention.
- Where does MySQL's EXPLAIN show the same distinction?In the `Extra` column: `Using index condition` means the predicate was pushed to the storage engine and applied while scanning the index, `Using where` means rows were retrieved and then filtered, and `Using index` means the query was satisfied from index entries alone. The `key` column names the index chosen.
- Your most selective predicate appears under Filter. What do you check first in your own SQL?Whether the column appears bare and directly comparable: a function wrapped around it, a pattern starting with a wildcard, or a comparison against a differently-typed value all prevent the predicate from positioning a scan. Fixing the shape is free; asking for a new index is not.
saying these in an interview costs you the question
- Treats any predicate under Filter as automatically a bug
- Assumes a filter costs the same as an index condition
- Reads 'Filter' as meaning the index was not used at all
- Ignores the rows-removed counter when judging severity
- Expects the same wording in every engine's plan output