PostgreSQL Performance & Query Optimization · Lesson

Creating and Using Indexes

Practical guide on how to create indexes, understand their syntax, and apply them to improve query performance.

Creating and Using Indexes is a free PostgreSQL Performance & Query Optimization lesson on CoddyKit — lesson 2 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the PostgreSQL Performance & Query Optimization learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Welcome to Index Creation

In the last lesson, we learned about B-Tree indexes, the most common type in PostgreSQL. Now, let's get practical! This lesson will guide you through creating, using, and managing indexes to boost your query performance.

You'll learn the syntax, see practical examples, and understand when and how to apply indexes effectively.

The CREATE INDEX Statement

The basic syntax for creating an index is straightforward. You specify the table, the column(s) to index, and optionally the index type (though B-Tree is default and most common).

CREATE INDEX: The command to create a new index.
index_name: A unique name you choose for your index.
table_name: The table on which the index is built.
column_name(s): The column(s) to include in the index.

Simple Index Creation Demo

Let's create a simple products table, insert some data, and then add a single-column index. This setup will be used in subsequent examples.

DROP TABLE IF EXISTS products;

CREATE TABLE products (
  product_id SERIAL PRIMARY KEY,
  product_name VARCHAR(100),
  price DECIMAL(10, 2),
  category VARCHAR(50)
);

INSERT INTO products (product_name, price, category) VALUES
('Laptop', 1200.00, 'Electronics'),
('Mouse', 25.00, 'Electronics'),
('Keyboard', 75.00, 'Electronics'),
('Desk Chair', 150.00, 'Furniture'),
('Monitor', 300.00, 'Electronics'),
('Webcam', 50.00, 'Electronics'),
('Gaming PC', 1500.00, 'Electronics'),
('Office Desk', 250.00, 'Furniture'),
('Bookshelf', 80.00, 'Furniture'),
('Smartwatch', 199.99, 'Wearables');

CREATE INDEX idx_products_category ON products (category);

Verify Your New Index

After creating an index, it's a good practice to confirm it exists. PostgreSQL provides a few ways to inspect your table's structure and its associated indexes.

In the psql command-line client, type \d products to see details for the products table, including its indexes.
Alternatively, you can query system catalog tables like pg_indexes: SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'products';

When Indexes Help Queries

Indexes are most effective when PostgreSQL needs to quickly locate specific rows without scanning the entire table. Think of them like the index in a textbook, pointing directly to relevant pages.

WHERE clauses: Filtering data by indexed columns (e.g., WHERE category = 'Electronics').
ORDER BY clauses: Sorting data by indexed columns.
JOIN conditions: Connecting tables on indexed columns.

Without an index, PostgreSQL might perform a slow "sequential scan" on large tables.

Single-Column Index in Action

Let's use EXPLAIN to see how our idx_products_category index can help a query that filters by the category column. EXPLAIN shows the query plan, revealing if an index is used.

-- Query that benefits from idx_products_category
EXPLAIN SELECT * FROM products WHERE category = 'Electronics';

-- Another example where the index might help with sorting
EXPLAIN SELECT product_name, price FROM products WHERE category = 'Furniture' ORDER BY price DESC;

Composite (Multi-Column) Indexes

Sometimes, your queries filter or sort by *multiple* columns together. In such cases, a composite index (or multi-column index) can be highly beneficial.

It includes more than one column, and the order of columns matters! Place the most frequently used or most selective columns (those with more unique values) first.

Creating a Composite Index

Let's create a composite index on both category and price. This would be useful for queries that filter by category and then further filter or sort by price within that category.

DROP INDEX IF EXISTS idx_products_category_price;
CREATE INDEX idx_products_category_price ON products (category, price);

-- Query benefiting from the composite index
EXPLAIN SELECT product_name, price
FROM products
WHERE category = 'Electronics' AND price > 100
ORDER BY price DESC;

When Not to Index

Indexes aren't always a magic bullet. They come with trade-offs:

Storage space: Indexes consume disk space, potentially a lot for large tables.
Write overhead: Every INSERT, UPDATE, or DELETE on an indexed column requires updating the index, which can slow down write operations.
Over-indexing: Too many indexes can confuse the query planner or increase write overhead, hurting overall performance.

Avoid indexing columns with very few unique values (e.g., a boolean is_active column).

Removing an Index

If an index is no longer needed, or if you've created a better one, you can easily remove it using the DROP INDEX command. This frees up storage space and reduces write overhead on your database.

DROP INDEX IF EXISTS idx_products_category_price;
DROP INDEX IF EXISTS idx_products_category;

-- You can also drop the table if you want to clean up completely
-- DROP TABLE IF EXISTS products;

Indexing Quiz

You've learned how to create and manage indexes. Let's test your understanding!

Recap: Creating & Using Indexes

Fantastic work! In this lesson, you learned the practical steps for creating and managing indexes in PostgreSQL:

We used the CREATE INDEX command to add indexes to tables.
We explored both single-column and composite (multi-column) indexes.
You learned when indexes are beneficial (WHERE, ORDER BY, JOIN) and their trade-offs (write overhead, storage).
Finally, you also learned how to verify and remove indexes using DROP INDEX.

Next, we'll dive deeper into best practices for deciding *which* columns to index and how to avoid common pitfalls!

Frequently asked questions

Is the “Creating and Using Indexes” lesson free?

Yes — the full text of “Creating and Using Indexes” is free to read here on the web, and the PostgreSQL Performance & Query Optimization course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the PostgreSQL Performance & Query Optimization course, upgrade to CoddyKit PRO.

What will I learn in “Creating and Using Indexes”?

Practical guide on how to create indexes, understand their syntax, and apply them to improve query performance. You practise PostgreSQL Performance & Query Optimization with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start PostgreSQL Performance & Query Optimization?

No prior experience is required. PostgreSQL Performance & Query Optimization on CoddyKit is structured for beginners through advanced learners; this is — lesson 2 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Creating and Using Indexes” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this PostgreSQL Performance & Query Optimization lesson?

Yes. Every PostgreSQL Performance & Query Optimization lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

← Back to PostgreSQL Performance & Query Optimization