การสร้างและลบดัชนี

Time to get hands-on. This lesson covers the practical SQL to create, verify, and drop indexes — the everyday tools of index management.

First, let's build a small users table with a few rows to experiment on. Run the code to create and populate it.

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.

Let's index the city column so filtering by city gets much faster as the table grows. Run the command below.

Did it work? PostgreSQL's pg_indexes system view lists every index in your database — query it to inspect what you just built.

Query pg_indexes filtered to the users table to confirm your new index exists. Run it and check the output.

When queries filter on several columns, use a composite index spanning them. Column order matters — match your typical WHERE clause order.

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.

Drop an index when it's unused, slowing writes, or being replaced. The syntax is simple: DROP INDEX index_name.

Let's remove the idx_users_city index by name with DROP INDEX, then verify it's gone with the pg_indexes query.

Consider a table named products with columns product_id, category, and price.

That's index management: CREATE INDEX to add, pg_indexes to verify, DROP INDEX to remove. Next up: more advanced index types.