Поле для индекса

Основные способы сканирования

postgres=# create table t(a integer, b text, c boolean);CREATE TABLEpostgres=# insert into t(a,b,c)select s.id, chr((32 random()*94)::integer), random() {amp}lt; 0.01from generate_series(1,100000) as s(id)order by random();INSERT 0 100000postgres=# create index on t(a);CREATE INDEXpostgres=# analyze t;ANALYZEМы создали таблицу с тремя полями.

Первое поле содержит числа от 1 до 100000, и по нему создан индекс (пока нам не важно, какой именно). Второе поле содержит различные ASCII-символы, кроме непечатных. Наконец, третье поле содержит логическое значение, истинное примерно для 1% строк, и ложное для остальных. Строки вставлены в таблицу в случайном порядке.

https://www.youtube.com/watch?v=ytcopyrightru

Попробуем выбрать значение по условию «a = 1». Заметим, что условие имеет вид «индексированное-поле оператор выражение», где в качестве оператора используется «равно», а выражением (ключом поиска) является «1». В большинстве случаев условие должно иметь именно такой вид, чтобы индекс мог использоваться.

postgres=# explain (costs off) select * from t where a = 1;QUERY PLAN          ——————————-Index Scan using t_a_idx on tIndex Cond: (a = 1)(2 rows)В данном случае оптимизатор принял решение использовать индексное сканирование (Index Scan). При индексном просмотре метод доступа возвращает значения TID по одному, до тех пор, пока подходящие строки не закончатся.

Индексное сканирование хорошо работает, когда речь идет всего о нескольких значениях. Однако при увеличении выборки возрастают шансы, что придется возвращаться к одной и той же табличной странице несколько раз. Поэтому в таком случае оптимизатор переключается на

postgres=# explain (costs off) select * from t where a {amp}lt;= 100;QUERY PLAN            ————————————Bitmap Heap Scan on tRecheck Cond: (a {amp}lt;= 100)-{amp}gt;  Bitmap Index Scan on t_a_idxIndex Cond: (a {amp}lt;= 100)(4 rows)Сначала метод доступа возвращает все TID, соответствующие условию (узел Bitmap Index Scan), и по ним строится битовая карта версий строк. Затем версии строк читаются из таблицы (Bitmap Heap Scan) — при этом каждая страница будет прочитана только один раз.

Обратите внимание, что на втором шаге условие может перепроверяться (Recheck Cond). Выборка может оказаться слишком велика, чтобы битовая карта версий строк могла целиком поместиться в оперативную память (ограниченную параметром work_mem). В этом случае строится только битовая карта страниц, содержащих хотя бы одну подходящую версию строки.

postgres=# create index on t(b);CREATE INDEXpostgres=# analyze t;ANALYZEpostgres=# explain (costs off) select * from t where a {amp}lt;= 100 and b = ‘a’;QUERY PLAN                    ————————————————–Bitmap Heap Scan on tRecheck Cond: ((a {amp}lt;= 100) AND (b = ‘a’::text))-{amp}gt;

postgres=# select attname, correlation from pg_stats where tablename = ‘t’;attname | correlation——— ————-b       |    0.533512c       |    0.942365a       | -0.00768816(3 rows)Значения, близкие по модулю к единице, говорят о высокой упорядоченности (как для столбца c), а близкие к нулю — наоборот, о хаотичном распределении (столбец a).

Поле для индекса

Для полноты картины следует сказать, что при неселективном условии оптимизатор предпочтет использованию индекса

postgres=# explain (costs off) select * from t where a {amp}lt;= 40000;QUERY PLAN      ————————Seq Scan on tFilter: (a {amp}lt;= 40000)(2 rows)И будет прав. Дело в том, что индексы работают тем лучше, чем выше селективность условия, то есть чем меньше строк ему удовлетворяет. При увеличении выборки возрастают и накладные расходы на чтение страниц индекса.

Ситуация усугубляется тем, что последовательное чтение выполняется быстрее, чем чтение страниц «вразнобой». Это особенно верно для жестких дисков, где механическая операция подведения головки к дорожке занимает существенно больше времени, чем само чтение данных; в случае дисков SSD этот эффект менее выражен.

Покрывающие индексы

Как правило, основная задача метода доступа — вернуть идентификаторы подходящих строк таблицы, чтобы механизм индексирования мог прочитать из них необходимые данные. Но что, если индекс уже содержит все необходимые для запроса данные? Такой индекс называется

