Single-column index examples
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'