=> EXPLAIN SELECT total_amount FROM bookings ORDER BY total_amount DESC LIMIT 1;
QUERY PLAN ------------------------------------------------------------------------------ Limit (cost=45113.65..45113.65 rows=1 width=6) -> Sort (cost=45113.65..50391.43 rows=2111110 width=6) Sort Key: total_amount DESC -> Seq Scan on bookings (cost=0.00..34558.10 rows=2111110 width=6) (4 rows)
Планировщик последовательно сканирует всю таблицу, а затем сортирует данные. Это не эффективный доступ, поскольку нам требуется только одно значение. Но, пока нет индекса, это единственно возможный способ.
Создаем индекс.
=> CREATE INDEX ON bookings(total_amount);
CREATE INDEX
Повторим запрос:
=> EXPLAIN SELECT total_amount FROM bookings ORDER BY total_amount DESC LIMIT 1;
QUERY PLAN ------------------------------------------------------------------------------------------------------------------------- Limit (cost=0.43..0.48 rows=1 width=6) -> Index Only Scan Backward using bookings_total_amount_idx on bookings (cost=0.43..108623.04 rows=2111110 width=6) (2 rows)
Теперь планировщик выбрал только индексное сканирование.
Сформулируем запрос по-другому:
=> EXPLAIN SELECT max(total_amount) FROM bookings;
QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------- Result (cost=0.48..0.49 rows=1 width=32) InitPlan 1 (returns $0) -> Limit (cost=0.43..0.48 rows=1 width=6) -> Index Only Scan Backward using bookings_total_amount_idx on bookings (cost=0.43..113900.82 rows=2111110 width=6) Index Cond: (total_amount IS NOT NULL) (5 rows)
Видно, что и в этом случае планировщик выбрал индексное сканирование (добавив условие NOT NULL, поскольку функция max не должна учитывать неопределенные значения).
Новый узел плана, который здесь появляется - InitPlan. Он соответствует подзапросу, который выполняется один раз. То есть, фактически, планировщик переформулировал запрос следующим образом:
=> EXPLAIN SELECT ( SELECT total_amount FROM bookings WHERE total_amount IS NOT NULL ORDER BY total_amount DESC LIMIT 1 );
QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------- Result (cost=0.48..0.49 rows=1 width=16) InitPlan 1 (returns $0) -> Limit (cost=0.43..0.48 rows=1 width=6) -> Index Only Scan Backward using bookings_total_amount_idx on bookings (cost=0.43..113900.82 rows=2111110 width=6) Index Cond: (total_amount IS NOT NULL) (5 rows)
Если бы подзапрос выполнялся несколько раз, вместо InitPlan в плане появился бы узел SubPlan.
Порядок важен для многоколоночных индексов. Создадим индекс на таблице рейсов по аэропортам вылета и прилета:
=> CREATE INDEX dep_arr on flights(departure_airport, arrival_airport);
CREATE INDEX
Индекс может использоваться для такого запроса:
=> EXPLAIN SELECT * FROM flights ORDER BY departure_airport, arrival_airport;
QUERY PLAN --------------------------------------------------------------------------------- Index Scan using dep_arr on flights (cost=0.42..15199.59 rows=214867 width=63) (1 row)
И для такого:
=> EXPLAIN SELECT * FROM flights ORDER BY departure_airport DESC, arrival_airport DESC;
QUERY PLAN ------------------------------------------------------------------------------------------ Index Scan Backward using dep_arr on flights (cost=0.42..15199.59 rows=214867 width=63) (1 row)
Но не для такого (сортировка в разных направлениях):
=> EXPLAIN SELECT * FROM flights ORDER BY departure_airport, arrival_airport DESC;
QUERY PLAN ---------------------------------------------------------------------- Sort (cost=31675.96..32213.12 rows=214867 width=63) Sort Key: departure_airport, arrival_airport DESC -> Seq Scan on flights (cost=0.00..4564.67 rows=214867 width=63) (3 rows)
Здесь приходится отдельно выполнять сортировку результатов. В этом случае поможет другой индекс:
=> CREATE INDEX dep_asc_arr_desc ON flights(departure_airport, arrival_airport DESC);
CREATE INDEX
=> EXPLAIN SELECT * FROM flights ORDER BY departure_airport, arrival_airport DESC;
QUERY PLAN ------------------------------------------------------------------------------------------ Index Scan using dep_asc_arr_desc on flights (cost=0.42..15199.59 rows=214867 width=63) (1 row)
А также для запроса с обратным порядком сортировки:
=> EXPLAIN SELECT * FROM flights ORDER BY departure_airport DESC, arrival_airport;
QUERY PLAN --------------------------------------------------------------------------------------------------- Index Scan Backward using dep_asc_arr_desc on flights (cost=0.42..15199.59 rows=214867 width=63) (1 row)