(covering), и в этом случае оптимизатор может применить

postgres=# vacuum t;VACUUMpostgres=# explain (costs off) select a from t where a {amp}lt; 100;QUERY PLAN            ————————————Index Only Scan using t_a_idx on tIndex Cond: (a {amp}lt; 100)(2 rows)Название может навести на мысль, что механизм индексирования совсем не обращается к таблице, получая всю необходимую информацию исключительно от метода доступа.

Однако если бы механизму индексирования приходилось каждый раз заглядывать в таблицу для определения видимости, этот метод сканирования ничем не отличался бы от обычного индексного сканирования.

Проблема решается тем, что PostgreSQL поддерживает для таблиц так называемую карту видимости, в которой процесс очистки (vacuum) отмечает страницы, в которых данные не менялись достаточно давно для того, чтобы их видели все транзакции, независимо от времени начала и уровня изоляции. Если идентификатор строки, возвращенной индексом, относится к такой странице, то видимость можно не проверять.

Поле для индекса

Поэтому регулярное выполнение очистки повышает эффективность покрывающих индексов. Более того, оптимизатор учитывает число неочищенных строк и может отказаться от использования исключительно индексного сканирования, если спрогнозирует большие накладные расходы на проверку видимости.

https://www.youtube.com/watch?v=upload

Не все индексы хранят вместе с идентификаторами строк сами проиндексированные значения. Если метод доступа не может вернуть данные, он не может использоваться для исключительно индексного сканирования.

Индексы по нескольким полям

Условия на несколько полей могут быть поддержаны с помощью

postgres=# explain (costs off) select * from t where a {amp}lt;= 100;QUERY PLAN              ————————————–Bitmap Heap Scan on tRecheck Cond: (a {amp}lt;= 100)-{amp}gt;  Bitmap Index Scan on t_a_b_idxIndex Cond: (a {amp}lt;= 100)(4 rows)Как правило, если на первое поле не наложено условие, индекс использоваться не будет.

Не все методы доступа поддерживают создание индексов по нескольким столбцам.

Индексы по выражениям

Мы говорили о том, что условие поиска должно иметь вид «

text)(4 rows)Функциональный индекс создается не по полю таблицы, а по произвольному выражению; оптимизатор будет принимать во внимание такой индекс для условий вида «индексированное-выражение оператор выражение». Если вычисление индексируемого выражения — затратная операция, то и обновление индекса будет требовать значительных вычислительных ресурсов.

postgres=# d tTable “public.t”Column |  Type   | Modifiers——– ——— ———–a      | integer |b      | text    |c      | boolean |Indexes:”t_a_b_idx” btree (a, b)”t_a_idx” btree (a)”t_b_idx” btree (b)”t_lower_idx” btree (lower(b))

postgres=# d t_lower_idxIndex “public.t_lower_idx”Column | Type | Definition——– —— ————lower  | text | lower(b)btree, for table “public.t”

postgres=# alter index t_lower_idx alter column “lower” set statistics 69;ALTER INDEX

Частичные индексы

https://www.youtube.com/watch?v=ytcreatorsru

Иногда возникает необходимость проиндексировать только часть строк таблицы. Обычно это связано с сильной неравномерностью распределения: редкое значение имеет смысл искать по индексу, но частое проще найти полным сканированием таблицы.

postgres=# create index on t(c);CREATE INDEXpostgres=# analyze t;ANALYZEpostgres=# explain (costs off) select * from t where c;QUERY PLAN          ——————————-Index Scan using t_c_idx on tIndex Cond: (c = true)Filter: c(3 rows)

postgres=# select relpages from pg_class where relname=’t_c_idx1′;relpages———-5(1 row)В некоторых случаях разница в объеме и производительности может быть весьма существенной.

Сортировка

Если метод доступа возвращает идентификаторы строк в порядке сортировки, это дает оптимизатору дополнительные варианты выполнения запроса.

postgres=# set enable_indexscan=on;SETpostgres=# explain (costs off) select * from t order by a;QUERY PLAN          ——————————-Index Scan using t_a_idx on t(1 row)Из всех методов доступа только btree умеет возвращать данные в отсортированном виде, так что отложим более подробный разговор до рассмотрения этого типа индекса.

Параллельное построение

Обычно построение индекса требует установки блокировки типа SHARE на таблицу. Такая блокировка позволяет читать данные из таблицы, но запрещает любые изменения, пока строится индекс.

