lesson 68 Performance & query plans · advanced

SARGable ranges, not speed commandments

Compare range filters and functions on indexed columns.

the idea

A predicate is SARGable when the database can use an index to search it: the indexed column appears bare, compared with a value or range. Wrapping the column in a function, such as date(scheduled_departure) = '2025-01-15', hides it from a plain index; the equivalent half-open range keeps it searchable.

Dataset: U.S. Flights

The tables the challenges use, with their first 6 rows. Under each column name: the kind of value it holds, and which table it links to.

worked example

Run these steps in order to see the idea before you write your own query. The step marked “the pattern” is what the challenges ask for. Running the example does not count toward progress.

1. 1

   Wrap the column in a function. date(scheduled_departure) hides the indexed column from a plain index search, so the plan often still scans despite idx_flight_time.the pattern

   example.sql Run

   sql›

   CREATE INDEX idx_flight_time ON flights(scheduled_departure);
   EXPLAIN QUERY PLAN
   SELECT flight_id
   FROM flights
   WHERE date(scheduled_departure) = '2025-01-15';

2. 2

   Filter with a searchable range. Leaving scheduled_departure bare in a half-open >= / < range is the SARGable form—the plan can search the index for January 15th flights.

   step-2.sql Run

   sql›

   CREATE INDEX idx_flight_time ON flights(scheduled_departure);
   EXPLAIN QUERY PLAN
   SELECT flight_id
   FROM flights
   WHERE scheduled_departure >= '2025-01-15 00:00'
     AND scheduled_departure < '2025-01-16 00:00';

common mistake

SARGability is about access paths, not a rule that functions are slow. On a tiny table a scan can be faster; on 124,460 flights the difference is visible in the plan.