PostgreSQL Performance & Query Optimization · Урок

Частичные индексы и индексы выражений

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

«Частичные индексы и индексы выражений» — бесплатный урок PostgreSQL Performance & Query Optimization на CoddyKit. Это урок 2 из 4. Ты можешь прочитать весь урок бесплатно ниже — а потом практиковать его прямо в браузере с встроенным редактором кода и ИИ-репетитором 24/7. Это часть пути обучения PostgreSQL Performance & Query Optimization, и твой прогресс синхронизируется между веб-версией и приложением CoddyKit. Курс PostgreSQL Performance & Query Optimization содержит 4 уроков всего.

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

Intro to Targeted Indexes

Welcome! In this lesson, we'll dive into two powerful, specialized index types in PostgreSQL: Partial Indexes and Expression Indexes.

These indexes allow for highly targeted optimization, focusing on specific subsets of data or the results of calculations, rather than entire columns.

Focus with Partial Indexes

A Partial Index is an index that covers only a portion of the rows in a table. You define this subset using a WHERE clause during index creation.

Think of it as filtering your index. Only rows that satisfy the WHERE condition will be included in the index structure.

Benefits of Partial Indexes

Why use a partial index?

Smaller Size: They take up less disk space and memory compared to full indexes.
Faster Updates: Less data to maintain means faster INSERT, UPDATE, and DELETE operations on the indexed table.
Reduced Bloat: Can significantly reduce index bloat on tables with frequently updated rows that don't satisfy the index's WHERE clause.

They shine when a small subset of rows is queried very often, like 'active' users or 'pending' orders.

Creating Partial Indexes

The syntax for a partial index is straightforward. You simply add a WHERE clause to your standard CREATE INDEX statement.

The condition in the WHERE clause must match the condition used in your queries for the index to be effective.

CREATE INDEX index_name
ON table_name (column_name)
WHERE condition;

Partial Index in Action

Let's see a partial index in action. We'll create an index on order_date specifically for orders with a status of 'pending'. This is common for e-commerce where 'pending' orders need quick attention.

Try running the code to see how it works:

CREATE TABLE orders (
  order_id SERIAL PRIMARY KEY,
  customer_id INT,
  order_date DATE,
  status VARCHAR(20)
);

INSERT INTO orders (customer_id, order_date, status) VALUES
(101, '2023-01-15', 'completed'),
(102, '2023-01-16', 'pending'),
(103, '2023-01-17', 'completed'),
(104, '2023-01-18', 'pending'),
(105, '2023-01-19', 'completed'),
(106, '2023-01-20', 'completed'),
(107, '2023-01-21', 'pending');

CREATE INDEX idx_pending_orders_date
ON orders (order_date)
WHERE status = 'pending';

EXPLAIN ANALYZE SELECT order_id, order_date
FROM orders
WHERE status = 'pending' AND order_date > '2023-01-01';

Indexing Expressions

An Expression Index (also known as a Function-Based Index) indexes the result of a function or expression, rather than just the raw column value.

This is incredibly useful when your queries frequently use functions on columns, like converting text to lowercase for case-insensitive searches.

Power of Expression Indexes

Expression indexes offer great flexibility:

Case-Insensitive Search: Index LOWER(column) or UPPER(column) to speed up queries like WHERE LOWER(column) = 'value'.
Date/Time Manipulation: Index DATE_TRUNC('month', timestamp_column) to optimize queries grouped or filtered by month.
Complex Computations: Index on mathematical results or custom functions if they are part of frequent query conditions.

Without an expression index, PostgreSQL would have to compute the function for every row during a scan, making it slow.

Creating Expression Indexes

To create an expression index, you simply replace the column name in your CREATE INDEX statement with the desired function or expression.

The important rule is that the expression in your query's WHERE clause must exactly match the expression used in the index definition for the index to be used.

CREATE INDEX index_name
ON table_name (expression);

Expression Index Example

Let's create an expression index to enable fast, case-insensitive searches on email addresses. This is a very common use case.

Notice how the EXPLAIN ANALYZE output should show an 'Index Scan' using our new idx_lower_email index.

CREATE TABLE users (
  user_id SERIAL PRIMARY KEY,
  username VARCHAR(50),
  email VARCHAR(100)
);

INSERT INTO users (username, email) VALUES
('Alice', 'alice@example.com'),
('Bob', 'BOB@example.com'),
('Charlie', 'Charlie@example.com'),
('David', 'david@example.com');

CREATE INDEX idx_lower_email ON users (LOWER(email));

EXPLAIN ANALYZE SELECT user_id, username
FROM users
WHERE LOWER(email) = 'bob@example.com';

-- This query would NOT use the index:
-- EXPLAIN ANALYZE SELECT user_id, username
-- FROM users
-- WHERE email = 'bob@example.com';

Apply Your Knowledge

Now that you've learned about Partial and Expression Indexes, let's test your understanding.

Lesson Summary

Great job! You've learned about two powerful advanced indexing techniques:

Partial Indexes: Index only a subset of rows based on a WHERE clause, saving space and speeding up writes.
Expression Indexes: Index the result of a function or expression, optimizing queries that use those functions in their conditions.

By using these targeted indexes, you can significantly improve the performance of specific, critical queries in your PostgreSQL database.

Часто задаваемые вопросы

Урок «Частичные индексы и индексы выражений» бесплатный?

Да — полный текст урока «Частичные индексы и индексы выражений» бесплатно доступен здесь в веб-версии. Чтобы практиковать его интерактивно (встроенный редактор кода и ИИ-репетитор 24/7) и разблокировать остальной курс PostgreSQL Performance & Query Optimization, подпишись на CoddyKit PRO. Курс PostgreSQL Performance & Query Optimization содержит 4 уроков всего.

Чему я научусь в уроке «Частичные индексы и индексы выражений»?

Научитесь создавать индексы для части строк или результата выражения с целью точечной оптимизации. Ты практикуешь PostgreSQL Performance & Query Optimization с помощью реального кода, который запускаешь прямо в браузере, и ИИ-репетитор 24/7 отвечает на твои вопросы во время урока.

Нужен ли мне опыт, чтобы начать PostgreSQL Performance & Query Optimization?

Предыдущий опыт не требуется. PostgreSQL Performance & Query Optimization на CoddyKit структурирован для всех уровней — от новичков до продвинутых, поэтому ты можешь начать отсюда или с самого начала и учиться в своем темпе. Это урок 2 из 4.

Сколько времени занимает урок «Частичные индексы и индексы выражений»?

Большинство уроков CoddyKit занимают около 5–10 минут. Каждый из них компактный и интерактивный, поэтому ты постоянно делаешь прогресс и продолжаешь с того же места в веб-версии и приложении.

Можно ли писать и запускать код в этом уроке PostgreSQL Performance & Query Optimization?

Да. Каждый урок PostgreSQL Performance & Query Optimization включает встроенный редактор кода, поэтому ты пишешь и запускаешь реальный код прямо в браузере и получаешь моментальную обратную связь от AI — локальная установка не требуется.

Все уроки этого курса

Хеш-индексы, GIN и GiST
Частичные индексы и индексы выражений
Покрывающие индексы и сканирование только по индексу
Индексы BRIN для больших последовательных данных

← Назад к PostgreSQL Performance & Query Optimization