Пошук уроків, статей та іншого контенту
Навчитеся індексувати результати виразів і функцій, зокрема регістрозалежний пошук та обчислювані значення.
Звичайний індекс зберігає значення одного або кількох стовпців:
CREATE INDEX users_email_idx ON users (email);Такий індекс ефективний для запитів на кшталт:
SELECT *
FROM users
WHERE email = 'Alice@example.com';Але він не допоможе напряму для запиту:
SELECT *
FROM users
WHERE lower(email) = 'alice@example.com';У цьому випадку PostgreSQL спочатку має застосувати lower() до значень стовпця. Індекс для необробленого email не містить результатів цієї функції.
Індекс за виразом зберігає результат обчислення виразу:
CREATE INDEX users_lower_email_idx
ON users (lower(email));Тепер PostgreSQL може використовувати індекс для пошуку за lower(email).
Індекс за виразом іноді називають функціональним індексом, якщо вираз містить функцію.
Розглянемо таблицю користувачів:
CREATE TABLE users (
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
email text NOT NULL,
display_name text NOT NULL
);Додамо тестові дані:
INSERT INTO users (email, display_name)
SELECT
format('User_%s@example.com', number),
format('Користувач %s', number)
FROM generate_series(1, 20000) AS numbers(number);Створимо індекс для результату lower(email):
CREATE INDEX users_lower_email_idx
ON users (lower(email));Після створення індексу бажано оновити статистику таблиці:
ANALYZE users;Тепер запит може використовувати індекс:
SELECT id, email, display_name
FROM users
WHERE lower(email) = 'user_15000@example.com';Умова пошуку приводить значення стовпця до нижнього регістру так само, як це робиться під час побудови індексу.
Перевірити план виконання можна за допомогою EXPLAIN:
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, email, display_name
FROM users
WHERE lower(email) = 'user_15000@example.com';У плані можна очікувати використання users_lower_email_idx, наприклад через Index Scan або Bitmap Index Scan.
Виклик функції можна застосувати і до параметра:
PREPARE find_user(text) AS
SELECT id, email, display_name
FROM users
WHERE lower(email) = lower($1);
EXECUTE find_user('USER_15000@EXAMPLE.COM');У такому разі обидві сторони порівняння приводяться до нижнього регістру.
Індекс за виразом не обмежується функціями. Він може містити арифметичний або інший обчислюваний вираз.
Створимо таблицю замовлень:
CREATE TABLE orders (
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
price numeric(10, 2) NOT NULL,
quantity integer NOT NULL
);Додамо замовлення:
INSERT INTO orders (price, quantity)
SELECT
(number % 1000 + 1)::numeric(10, 2),
number % 10 + 1
FROM generate_series(1, 20000) AS numbers(number);Загальна вартість замовлення обчислюється так:
price * quantityДля індексації цього значення потрібно взяти весь вираз у додаткові дужки:
CREATE INDEX orders_total_idx
ON orders ((price * quantity));Тепер індекс може бути використаний для пошуку замовлень за загальною вартістю:
ANALYZE orders;
SELECT id, price, quantity
FROM orders
WHERE price * quantity > 5000;Він також може допомогти для сортування за цим значенням:
SELECT id, price, quantity
FROM orders
ORDER BY price * quantity DESC
LIMIT 10;Вираз у запиті має відповідати виразу в індексі. Наприклад, індекс для price * quantity не обов’язково буде використаний для виразу:
quantity * priceМатематично ці вирази рівнозначні, але PostgreSQL не в усіх випадках перетворює їх до однакової форми під час пошуку відповідного індексу.
Індекс за виразом може бути унікальним. Це корисно, коли потрібно заборонити дублікати після перетворення значення.
Наприклад, щоб не дозволяти два email, які відрізняються лише регістром:
CREATE UNIQUE INDEX users_unique_lower_email_idx
ON users (lower(email));Після цього PostgreSQL вважатиме однаковими такі значення:
Alice@example.com
alice@example.com
ALICE@EXAMPLE.COMВставка другого еквівалентного значення завершиться помилкою порушення унікальності.
Перед створенням унікального індексу потрібно переконатися, що в таблиці вже немає конфліктних значень. Інакше команда створення індексу завершиться помилкою.
Функції, використані в індексі, мають бути незмінними — IMMUTABLE.
Незмінна функція для однакових аргументів завжди повертає однаковий результат, незалежно від поточного часу, налаштувань сесії або інших зовнішніх даних.
Наприклад, lower(email) зазвичай підходить для індексу:
CREATE INDEX users_lower_email_idx
ON users (lower(email));Функція, яка залежить від поточного часу, для індексу не підходить:
-- Такий індекс створити не можна:
CREATE INDEX events_current_time_idx
ON events (now());Значення індексу обчислюються під час вставки або оновлення рядка. PostgreSQL не може коректно підтримувати індекс, якщо результат функції для того самого аргументу може змінитися сам по собі.
Власні функції також повинні бути оголошені з правильною категорією незмінності:
CREATE FUNCTION normalize_code(value text)
RETURNS text
LANGUAGE sql
IMMUTABLE
AS $$
SELECT lower(trim(value));
$$;
CREATE INDEX products_normalized_code_idx
ON products (normalize_code(code));Позначати функцію як IMMUTABLE можна лише тоді, коли вона справді завжди повертає однаковий результат для однакових аргументів. Неправильне оголошення може призвести до некоректних результатів пошуку.
Під час створення індексу PostgreSQL:
обчислює вираз для кожного наявного рядка;
зберігає отримане значення в індексі;
повторно обчислює вираз для нових рядків;
оновлює індекс, коли змінюються потрібні стовпці.
Тому індекс за виразом має додаткову вартість:
займає місце на диску;
збільшує час вставки;
збільшує час оновлення відповідних рядків;
потребує обслуговування під час видалення рядків.
Не кожен запит використовуватиме індекс. PostgreSQL може вибрати послідовне сканування, якщо:
таблиця маленька;
умова повертає значну частину рядків;
статистика застаріла;
виконання послідовного сканування оцінене як дешевше.
Саме тому рішення про використання індексу потрібно перевіряти через EXPLAIN або EXPLAIN ANALYZE.
Нижче наведено завершений приклад для локального запуску в PostgreSQL:
DROP TABLE IF EXISTS users;
CREATE TABLE users (
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
email text NOT NULL,
display_name text NOT NULL
);
INSERT INTO users (email, display_name)
SELECT
format('User_%s@example.com', number),
format('Користувач %s', number)
FROM generate_series(1, 20000) AS numbers(number);
CREATE INDEX users_lower_email_idx
ON users (lower(email));
ANALYZE users;
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, email, display_name
FROM users
WHERE lower(email) = 'user_15000@example.com';
SELECT id, email, display_name
FROM users
WHERE lower(email) = lower('USER_15000@EXAMPLE.COM');Для коректного порівняння передбачуваний вираз має бути присутнім у WHERE, ORDER BY або іншій частині запиту, де PostgreSQL може використати індекс.
Індекс для lower(email) не є індексом для email. Запит із умовою:
WHERE email = 'user@example.com'може потребувати окремого індексу на email, якщо звичайний пошук також має бути швидким.
Такий запит не використовує індекс lower(email) так само, як потрібний вираз у стовпці:
WHERE email = lower('USER@EXAMPLE.COM')Тут функція застосована до константи, а не до email. Для індексу за виразом потрібна умова на кшталт:
WHERE lower(email) = lower('USER@EXAMPLE.COM')Для індексу:
CREATE INDEX orders_total_idx
ON orders ((price * quantity));краще використовувати в запитах такий самий вираз:
WHERE price * quantity > 5000Зміна порядку операндів, додавання перетворень типів або використання іншої функції може завадити PostgreSQL зіставити запит з індексом.
На маленьких таблицях послідовне сканування часто швидше за звернення до індексу. Це нормальна поведінка, а не ознака несправності індексу.
Після значних змін у таблиці планувальнику потрібна актуальна статистика:
ANALYZE users;Без неї PostgreSQL може неправильно оцінити кількість рядків і вибрати не найкращий план.
Функції, результат яких залежить від поточного часу або інших змінних умов, не можна безпечно використовувати в індексах. Перед створенням індексу потрібно перевірити, що функція є IMMUTABLE.
Індекс за виразом зберігає результат обчислення, а не лише необроблене значення стовпця.
Він корисний для lower(), арифметичних виразів та інших обчислень у WHERE або ORDER BY.
Вираз у запиті має відповідати виразу, для якого створено індекс.
Функції в індексі повинні бути незмінними — IMMUTABLE.
Унікальний індекс за виразом може забезпечити, наприклад, регістронезалежну унікальність email.
Індекси за виразом прискорюють читання, але збільшують витрати на диск і зміну даних.
Фактичне використання індексу потрібно перевіряти за допомогою EXPLAIN.