The following queries do not provide start and
stop conditions for a scan of OrigIndex, the index
on the orig_airport column in Flights. However, some of these queries allow to do an index
rather than a table scan because the index covers the query.
-- would scan entire table; comparison is not with a
-- constant or with a column in another table
SELECT *
FROM Flights
WHERE orig_airport = dest_airport
-- would scan entire table; <> operator is not optimizable
SELECT *
FROM Flights
WHERE orig_airport <> 'SFO'
-- not valid operator for matching index scan
-- However, would do an index
-- rather than a table scan because
-- index covers query
SELECT orig_airport
FROM Flights
WHERE orig_airport <> 'SFO'
-- use of a function is not simple column reference
-- would scan entire index, but not table
-- (index covers query)
SELECT orig_airport
FROM Flights
WHERE lower(orig_airport) = 'sfo'