Индексация

Индексация — это метод ускорения поиска строк в таблице. Индексы создаются на столбцах, которые часто используются в условиях WHERE, JOIN, ORDER BY .

Как работает индексация внутри PostgreSQL?

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

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

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

Создание индекса

-- Создание индекса при создании таблицы
CREATE TABLE sales_data (
    order_id SERIAL PRIMARY KEY,
    customer_id INT,
    amount DECIMAL(10, 2),
    order_date DATE,
    order_quartal DATE,
    INDEX idx_customer_id (customer_id)
);

Выбор столбцов для индексации

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

Обычно это какие-то id и дата, так как по ним чаще всего происходит соединение.

• Столбцы для JOIN: Столбцы, которые используются для соединения таблиц.
• Столбцы для WHERE: Столбцы, по которым часто выполняется фильтрация.
• Столбцы для ORDER BY: Столбцы, по которым часто выполняется сортировка.
• Можно использовать несколько колонок, но только в том случае, если вы часто соединяете таблицы по этим двум колонкам или фильтруете. В остальных случаях - это ненужное усложнение.

Типы индексов в PostgreSQL

Алгоритмы индексации можно использовать 

• B-tree: Стандартный тип индекса, подходит для большинства случаев (равенство, диапазоны, сортировка).
• Hash: Эффективен только для операций равенства.
• GiST: Обобщенное поисковое дерево, используется для геоданных, полнотекстового поиска.
• GIN: Обобщенный инвертированный индекс, эффективен для массивов и JSON.

Можно сказать, что B-tree и Hash - алгоритмы, которые вам понадобятся. B-tree  - более универсальный, Hash - очень эффективный при соединениях с равенством. Остальные используются для 

Использование индексов в запросе

SELECT 
    c.customer_id,
    c.customer_name,  
    SUM(s.amount) AS total_sales
FROM 
    sales_data s
JOIN 
    customers c ON s.customer_id = c.customer_id  
GROUP BY 
    c.customer_id, 
    c.customer_name

customer_id это индекс, JOIN работает быстрее

Индексация — это тоже must have для ускорения запросов :)