ПОСЛЕДОВАТЕЛЬНОЕ СКАНИРОВАНИЕ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Создадим отдельную базу данных и таблицу: => create database db15; CREATE DATABASE => \c db15 You are now connected to database "db15" as user "postgres". => create table t(id integer, s text); CREATE TABLE => insert into t(id, s) => select s.id, repeat('a',1000) => from generate_series(1,100000) as s(id) => order by random(); INSERT 0 100000 ....................................................................... Создадим индекс и выполним очистку и анализ таблицы. => create index on t(id); CREATE INDEX => vacuum analyze t; VACUUM Для того, чтобы проверить план запроса, используется команда explain. Посмотрим, какой метод доступа выберет оптимизатор, если требуются все строки таблицы. ....................................................................... => explain select * from t; QUERY PLAN ------------------------------------------------------------- Seq Scan on t (cost=0.00..15286.00 rows=100000 width=1008) (1 row) Выбран метод доступа Seq Scan - последовательное чтение, что правильно. В скобках приведены важные значения: * cost - оценка стоимости; * rows - оценка числа строк, возвращаемых операцией; * width - оценка размера одной записи в байтах. Стоимость указывается в некоторых условных единицах. Приводятся два числа: первое показывает оценку ресурсов на предварительную подготовку к операции, второе показывает общую оценку ресурсов для получения всех данных. В нашем случае мы видим, что последовательное сканирование возвращает строки по мере их получения. ....................................................................... ИНДЕКСНОЕ СКАНИРОВАНИЕ ~~~~~~~~~~~~~~~~~~~~~~ Проверим, какой метод доступа будет выбран для поиска одного значения: => explain select * from t where id = 42; QUERY PLAN --------------------------------------------------------------------- Index Scan using t_id_idx on t (cost=0.29..8.31 rows=1 width=1008) Index Cond: (id = 42) (2 rows) Выбран метод доступа Index Scan и указано имя использованного индекса. Строчкой ниже указано условие, по которому происходит обращение. Не стоит забывать, что Index Scan подразумевает обращение как к индексу, так и к таблице. ....................................................................... В строке Index Cond указывается только условия, которые используются для индексного доступа или могут быть проверены на уровне индекса. Дополнительные условия, которые можно проверить только по таблице, отображаются в отдельной строке Filter: => explain select * from t where id = 42 and s = 'abc'; QUERY PLAN --------------------------------------------------------------------- Index Scan using t_id_idx on t (cost=0.29..8.31 rows=1 width=1008) Index Cond: (id = 42) Filter: (s = 'abc'::text) (3 rows) ....................................................................... Важный и полезный вариант команды - explain analyze. Он не просто оптимизирует запрос и показывает план, но и выполняет запрос и дополняет вывод значениями, полученными в ходе выполнения. ....................................................................... => explain analyze select * from t where id = 42 and s = 'abc'; QUERY PLAN --------------------------------------------------------------------------------------------------------------- Index Scan using t_id_idx on t (cost=0.29..8.31 rows=1 width=1008) (actual time=0.040..0.040 rows=0 loops=1) Index Cond: (id = 42) Filter: (s = 'abc'::text) Rows Removed by Filter: 1 Planning time: 0.069 ms Execution time: 0.061 ms (6 rows) Добавились актуальные значения: * time - время получения первой и последней строки выборки; * rows - число строк (кардинальность); * loops - число раз, которое выполнялся этот шаг (подробнее позже). Также добавилась строка Rows Removed by Filter, а также время планирования и выполнения запроса. ....................................................................... СКАНИРОВАНИЕ БИТОВОЙ КАРТЫ ~~~~~~~~~~~~~~~~~~~~~~~~~~ Посмотрим, какой метод доступа будет выбран для поиска диапазона. ....................................................................... => explain select * from t where id > 90000; QUERY PLAN ----------------------------------------------------------------------------- Bitmap Heap Scan on t (cost=170.08..13939.10 rows=10037 width=1008) Recheck Cond: (id > 90000) -> Bitmap Index Scan on t_id_idx (cost=0.00..167.57 rows=10037 width=0) Index Cond: (id > 90000) (4 rows) Выбран метод доступа Bitmap Scan. Он состоит из двух узлов - это дерево запроса, но пока в простом, линейном виде. Выполнение начинается сверху: узел Bitmap Heap Scan обращается за данными ниже, к узлу Bitmap Index Scan. Узел Bitmap Index Scan читает индекс, строит битовую карту и возвращет ее наверх. Обратите внимание, что карта должна быть построена полностью, прежде чем ее можно будет вернуть. Узел Bitmap Heap Scan, получив карту, читает страницы таблицы. ....................................................................... Еще одно достоинство сканирования битовой карты - возможность объединения нескольких условий. => explain select * from t where id > 95000 or id < 5000; QUERY PLAN --------------------------------------------------------------------------------- Bitmap Heap Scan on t (cost=166.97..13863.18 rows=9572 width=1008) Recheck Cond: ((id > 95000) OR (id < 5000)) -> BitmapOr (cost=166.97..166.97 rows=9813 width=0) -> Bitmap Index Scan on t_id_idx (cost=0.00..81.61 rows=4975 width=0) Index Cond: (id > 95000) -> Bitmap Index Scan on t_id_idx (cost=0.00..80.58 rows=4838 width=0) Index Cond: (id < 5000) (7 rows) Здесь сначала были построены две битовые карты - по одной на каждое условие, а затем объединены побитовой операцией "или". Таким же образом могут быть использованы и разные индексы. ....................................................................... КЛАСТЕРИЗАЦИЯ ~~~~~~~~~~~~~ Если строки таблицы упорядочены так же, как и индекс, битовая карта становится излишней. Продемонстрируем это с помощью команды cluster. => cluster t using t_id_idx; CLUSTER => analyze t; ANALYZE ....................................................................... => explain select * from t where id > 90000; QUERY PLAN --------------------------------------------------------------------------- Index Scan using t_id_idx on t (cost=0.29..1650.43 rows=9722 width=1008) Index Cond: (id > 90000) (2 rows) Раньше использовался метод Bitmap Scan, но теперь проще и выгодней сделать обычное индексное сканирование. Обратите внимание: * команда cluster устанавливает эксклюзивную блокировку; * строки упорядочиваются, но не поддерживаются в упорядоченном виде - в процессе работы кластеризация будет ухудшаться. ....................................................................... ИСКЛЮЧИТЕЛЬНО ИНДЕКСНОЕ СКАНИРОВАНИЕ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Если вся необходимая информация содержится в самом индексе, то нет необходимости обращаться к таблице - за исключением проверки видимости: => explain select id from t where id > 90000; QUERY PLAN ----------------------------------------------------------------------------- Index Only Scan using t_id_idx on t (cost=0.29..1650.43 rows=9722 width=4) Index Cond: (id > 90000) (2 rows) Проверка видимости может происходить двумя способами: * с помошью карты видимости (эффективно); * с помощью чтения страниц таблицы (не эффективно). ....................................................................... => explain analyze select id from t where id > 90000; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------- Index Only Scan using t_id_idx on t (cost=0.29..1650.43 rows=9722 width=4) (actual time=0.027..23.456 rows=10000 loops=1) Index Cond: (id > 90000) Heap Fetches: 10000 Planning time: 0.104 ms Execution time: 25.416 ms (5 rows) Строка Heap Fetches показывает, сколько строк было проверено с помощью таблицы. Почему видимость всех 10000 строк проверялась по таблице? ....................................................................... Потому что после выполнение команды cluster не выполнялась очистка - стало быть, карта видимости не обновлена. => vacuum t; VACUUM ....................................................................... => explain analyze select id from t where id > 90000; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------- Index Only Scan using t_id_idx on t (cost=0.29..258.43 rows=9722 width=4) (actual time=0.015..3.542 rows=10000 loops=1) Index Cond: (id > 90000) Heap Fetches: 0 Planning time: 0.074 ms Execution time: 5.288 ms (5 rows) Теперь для проверки хватило карты видимости. ....................................................................... ДРУГИЕ СПОСОБЫ ДОСТУПА ~~~~~~~~~~~~~~~~~~~~~~ В планах запросов могут встретиться и другие виды доступа. Как правило, они понятны и без объяснений, но для полноты картины посмотрим несколько примеров. Сканирование функции применяется, если надо прочитать данные, возвращаемые функцией. => explain select * from pg_locks; QUERY PLAN --------------------------------------------------------------------------- Function Scan on pg_lock_status l (cost=0.00..10.00 rows=1000 width=162) (1 row) Можно считать его аналогом последовательного сканирования. ....................................................................... Сканирование значений используется в запросах с фразой value: => explain select * from (values (1),(2),(3)) v; QUERY PLAN ------------------------------------------------------------- Values Scan on "*VALUES*" (cost=0.00..0.04 rows=3 width=4) (1 row) ....................................................................... Сканирование общего табличного значения (CTE, Common Table Expression) применяется при использовании фразы with: => explain with a as (select * from t) select * from a; QUERY PLAN --------------------------------------------------------------------- CTE Scan on a (cost=15286.00..17286.00 rows=100000 width=36) CTE a -> Seq Scan on t (cost=0.00..15286.00 rows=100000 width=1008) (3 rows) Надо заметить, что подзапросы CTE всегда материализуются. ....................................................................... СОРТИРОВКА И ОГРАНИЧЕНИЕ ~~~~~~~~~~~~~~~~~~~~~~~~ Есть два способа выполнить сортировку. Первый - получить строки сканированием подходящего индекса: в этом случае данные автоматически будут отсортированы. => explain select * from t order by id; QUERY PLAN ------------------------------------------------------------------------------ Index Scan using t_id_idx on t (cost=0.29..16673.29 rows=100000 width=1008) (1 row) ....................................................................... Тот же самый индекс может использоваться и для сортировки в обратном порядке. => explain select * from t order by id desc; QUERY PLAN --------------------------------------------------------------------------------------- Index Scan Backward using t_id_idx on t (cost=0.29..16673.29 rows=100000 width=1008) (1 row) В этом случае мы спускаемся от корня дерева к правому листовому узлу, и проходим по списку листовых страниц в обратном порядке. ....................................................................... Второй способ - выполнить последовательное сканирование таблицы и затем отсортировать полученные данные. Чтобы посмотреть на план такого запроса, запретим индексный доступ (аналогично можно запретить и другие методы доступа): => set enable_indexscan=off; SET => explain select * from t order by id; QUERY PLAN ------------------------------------------------------------------- Sort (cost=67683.82..67933.82 rows=100000 width=1008) Sort Key: id -> Seq Scan on t (cost=0.00..15286.00 rows=100000 width=1008) (3 rows) ....................................................................... Для того, чтобы выполнить сортировку, надо получить весь набор данных. Это может оказаться неудачным, если в результате требуется только часть выборки. Обратите внимание на стоимость: => explain select * from t order by id limit 10; QUERY PLAN ------------------------------------------------------------------------- Limit (cost=17446.96..17446.99 rows=10 width=1008) -> Sort (cost=17446.96..17696.96 rows=100000 width=1008) Sort Key: id -> Seq Scan on t (cost=0.00..15286.00 rows=100000 width=1008) (4 rows) ....................................................................... Зато индексное сканирование позволяет получать данные по мере необходимости. Сравните стоимость с предыдущим вариантом: => reset enable_indexscan; RESET => explain select * from t order by id limit 10; QUERY PLAN ------------------------------------------------------------------------------------ Limit (cost=0.29..1.96 rows=10 width=1008) -> Index Scan using t_id_idx on t (cost=0.29..16673.29 rows=100000 width=1008) (2 rows) Конец демонстрации. ....................................................................... => alter system reset all; ALTER SYSTEM => select pg_reload_conf(); pg_reload_conf ---------------- t (1 row) => \q