postgres=# select mode, granted from pg_locks where relation = ‘t’::regclass;mode    | granted———– ———ShareLock | t(1 row)Если таблица достаточно большая и активно используется в режиме вставки, обновления или удаления, это может оказаться недопустимым — изменяющие сеансы будут ожидать освобождения блокировки длительное время.

postgres=# create index concurrently on t(a);CREATE INDEXТакая команда устанавливает блокировку типа SHARE UPDATE EXCLUSIVE, которая разрешает и чтение, и изменение данных (запрещается только изменение структуры таблицы, а также одновременное выполнение очистки, анализа, или построения другого индекса на той же таблице).

Однако есть и обратная сторона. Во-первых, индекс будет строиться медленнее, чем обычно, поскольку вместо одного прохода по таблице выполняется два, а еще необходимо дожидаться завершения параллельных транзакций, изменяющих данные.

postgres=# select indexrelid::regclass index_name, indrelid::regclass table_name from pg_index where not indisvalid;index_name | table_name———— ————t_a_idx    | t(1 row)Продолжение.

Ввод и проверка данных. Навигация по таблице.

https://www.youtube.com/watch?v=ytadvertiseru

До сих пор мы обсуждали все вопросы, связанные с созданием таблиц, определением и изменением их структуры. Эти изменения выполняются в режиме Конструктора таблиц. Теперь мы переходим к описанию возможностей, которые предоставляет Access при работе с таблицами в режиме Таблицы. Этот режим связан с вводом и обновлением данных в таблице.

Для перемещения по записям используются кнопки, расположенные на нижней границе окна таблицы (рис. 2.33). Описание этих кнопок приведено в табл. 2.13.

Рис. 2.31. Кнопки навигации в окне таблицы

Таблица 2.13. Кнопки перемещения по записям таблицы.

КнопкаОписание
|{amp}lt;Перемещение на первую запись таблицы
{amp}lt;Перемещение на предыдущую запись таблицы
{amp}gt;Перемещение на следующую запись таблицы
{amp}gt;|Перемещение на последнюю запись таблицы
{amp}gt;*Создание новой записи

Помимо кнопок перемещения в нижней части окна таблицы расположено поле номера записи, в котором отображается номер текущей записи. Для перехода на запись с известным номером можно ввести номер в поле номера записи и нажать клавишу Enter. Поиск конкретной записи можно также осуществить с помощью полос прокрутки.

Для перемещения по полям записи и между записями в режиме Таблицы можно использовать также сочетания клавиш, показанных в табл. 2.14.

Таблица 2.14. Навигация по таблице с помощью клавиш.

Сочетания клавишДействие
F5Переход в поле номера записи
Tab, Enter или ?Переход к следующему полю
EndПереход к последнему полю в текущей записи
SHIFT Tab или {amp}lt;Переход к предыдущему полю
HomeПереход к первому полю в текущей записи в режиме перемещения
vПереход к текущему полю в следующей записи
CTRL vПереход к текущему полю в последней записи
CTRL ENDПереход к последнему полю в последней записи
^Переход к текущему полю в предыдущей записи
CTRL ^Переход к текущему полю в первой записи
CTRL HomeПереход к первому полю в первой записи
Page DownНа один экран вниз
Page UpНа один экран вверх
CTRL Page DownНа один экран вправо
CTRL Page UpНа один экран влево

Рис. 2.32. Выбор панели инструментов Формат (режим таблицы)

Рис. 2.33. Список полей на панели инструментов Формат (режим таблицы)

Перемещение в режиме Таблицы по полям в длинных записях можно осуществлять с помощью поля со списком Перейти к полю (Go to Field) панели инструментов Формат (режим таблицы) (Formatting (Datasheet)). Чтобы отобразить на экране эту панель инструментов:

  1. В главном меню выберите команду Вид › Панели инструментов (View › Toolbars).
  2. Установите флажок для панели Формат (режим таблицы) (Formatting (Datasheet)) (рис. 2.32).

https://www.youtube.com/watch?v=playlist

Поле со списком Переход к полю (Go to Field) расположено слева на панели инструментов Формат (режим таблицы) (Formatting (Datasheet)). Для перехода на нужное поле в текущей записи необходимо выбрать в раскрывающемся списке его имя (рис. 2.33). Остальные элементы панели форматирования можно использовать для изменения шрифта, цвета, линий сетки в текущей таблице.

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *