创建和删除索引
学习用于创建、验证和删除索引的 SQL 命令，以及管理索引的最佳实践。
创建和删除索引 是 CoddyKit 上的免费 Advanced PostgreSQL: Indexing, Partitioning, Replication 课时。 这是第 3 节课，共 4 节。 你可以在下方免费阅读本课时的完整内容 — 然后在浏览器中使用内置代码编辑器和全天候 AI 导师进行实践。 这是 Advanced PostgreSQL: Indexing, Partitioning, Replication 学习路径的一部分，你的进度在网页和 CoddyKit 应用中同步。 Advanced PostgreSQL: Indexing, Partitioning, Replication 课程共包含 4 节课。
本课时的部分内容尚未翻译，以英文显示。
Index Management: The Basics
Time to get hands-on. This lesson covers the practical SQL to create, verify, and drop indexes — the everyday tools of index management.
Setting Up Our Table
First, let's build a small users table with a few rows to experiment on. Run the code to create and populate it.
CREATE TABLE users (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(100) UNIQUE,
city VARCHAR(100)
);
INSERT INTO users (name, email, city) VALUES
('Alice Smith', 'alice@example.com', 'New York'),
('Bob Johnson', 'bob@example.com', 'Los Angeles'),
('Charlie Brown', 'charlie@example.com', 'New York'),
('Diana Prince', 'diana@example.com', 'London');The CREATE INDEX Command
CREATE INDEX name ON table (column) builds an index to speed up queries. PostgreSQL makes it a B-tree by default — no USING BTREE needed.
Creating Your First Index
Let's index the city column so filtering by city gets much faster as the table grows. Run the command below.
CREATE INDEX idx_users_city ON users (city);Verifying Existing Indexes
Did it work? PostgreSQL's pg_indexes system view lists every index in your database — query it to inspect what you just built.
Example: Listing Indexes
Query pg_indexes filtered to the users table to confirm your new index exists. Run it and check the output.
SELECT indexname, tablename, indexdef
FROM pg_indexes
WHERE tablename = 'users';
Composite Indexes
When queries filter on several columns, use a composite index spanning them. Column order matters — match your typical WHERE clause order.
Creating a Composite Index
Here we build a composite index on city and name — handy when you often search by both at once. Then re-run the pg_indexes query.
CREATE INDEX idx_users_city_name ON users (city, name);The DROP INDEX Command
Drop an index when it's unused, slowing writes, or being replaced. The syntax is simple: DROP INDEX index_name.
Example: Dropping an Index
Let's remove the idx_users_city index by name with DROP INDEX, then verify it's gone with the pg_indexes query.
DROP INDEX idx_users_city;Quick Check: Index Commands
Consider a table named products with columns product_id, category, and price.
Recap: Index Management
That's index management: CREATE INDEX to add, pg_indexes to verify, DROP INDEX to remove. Next up: more advanced index types.
常见问题解答
「创建和删除索引」课时是免费的吗？
是的 — 「创建和删除索引」的完整文本可在网页上免费阅读。要进行交互式练习（内置代码编辑器和全天候 AI 导师）并解锁 Advanced PostgreSQL: Indexing, Partitioning, Replication 课程的其余内容，请升级到 CoddyKit PRO。 Advanced PostgreSQL: Indexing, Partitioning, Replication 课程共包含 4 节课。
「创建和删除索引」这节课中我会学到什么？
学习用于创建、验证和删除索引的 SQL 命令，以及管理索引的最佳实践。 你通过在浏览器中直接运行的动手代码来练习 Advanced PostgreSQL: Indexing, Partitioning, Replication，全天候 AI 导师会在你学习这节课的过程中回答你的问题。
学习 Advanced PostgreSQL: Indexing, Partitioning, Replication 需要有经验吗？
无需任何先前经验。CoddyKit 上的 Advanced PostgreSQL: Indexing, Partitioning, Replication 课程适合初学者到高级学习者，你可以从这里开始或从头开始，按照自己的节奏学习。 这是第 3 节课，共 4 节。
「创建和删除索引」课时需要多长时间？
大多数 CoddyKit 课程大约需要 5–10 分钟。每节课都很精短且互动，所以你能稳步进步，并在网页和应用中从离开的地方继续。
我能在这节 Advanced PostgreSQL: Indexing, Partitioning, Replication 课中编写并运行代码吗？
能。每节 Advanced PostgreSQL: Indexing, Partitioning, Replication 课都包含内置代码编辑器，你可以在浏览器中直接编写并运行真实代码，并获得即时 AI 反馈 — 无需本地设置。