Пошук уроків, статей та іншого контенту
З’ясуйте, як PostgreSQL об’єднує результати індексів перед читанням сторінок таблиці.
Під час виконання запиту PostgreSQL може спочатку знайти відповідні записи в одному або кількох індексах, а вже потім прочитати потрібні сторінки таблиці.
Для цього використовуються два пов’язані вузли плану:
Bitmap Index Scan — шукає в індексі адреси знайдених рядків;
Bitmap Heap Scan — за цими адресами читає сторінки самої таблиці.
На відміну від звичайного Index Scan, bitmap-підхід спочатку збирає багато результатів, групує їх за сторінками, а потім читає сторінки ефективнішим порядком.
Рядок таблиці фізично зберігається на сторінці. Індекс містить посилання на рядок, яке називається TID — tuple identifier.
Спрощено алгоритм має такий вигляд:
Bitmap Index Scan знаходить TID рядків, які відповідають умові.
PostgreSQL групує ці TID за сторінками таблиці.
Bitmap Heap Scan читає потрібні сторінки.
Із прочитаних сторінок повертаються відповідні рядки.
Якщо потрібно отримати багато рядків, це може бути ефективніше за послідовне виконання великої кількості випадкових переходів від індексу до таблиці.
Створимо таблицю товарів та індекс:
CREATE TABLE products (
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
category text NOT NULL,
name text NOT NULL,
price numeric(10, 2) NOT NULL,
in_stock boolean NOT NULL
);
INSERT INTO products (category, name, price, in_stock)
SELECT
CASE
WHEN number % 4 = 0 THEN 'books'
WHEN number % 4 = 1 THEN 'electronics'
WHEN number % 4 = 2 THEN 'clothing'
ELSE 'home'
END,
'Product ' || number,
(number % 500) + 10,
number % 3 <> 0
FROM generate_series(1, 100000) AS numbers(number);
CREATE INDEX products_category_idx
ON products (category);
ANALYZE products;
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, name, price
FROM products
WHERE category = 'books';У плані може з’явитися структура на кшталт:
Bitmap Heap Scan on products
Recheck Cond: (category = 'books'::text)
-> Bitmap Index Scan on products_category_idx
Index Cond: (category = 'books'::text)Фактичний план залежить від статистики, розміру таблиці, параметрів PostgreSQL і кількості знайдених рядків. Планувальник не зобов’язаний обирати bitmap-сканування в кожному запуску.
Bitmap Index Scan працює не так, як звичайний Index Scan.
Звичайний Index Scan зазвичай:
знаходить запис в індексі;
одразу переходить до відповідного рядка таблиці;
повторює ці кроки для наступного запису.
Bitmap Index Scan:
проходить індекс;
формує bitmap із позицій знайдених рядків;
передає bitmap вузлу Bitmap Heap Scan.
Це особливо корисно, коли результат містить багато рядків, розташованих на багатьох сторінках таблиці.
Bitmap Heap Scan читає таблицю на основі bitmap, отриманого від індексу.
У плані можна побачити:
Bitmap Heap Scan on products
Recheck Cond: (category = 'books'::text)Recheck Cond означає, що PostgreSQL перевіряє умову під час читання рядків зі сторінок таблиці.
Причини повторної перевірки:
bitmap може зберігати інформацію точно для кожного рядка;
bitmap може бути lossy — лише на рівні сторінок;
умова індексу може потребувати перевірки після отримання рядка з таблиці.
Bitmap має обмежений обсяг пам’яті. Якщо для збереження всіх окремих TID потрібно забагато пам’яті, PostgreSQL може перейти до lossy-представлення.
Exact bitmap зберігає конкретні рядки.
Lossy bitmap зберігає лише сторінки, на яких можуть бути потрібні рядки.
Для lossy-сторінки PostgreSQL читає всі її рядки та додатково перевіряє умову. У плані це може бути видно за такими полями:
Heap Blocks:
exact=120
lossy=35Також EXPLAIN (ANALYZE) може показати:
Rows Removed by Index Recheck: 4200Це означає, що частина рядків була прочитана зі сторінок, але не пройшла повторну перевірку умови.
Розмір пам’яті для операцій із bitmap залежить, зокрема, від параметра work_mem. Збільшення цього параметра іноді зменшує кількість lossy-сторінок, але не гарантує кращого плану для кожного запиту.
Одна з головних переваг bitmap-планів — можливість об’єднати результати кількох індексів.
Для цього PostgreSQL використовує спеціальні вузли:
BitmapAnd — перетин результатів;
BitmapOr — об’єднання результатів.
Розглянемо запит із двома умовами:
CREATE INDEX products_price_idx
ON products (price);
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, name, price
FROM products
WHERE category = 'books'
AND price < 100;План може мати таку структуру:
Bitmap Heap Scan on products
Recheck Cond: ((category = 'books'::text) AND (price < 100))
-> BitmapAnd
-> Bitmap Index Scan on products_category_idx
Index Cond: (category = 'books'::text)
-> Bitmap Index Scan on products_price_idx
Index Cond: (price < 100)Послідовність роботи:
перший Bitmap Index Scan знаходить рядки категорії books;
другий знаходить рядки з ціною меншою за 100;
BitmapAnd залишає лише спільні позиції;
Bitmap Heap Scan читає сторінки таблиці та повертає рядки.
Тобто умова AND реалізується як перетин двох наборів позицій.
Важливо, що PostgreSQL не завжди обиратиме два окремі індекси. Якщо складений індекс або інший план буде дешевшим, планувальник використає саме його.
Для умов із OR PostgreSQL може об’єднати результати індексів через BitmapOr.
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, name, price
FROM products
WHERE category = 'books'
OR price < 20;Можлива структура плану:
Bitmap Heap Scan on products
Recheck Cond: ((category = 'books'::text) OR (price < 20))
-> BitmapOr
-> Bitmap Index Scan on products_category_idx
Index Cond: (category = 'books'::text)
-> Bitmap Index Scan on products_price_idx
Index Cond: (price < 20)У цьому випадку:
перший bitmap містить рядки категорії books;
другий bitmap містить рядки з ціною меншою за 20;
BitmapOr об’єднує обидва набори;
Bitmap Heap Scan читає відповідні сторінки таблиці.
Один і той самий рядок може відповідати обом умовам, але в об’єднаному bitmap він не дублюється.
Планувальник обирає тип доступу за оцінкою вартості.
запит повертає мало рядків;
потрібні рядки розташовані на невеликій кількості сторінок;
індекс може одразу забезпечити потрібний порядок;
потрібно рано отримати перші рядки.
запит повертає помітну частину таблиці;
відповідні рядки розташовані на багатьох сторінках;
потрібно об’єднати кілька індексів;
порядок рядків із таблиці не має значення;
послідовне читання вибраних сторінок дешевше за багато випадкових переходів.
Якщо умова вибирає значну частину таблиці, планувальник може обрати Seq Scan. Це нормально: іноді повне послідовне читання таблиці дешевше, ніж використання індексу.
Для bitmap-плану звертайте увагу на такі частини:
Bitmap Heap Scan on products
Recheck Cond: ...
Heap Blocks: exact=... lossy=...
-> BitmapAnd
-> Bitmap Index Scan ...
-> Bitmap Index Scan ...Index CondПоказує умову, яку PostgreSQL використав під час пошуку в індексі.
Index Cond: (price < 100)Це означає, що індекс products_price_idx використовувався для пошуку потрібних позицій.
Recheck CondПоказує умову, яку PostgreSQL перевіряє під час отримання рядків зі сторінок таблиці.
Recheck Cond: ((category = 'books'::text) AND (price < 100))Heap BlocksПоказує, скільки сторінок таблиці було прочитано в точному або lossy-режимі:
Heap Blocks: exact=240 lossy=12actual time і rowsПри використанні EXPLAIN (ANALYZE) ці значення показують фактичне виконання:
(actual time=0.500..4.200 rows=18000 loops=1)rows у батьківському вузлі показує кількість рядків, які він фактично передав далі.
Bitmap-сканування збирає позиції рядків, а потім зазвичай читає сторінки таблиці у фізично зручнішому порядку. Тому результат не обов’язково буде відсортований так, як записи в індексі.
Наприклад, такий запит не гарантує сортування за price:
SELECT id, name, price
FROM products
WHERE category = 'books';Якщо порядок важливий, його потрібно вказати явно:
SELECT id, name, price
FROM products
WHERE category = 'books'
ORDER BY price;Без ORDER BY порядок рядків не є частиною гарантії результату.
Не кожна умова запиту обов’язково виконується в індексі. Частина умов може залишатися для перевірки після читання таблиці.
Наприклад, індекс може допомагати знайти рядки за category, але інша умова залишиться фільтром:
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, name
FROM products
WHERE category = 'books'
AND in_stock = true;Якщо індексу на in_stock немає, план може містити:
Bitmap Heap Scan on products
Recheck Cond: (category = 'books'::text)
Filter: in_stockУ такій ситуації:
category = 'books' використовується для побудови bitmap;
in_stock = true перевіряється після читання рядків таблиці.
Це відрізняється від Index Cond, оскільки умова Filter не була використана для пошуку позицій в індексі.
Під час дослідження bitmap-плану корисно діяти послідовно:
Запустити запит із EXPLAIN (ANALYZE, BUFFERS).
Перевірити, чи є Bitmap Heap Scan.
Подивитися, який або які Bitmap Index Scan використовуються.
Якщо є BitmapAnd або BitmapOr, визначити, які індекси об’єднуються.
Порівняти Index Cond, Recheck Cond і Filter.
Перевірити значення exact і lossy у Heap Blocks.
Порівняти оцінені та фактичні значення rows.
Використовуйте EXPLAIN (ANALYZE) обережно для запитів, які змінюють дані: такий режим реально виконує запит.
Bitmap Index Scan знаходить позиції рядків, але сам по собі не повертає повні рядки таблиці. Для цього потрібен Bitmap Heap Scan.
Bitmap-план не є універсально найшвидшим. Для кількох рядків звичайний Index Scan може бути ефективнішим, а для великої частини таблиці — Seq Scan.
Велика кількість lossy-сторінок означає, що PostgreSQL читає сторінки та додатково перевіряє багато рядків. Це може збільшувати час виконання.
Recheck Cond і FilterRecheck Cond пов’язаний із повторною перевіркою умови bitmap-доступу.
Filter застосовується до рядків після їх отримання з таблиці.
Bitmap-план не гарантує порядок рядків. Для гарантованого порядку потрібен ORDER BY.
Bitmap Index Scan збирає позиції рядків із індексу.
Bitmap Heap Scan читає сторінки таблиці за зібраним bitmap.
BitmapAnd виконує перетин результатів кількох індексів для умов AND.
BitmapOr об’єднує результати індексів для умов OR.
Bitmap може бути точним або lossy.
Recheck Cond потрібен для повторної перевірки умов під час читання таблиці.
Bitmap Scan часто корисний для запитів, які повертають багато рядків або використовують кілька індексів.
Остаточний вибір між Index Scan, Bitmap Heap Scan і Seq Scan робить планувальник PostgreSQL на основі оцінки вартості.