部分索引与表达式索引
学习针对部分行或表达式结果创建索引，以实现有针对性的优化。
部分索引与表达式索引 是 CoddyKit 上的免费 PostgreSQL Performance & Query Optimization 课时。 这是第 2 节课，共 4 节。 你可以在下方免费阅读本课时的完整内容 — 然后在浏览器中使用内置代码编辑器和全天候 AI 导师进行实践。 这是 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, andDELETEoperations on the indexed table. - Reduced Bloat: Can significantly reduce index bloat on tables with frequently updated rows that don't satisfy the index's
WHEREclause.
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)orUPPER(column)to speed up queries likeWHERE 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
WHEREclause, 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.
常见问题解答
「部分索引与表达式索引」课时是免费的吗？
是的 — 「部分索引与表达式索引」的完整文本可在网页上免费阅读。要进行交互式练习（内置代码编辑器和全天候 AI 导师）并解锁 PostgreSQL Performance & Query Optimization 课程的其余内容，请升级到 CoddyKit PRO。 PostgreSQL Performance & Query Optimization 课程共包含 4 节课。
「部分索引与表达式索引」这节课中我会学到什么？
学习针对部分行或表达式结果创建索引，以实现有针对性的优化。 你通过在浏览器中直接运行的动手代码来练习 PostgreSQL Performance & Query Optimization，全天候 AI 导师会在你学习这节课的过程中回答你的问题。
学习 PostgreSQL Performance & Query Optimization 需要有经验吗？
无需任何先前经验。CoddyKit 上的 PostgreSQL Performance & Query Optimization 课程适合初学者到高级学习者，你可以从这里开始或从头开始，按照自己的节奏学习。 这是第 2 节课，共 4 节。
「部分索引与表达式索引」课时需要多长时间？
大多数 CoddyKit 课程大约需要 5–10 分钟。每节课都很精短且互动，所以你能稳步进步，并在网页和应用中从离开的地方继续。
我能在这节 PostgreSQL Performance & Query Optimization 课中编写并运行代码吗？
能。每节 PostgreSQL Performance & Query Optimization 课都包含内置代码编辑器，你可以在浏览器中直接编写并运行真实代码，并获得即时 AI 反馈 — 无需本地设置。