SARGable SQL Query Optimization & Indexing
Master advanced SQL query optimization, window partitioning, recursive CTE patterns, and SARGable search arguments for high-scale databases.
See the About page for curation and verification details.
Mastering SQL queries requires developing an accurate mental model of the database engine's logical execution pipeline: FROM, WHERE, GROUP BY, HAVING, SELECT, and ORDER BY. Senior data engineers and backend developers do not rely on trial-and-error query construction; they structure analytical queries using window function partitioning, conditional aggregation, recursive expressions, and index-friendly search arguments. This masterclass covers advanced SQL patterns that power mission-critical production systems and high-throughput data pipelines.
Finding top N records per group with ties
A frequent analytical problem asks: 'Find the top two highest-paid employees in every department. If there is a tie for second place, include all tied employees.' Using ROW_NUMBER() would arbitrarily discard tied records. The correct solution uses DENSE_RANK() inside a Common Table Expression:
CREATE TABLE staff (
emp_id INTEGER PRIMARY KEY,
dept TEXT,
salary REAL
);
INSERT INTO staff VALUES
(1, 'Eng', 120000), (2, 'Eng', 110000), (3, 'Eng', 110000), (4, 'Eng', 90000),
(5, 'HR', 85000), (6, 'HR', 80000);
WITH RankedStaff AS (
SELECT emp_id, dept, salary,
DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) as rank_num
FROM staff
)
SELECT dept, salary, emp_id
FROM RankedStaff
WHERE rank_num <= 2
ORDER BY dept, rank_num;Verified SQLite output: Eng | 120000.0 | 1 Eng | 110000.0 | 2 Eng | 110000.0 | 3 HR | 85000.0 | 5 HR | 80000.0 | 6
In Engineering, both employee 2 and employee 3 earn 110,000 and share rank 2. DENSE_RANK() ensures both employees are preserved in the result set.
The NULL hazard in NOT IN subqueries
One of the most dangerous silent bugs in SQL occurs when using WHERE column NOT IN (SELECT ...) when the subquery returns even a single NULL value:
CREATE TABLE clients (id INT);
CREATE TABLE churned (id INT);
INSERT INTO clients VALUES (1), (2), (3);
INSERT INTO churned VALUES (2), (NULL);
SELECT * FROM clients WHERE id NOT IN (SELECT id FROM churned);Verified SQLite output: (Empty result set)
The query returns zero rows, even though client 1 and client 3 never churned. Why? NOT IN (2, NULL) expands logically to id <> 2 AND id <> NULL. Since any comparison with NULL yields unknown, the entire boolean expression evaluates to unknown, and the WHERE clause drops every row.
The production fix is using NOT EXISTS or LEFT JOIN ... WHERE right.id IS NULL, which handle NULL values safely:
SELECT c.id FROM clients c
WHERE NOT EXISTS (
SELECT 1 FROM churned ch WHERE ch.id = c.id
);Verified output with NOT EXISTS: 1 3
Finding consecutive streaks: gaps and islands
The classic 'gaps and islands' problem asks to identify consecutive streaks of activity (e.g. daily logins). The elegant solution uses the difference between two row numbers:
CREATE TABLE user_logins (user_id INT, login_day INT);
INSERT INTO user_logins VALUES (1, 1), (1, 2), (1, 3), (1, 5), (1, 6);
WITH RankedLogins AS (
SELECT user_id, login_day,
login_day - ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_day) as island_id
FROM user_logins
)
SELECT user_id, MIN(login_day) as streak_start, MAX(login_day) as streak_end, COUNT(*) as streak_length
FROM RankedLogins
GROUP BY user_id, island_id
ORDER BY streak_start;Verified SQLite output: 1 | 1 | 3 | 3 1 | 5 | 6 | 2
For consecutive days (1, 2, 3), login_day - row_number produces (1-1=0, 2-2=0, 3-3=0), creating a constant island_id 0. When day 5 arrives, 5 - 4 = 1, forming a new island. Grouping by island_id reveals consecutive streaks instantly.
Deduplicating records without primary keys
A common data engineering challenge asks how to eliminate duplicate rows from a staging table that lacks a unique primary key. Using window functions, we partition by the natural business keys, assign row numbers, and delete duplicates where row_num > 1:
WITH RankedDuplicates AS (
SELECT rowid,
ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at DESC) as rn
FROM user_staging
)
DELETE FROM user_staging
WHERE rowid IN (SELECT rowid FROM RankedDuplicates WHERE rn > 1);This pattern guarantees deterministic deduplication in a single atomic transaction, preserving the latest active record while removing all historical duplicates.
Conditional pivoting with the FILTER (WHERE ...) clause
Modern SQL standards and database engines (PostgreSQL 9.4+, SQLite 3.30+) support the elegant FILTER (WHERE ...) clause appended directly to aggregate functions:
SELECT dept,
COUNT(*) AS total_staff,
COUNT(*) FILTER (WHERE salary > 100000) AS high_earners,
AVG(salary) FILTER (WHERE salary > 100000) AS avg_high_salary
FROM staff
GROUP BY dept;Verified SQLite output: Eng | 4 | 3 | 113333.33 HR | 2 | 0 | NULL
The FILTER clause is cleaner and more self-documenting than traditional CASE WHEN syntax, allowing analytical reports to compute multiple conditional aggregates across distinct thresholds in a single linear table scan.
SARGable range queries for timestamp filtering
A frequent production blunder is querying timestamp columns using string formatting: WHERE strftime('%Y-%m', created_at) = '2026-11'. This function call disables B-Tree indexes on created_at, forcing an exhaustive scan of hundreds of millions of rows. The high-performance rewrite uses an indexed range comparison: WHERE created_at >= '2026-11-01' AND created_at < '2026-12-01', enabling sub-millisecond B-Tree index seeks.
High-performance pagination: Keyset seeking versus OFFSET
In API development, traditional pagination using LIMIT 20 OFFSET 100000 is an infamous performance bottleneck. The database engine must scan all 100,020 rows, discard the first 100,000 rows in memory, and return the final 20, resulting in multi-second page loads as users paginate deeper into the dataset.
The high-scale industry standard is Keyset Pagination (also called the Seek Method). Instead of an offset count, the query filters using the unique indexed key of the last record seen on the previous page:
SELECT id, title, created_at
FROM articles
WHERE id > 10450
ORDER BY id ASC
LIMIT 20;Because the WHERE predicate filters on the indexed primary key, the database engine performs an instantaneous B-Tree seek directly to row 10,451 and reads exactly 20 consecutive leaf rows in microseconds, maintaining constant O(1) performance regardless of pagination depth.
Advanced SARGability Rules: Type Mismatches & Mathematical Operators
Beyond function wrapping and leading wildcards, subtle SARGability failures occur due to data type conversions and arithmetic expressions:
- Implicit type coercion: If a column phone_number is typed as VARCHAR(20) and an indexed query executes WHERE phone_number = 5550100 (comparing string against an integer literal), relational optimizers (such as PostgreSQL and MySQL) convert the column to integer using CAST(phone_number AS integer), immediately destroying B-Tree index seeks. Passing parameter types matching column schemas is mandatory for SARGability.
- Range scan arithmetic: In predicates like WHERE timestamp + 3600 > 1700000000, the database evaluates + 3600 for every row. Rewriting to WHERE timestamp > 1700000000 - 3600 computes the constant subtraction once at query compile time, enabling a clean logarithmic B-Tree range seek.
Four Subtle Traps That Break SARGability
- Function wrapping on indexed date columns: Writing
WHERE strftime('%Y', created_at) = '2026'forces the database engine to evaluate thestrftimefunction on every single row in the table, invalidating B-Tree indexes and forcing a full table scan. The SARGable rewrite is an indexed range:WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01'. - Leading wildcard searches in string predicates:
WHERE email LIKE '%@pycodeit.com'cannot utilize standard B-Tree index navigation because B-Trees are sorted by starting characters. Conversely, trailing wildcards likeWHERE email LIKE 'admin%'ARE SARGable and execute fast range scans. - Mathematical operations on indexed columns: Writing
WHERE salary * 1.10 > 100000disables index seeks. The optimizer does not perform algebraic inversion. The SARGable equivalent is moving math to the literal side:WHERE salary > 100000 / 1.10. - Offset pagination performance collapse:
SELECT * FROM transactions ORDER BY id LIMIT 20 OFFSET 50000forces the database engine to read, traverse, and discard 50,000 records from the B-Tree before returning 20 rows. Keyset pagination (WHERE id > 50000 ORDER BY id LIMIT 20) navigates directly to the target record in O(log N) time.
SARGable Practice Drills & Before-and-After Execution Plans
Drill 1:
Rewrite this non-SARGable case-insensitive lookup into an index-friendly query:
-- Non-SARGable:
SELECT * FROM users WHERE LOWER(email) = 'admin@pycodeit.com';
-- SARGable Solution:
-- Option A: Store normalized lowercase emails during ingestion.
-- Option B: Create an expression index in SQLite 3.49+:
CREATE INDEX idx_users_lower_email ON users(LOWER(email));
SELECT * FROM users WHERE LOWER(email) = 'admin@pycodeit.com';Running EXPLAIN QUERY PLAN on Option B confirms the query transitions from SCAN users to SEARCH users USING INDEX idx_users_lower_email.
Drill 2:
Keyset Pagination Implementation.
-- High performance keyset query:
SELECT id, user_id, amount, created_at
FROM transactions
WHERE id > 10452
ORDER BY id ASC
LIMIT 25;Walkthrough: Because id is indexed, the B-Tree seeks directly to key 10452 in O(log N) time and scans the next 25 consecutive leaf entries, achieving sub-millisecond response times regardless of whether page 1 or page 10,000 is requested.
Drill 3:
The BETWEEN Edge Case Trap on Timestamps.
Writing WHERE created_at BETWEEN '2026-01-01' AND '2026-01-31' evaluates to '2026-01-31 00:00:00'. Any order placed on January 31st at 14:30:00 is silently excluded! The robust SARGable range pattern is: WHERE created_at >= '2026-01-01' AND created_at < '2026-02-01'.
Drill 4:
Prefix Wildcards and SQLite Full-Text Search (FTS5).
When business requirements demand arbitrary substring or infix searching (e.g. WHERE description LIKE '%python%'), standard B-Tree indexes cannot assist. The production solution in SQLite is creating a Full-Text Search virtual table using FTS5: CREATE VIRTUAL TABLE docs USING fts5(title, body);. FTS5 maintains an inverted index that performs sub-millisecond keyword and token lookups with logarithmic complexity.
Practice database optimization in our SQL Fundamentals Practice track and read our complete guide on Reading EXPLAIN QUERY PLAN in SQLite.