Алексей Голобурдин – Демистификация PostgreSQL-индексов @ PythoNN

Transcript

1. Демистификация PostgreSQL-индексов 1
2. Что обсудим? как работают индексы когда использовать индексы когда не использовать индексы почему мой индекс не ускоряет запрос как создавать эффективные индексы как найти ненужные индексы 2
3. Как PostgreSQL хранит данные таблиц? в файлах (удивительно!) файлы побиты на блоки по 8KB, их называют страницами чтобы удобно считывать и кэшировать в RAM части данных на одной страничке может быть N строк таблицы данные хранятся неупорядоченно — в порядке вставки а после обновления и удаления строк и в ещё более хаотичном порядке новая строка может сохраниться там, где была старая версия строки (PostgreSQL не изменяет строки, а работает с MVCC) 3
4. и как в этом добре искать данные? 4
5. полным считыванием таблицы? 5
6. Полное считывание (SEQ SCAN) — уныло (для больших таблиц) таблица на 10 млн строк, нужны 3 строки, 9 999 997 строк каждый раз считываются с диска зря а чтение с диска медленное запросы медленные всё тупит все грустят деняк нет 6
7. хранить строки таблицы сразу упорядоченно? 7
8. А это как? надо будет копировать тогда все строки на каждой вставке данных, обновлении и удалении данных копировать 10 млн строк на каждой правке данных — уныло, медленные операции изменения 8
9. Хотяяя... для редко меняющихся (справочных) данных можно делать для таблицы CLUSTER по индексу это физически перестраивает таблицу в соответствии с порядком в индексе сие помогает быстрее выполнять выборки с between, например но уже нужен индекс:) CLUSTER разово перестраивает таблицу, порядок рушится со временем 9
10. хранить упорядоченные данные вне таблицы! 10
11. Это и есть индекс данные в таблице хранятся в расколбасе данные в индексе хранятся упорядоченно записи в индексе ссылаются на данные в таблице 11
12. Индекс — избыточная структура данных индекс можно удалить, данные в таблице останутся индекс нужен для ускорения поиска данных в таблице 12
13. B-Tree самый частый вид индекса, по умолчанию индекс использует именно этот тип, хотя есть и другие сбалансированное дерево 13
14. коды аэропортов. Ищем аэропорт LED (Пулково): старт с корневого узла. Нужен ключ, который <= искомого, а следующий за ним ключ был > искомого. Здесь ключ AER идем на второй уровень. Здесь ключ KZN на третьем уровне читаем узел (страницу данных) c KZN, LED, NUX. Здесь LED ссылается уже на таблицу 14
15. B-Tree дерево, ибо похоже, ветки:) сбалансированное, ибо глубина дерева одинакова для всех листовых узлов все пути от корня до листьев имеют одинаковую длину одинаковое время поиска для всех записей 4-5 уровней достаточно для хранения миллиарда записей каждый узел — страница, 8 килобайт в наборе данных на миллиард записей можно найти одну запись, прочитав с диска всего 4 страницы по 8 килобайт — легко и быстро 15
16. А если без индекса? что значит искать в миллиарде строк одну запись без индекса? Это значит, что надо прочитать миллиард строк. Сколько это страниц? например, в таблице хранятся два поля — bigint, 8 байт, и текст на 150 байт, плюс метаданные, итого 185 байт для каждой строки на одной странице помещается 44 строки для хранения миллиарда строк нам нужно 22.7 млн страниц на диске это ровно то число страниц, которое потенциально надо прочитать с диска, чтобы найти в миллиарде строк одну строку без индекса против 4-5 страниц для индекса 16
17. 5 — лучше, чем 22.7 млн! скажите, здорово придумано? вот хитры же бобры, да? вместо того, чтобы читать 22 млн страниц найти способ читать всего пять страниц здорово! 17
18. Актуальность индекс хранит избыточные данные, нннно: PostgreSQL сам следит за актуальностью индекса нам не надо об этом думать уррра! 18
19. -- создаём чудо-табличку с PK create table entity ( entity_id bigint generated always as identity primary key, name varchar(50) ); -- смотрим описание таблицы в psql — есть индекс для PK \d entity Таблица "public.entity" Столбец | Тип | Правило сортировки | Допустимость NULL | По умолчанию -----------+-----------------------+--------------------+-------------------+------------------------------ entity_id | bigint | | not null | generated always as identity name | character varying(50) | | | Индексы: "entity_pkey" PRIMARY KEY, btree (entity_id) -- вставим много данных insert into entity(name) select substring(md5(random()::text) || md5(random()::text) from 1 for (floor(random() * 46)::int + 5)) from generate_series(1, 10000000); 19
20. -- создаём ещё один индекс create index idx_entity_name on entity(name); -- смотрим описание таблицы в psql — добавился индекс \d entity Таблица "public.entity" Столбец | Тип | Правило сортировки | Допустимость NULL | По умолчанию -----------+-----------------------+--------------------+-------------------+------------------------------ entity_id | bigint | | not null | generated always as identity name | character varying(50) | | | Индексы: "entity_pkey" PRIMARY KEY, btree (entity_id) "idx_entity_name" btree (name) 20
21. -- сколько занимает места таблица? select pg_size_pretty(pg_relation_size('entity')); pg_size_pretty ---------------- 653 MB (1 строка) -- сколько занимает места индекс? select pg_size_pretty(pg_relation_size('idx_entity_name')); pg_size_pretty ---------------- 475 MB (1 строка) 21
22. индекс всегда используется после создания? 22
23. нннет! 23
24. как проверить, что запрос использует индекс? 24
25. Индекс не используется drop index idx_entity_name; entity Таблица "public.entity" Столбец | Тип | Правило сортировки | Допустимость NULL | По умолчанию -----------+-----------------------+--------------------+-------------------+------------------------------ entity_id | bigint | | not null | generated always as identity name | character varying(50) | | | Индексы: "entity_pkey" PRIMARY KEY, btree (entity_id) -- SEQ SCAN, последовательно сканируется вся таблица explain (analyze, buffers) select entity_id, name from entity where name='aaa'; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------- Gather (cost=1000.00..136625.43 rows=1 width=36) (actual time=540.293..551.459 rows=0 loops=1) ... Buffers: shared hit=12438 read=71104 -> Parallel Seq Scan on entity (cost=0.00..135625.33 rows=1 width=36) (actual time=508.218..508.219 rows=0 loops=3) ... (16 строк) 25
26. Индекс используется create index idx_entity_name on entity(name); -- индекс используется — INDEX SCAN explain (analyze, buffers) select entity_id, name from entity where name='aaa'; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------- Index Scan using idx_entity_name on entity (cost=0.56..8.58 rows=1 width=36) (actual time=0.081..0.081 rows=0 loops=1) Index Cond: ((name)::text = 'aaa'::text) Buffers: shared read=4 Planning: Buffers: shared hit=111 read=3 Planning Time: 1.046 ms Execution Time: 0.132 ms (7 строк) 26
27. Индекс есть, но не используется? Как так?! create table client ( client_id bigint generated always as identity primary key, first_name varchar(50) not null, last_name varchar(50) not null, sex char(1) not null check (sex in ('m', 'f')) ); -- вставляем половину мужчин, половину женщин -- создаём индекс и собираем статистику create index client_sex_idx on client(sex); vacuum analyze client; explain analyze select * from client where sex='f'; -- seq scan explain analyze select * from client where sex='m'; -- seq scan 27
28. Селективность если надо достать немного записей из большой таблицы — то это запрос с высокой селективностью и такой запрос будет использовать индекс если надо достать всю маленькую табличку или много данных большой таблицы — запрос с низкой селективностью и такой запрос не будет использовать индекс а зачем? Он не даст ускорения проще сразу пойти лопатить SEQ SCAN 28
29. Важно! надо (!) создавать индексы для запросов с высокой селективностью не надо (!) создавать индексы для запросов с низкой селективностью (незачем) 29
30. Индекс на несколько колонок можно! порядок колонок в индексе важен! create index idx_entity_name_address on entity(name, address); explain analyze select * from entity where name='aaa'; -- наш индекс используется explain analyze select * from entity where name='aaa' and address='bbb'; -- наш индекс используется explain analyze select * from entity where address='bbb'; -- наш индекс НЕ используется 30
31. Индекс на уникальность надо создавать, когда он нужен как ограничение для данных чтобы не вставляли дубликаты значений в колонку или колонки работает и для ускорения поиска (при высокой селективности) и как ограничение create temp table some_uniq_value ( some_uniq_value_id bigint generated always as identity primary key, value varchar(30) unique ); -- или так create unique index unique_index_name on table_name (column1); -- или так, на несколько колонок create unique index unique_index_name on table_name (column1, column2); 31
32. Индекс по выражению create table entity ( entity_id bigint generated always as identity primary key, name varchar(50) ); explain analyze select * from entity where name='abcdefbzz'; -- индекс используется explain analyze select * from entity where lower(name)='abcdefbzz'; -- индекс не используется -- потому что в индексе хранятся значения колонки name, а не значения lower(name) -- бахнем индекс по выражению! drop index idx_entity_name; create index idx_entity_name on entity(lower(name)); explain analyze select * from entity where lower(name)='abcdefbzz'; -- индекс используется! 32
33. Внимание очень часто индекс на колонке с DATE или TIMESTAMP, а все выборки по функциям от колонки в таких случаях индекс бесполезен:) занимает место, ресурсы на поддержание, но не используется в запросах 33
34. Обычный vs частичный индекс частичный индекс — крутая штука, индекс небольшой и очень эффективный create table client ( client_id bigint generated always as identity primary key, name varchar(50) not null, phone varchar(12) ); -- телефоны есть только у небольшой части клиентов create index client_phone_idx on client(phone); -- индекс весит 68 MB explain analyze select * from client where phone='1000057666'; -- этот индекс используется 34
35. Заменим индекс на частичный drop index client_phone_idx; create index client_phone_idx on client(phone) where phone is not null; -- 3 MB вместо 68 MB! explain analyze select * from client where phone='1827879406'; -- по-прежнему используется 35
36. Покрывающий индекс можно не ходить в таблицу за данными, если все необходимые данные есть в самом индексе в EXPLAIN будет видна операция INDEX ONLY SCAN create index client_last_name_idx on client(last_name); explain analyze select last_name from client where last_name='...'; -- Index Only Scan, cost up to 4.58 explain analyze select last_name, first_name from client where last_name='...'; -- Index Scan, cost up to 8.58 -- создадим покрывающий индекс create index client_last_name_idx on client(last_name) include (first_name); explain analyze select last_name, first_name from client where last_name='...'; -- Index Only Scan, cost up to 4.58 36
37. Конкурентное создание индекса -- чтобы не блокировались транзакции при создании индекса create index concurrently idx_entity_name_address on entity(name, address); но надо проверить, что статус индекса после создания нормальный, не invalid (в \d , например) если invalid — просто пересоздать индекс снова 37
38. Находим ненужные индексы select schemaname, relname, indexrelname, idx_scan from pg_stat_user_indexes where idx_scan = 0; schemaname | relname | indexrelname | idx_scan ------------+-----------------+----------------------+---------- public | client | client_sex_idx | 0 некоторые индексы могут использоваться исключительно для обеспечения ограничений (например, для уникальности), такие индексы могут не участвовать в обычных операциях выборки, поэтому их значение idx_scan может быть равно нулю, несмотря на их важность. Такие индексы удалять не надо! 38
39. Аналитика после изменения данных полезно вручную запустить сбор аналитики и vacuum для сбора статистики неверная статистика по данным — неверные планы выполнения запросов VACUUM ANALYZE; 39
40. Спасибы! https://t.me/t0digital 40