Skip to content
SQLTransactions and Indexesadvanced28 min read

Indexes and Query Performance

Learn how database indexes improve query performance, create single-column and composite indexes, analyze execution plans, and understand the trade-offs between faster reads and additional write overhead.

On This Page (Table of Contents)

In this lesson, you will learn

  • ✓Explain how B-Tree indexes help databases locate rows efficiently
  • ✓Create, inspect, and remove single-column indexes
  • ✓Design composite indexes for queries that filter and sort by multiple columns
  • ✓Interpret EXPLAIN and EXPLAIN ANALYZE output in PostgreSQL
  • ✓Compare sequential scans, index scans, and index-only scans
  • ✓Evaluate index trade-offs involving storage, inserts, updates, and deletes
  • ✓Identify common reasons a query planner may not use an index

Introduction to Database Indexes

A database index is a data structure that helps a database locate rows without examining every row in a table.

Imagine a book with 1,000 pages. Without an index, finding a topic might require checking page after page. With an index, you can look up the topic and go directly to the relevant pages.

A database index serves a similar purpose.

Suppose a customers table contains one million records and your application frequently runs:

SELECT *
FROM customers
WHERE email = 'alex@example.com';

Without a suitable index, PostgreSQL may need to inspect a large portion of the table. With an appropriate index, it may locate the matching row more efficiently.

An index is separate from the table's logical row structure, although the database maintains the index as rows change.

Indexes are especially useful for:
- Filtering rows with WHERE.
- Finding rows through joins.
- Supporting certain ORDER BY operations.
- Enforcing uniqueness through a unique index.
- Supporting queries that repeatedly search on the same columns.

An index does not guarantee that every query becomes faster. PostgreSQL's query planner chooses a plan based on estimated costs, available statistics, and the amount of data expected to be accessed.

How a B-Tree Index Works

PostgreSQL creates a B-Tree index when you use CREATE INDEX without specifying another index method.

A B-Tree organizes indexed values so that the database can navigate through the index instead of checking every indexed entry.

For a well-maintained B-Tree index, locating an entry generally requires logarithmic search work relative to the number of indexed entries. This is why B-Tree searches are often described as O(log N).

However, total query cost is not determined by index lookup complexity alone. PostgreSQL may also need to fetch table rows, perform filtering, sort results, or access many pages.

For example, searching for one email in a very large table may benefit greatly from an index. Reading almost every row in that same table may be faster with a sequential scan.

The correct question is not simply "Does an index exist?" It is "Does using this index make the complete query cheaper?"

Create a Single-Column Index

Use CREATE INDEX to create an index on a table column.

The following PostgreSQL example creates a temporary customers table, inserts sample records, and creates an index on email.

The table is temporary, so it exists only for the current database session. Run the example statements in order in the same session.

Index a Frequently Searched Column

Index a Frequently Searched Column
sql
CREATE TEMP TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT NOT NULL,
    city TEXT NOT NULL
);
 
INSERT INTO customers (customer_id, name, email, city)
VALUES
    (1, 'Alex', 'alex@example.com', 'Lucknow'),
    (2, 'Riya', 'riya@example.com', 'Noida'),
    (3, 'Sam', 'sam@example.com', 'Delhi');
 
CREATE INDEX idx_customers_email
ON customers (email);
 
SELECT customer_id, name, email
FROM customers
WHERE email = 'riya@example.com';
Output
customer_id | name | email -------------+------+------------------- 2 | Riya | riya@example.com (1 row)

The index makes email available as an indexed search key. PostgreSQL may use the index to find Riya's row, depending on its cost estimates. Because this example contains only three rows, PostgreSQL may prefer a sequential scan. That is normal: the example demonstrates index creation and query correctness, not a guaranteed performance improvement. The PRIMARY KEY on customer_id also creates a unique index automatically in PostgreSQL.

Inspect Existing Indexes

PostgreSQL provides catalog views for inspecting indexes.

The following query lists indexes associated with the customers table in the current session's temporary schema.

List Indexes on a Table

List Indexes on a Table
sql
SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'customers'
ORDER BY indexname;
Output
The result includes index definitions for the customers table. Example entries include: - idx_customers_email - The primary-key index on customer_id Exact index names and definitions depend on the database's generated names and the indexes created.

pg_indexes is a PostgreSQL catalog view that exposes index names and definitions. The table must exist in the current database. In a database with multiple schemas, use the schemaname column as an additional filter to identify the intended table.

Composite Indexes: Index Multiple Columns

A composite index, also called a multicolumn index, contains more than one column.

For example:

CREATE INDEX idx_orders_customer_date
ON orders (customer_id, order_date);

This index is useful for queries such as:

SELECT order_id, order_date
FROM orders
WHERE customer_id = 10
ORDER BY order_date DESC;

The index stores entries using customer_id first and order_date second.

Column order matters. A B-Tree index on (customer_id, order_date) is generally well suited to searches that constrain customer_id, including searches that also constrain order_date.

It is not generally equivalent to an index on (order_date, customer_id). A query that filters only by order_date may not be able to use the first index as efficiently.

A useful design principle is to consider:
- Which columns the query filters on.
- Which columns it uses for ordering.
- Whether the query commonly filters on the leading index columns.
- How selective the filters are.
- Whether the index can support multiple important queries.

Do not automatically place every column in one composite index. Large indexes increase storage and maintenance costs.

Composite Index Example

Create an orders table and add an index on customer_id followed by order_date.

The example query returns one customer's orders in date order.

Index Customer Orders by Date

Index Customer Orders by Date
sql
CREATE TEMP TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    order_date DATE NOT NULL,
    total_amount NUMERIC(10, 2) NOT NULL
);
 
INSERT INTO orders (
    order_id,
    customer_id,
    order_date,
    total_amount
)
VALUES
    (101, 10, '2026-10-01', 1200.00),
    (102, 10, '2026-10-05', 800.00),
    (103, 20, '2026-10-03', 1500.00),
    (104, 10, '2026-10-08', 950.00);
 
CREATE INDEX idx_orders_customer_date
ON orders (customer_id, order_date);
 
SELECT order_id, order_date, total_amount
FROM orders
WHERE customer_id = 10
ORDER BY order_date;
Output
order_id | order_date | total_amount ----------+------------+------------- 101 | 2026-10-01 | 1200.00 102 | 2026-10-05 | 800.00 104 | 2026-10-08 | 950.00 (3 rows)

The query filters on customer_id and orders the matching rows by order_date. The composite index matches this access pattern and may allow PostgreSQL to retrieve the relevant entries in the requested order without an additional sort. The planner still decides whether the index is worthwhile. With only four rows, a sequential scan may be cheaper.

The Leftmost Prefix Principle

For a B-Tree index on (customer_id, order_date), the leading column is customer_id.

The index is generally useful for these query patterns:

1. WHERE customer_id = 10
2. WHERE customer_id = 10 AND order_date = DATE '2026-10-05'
3. WHERE customer_id = 10 ORDER BY order_date

A query filtering only on order_date does not benefit from this index in the same straightforward way because the index is organized primarily by customer_id.

PostgreSQL has additional planner capabilities, including skip scans in supported situations, so the rule is a design guideline rather than an absolute statement that later columns can never be used.

The practical lesson is to order composite index columns according to the queries you actually need to support, then inspect execution plans to confirm the result.

EXPLAIN: Inspect the Planned Query

EXPLAIN displays the execution plan PostgreSQL intends to use for a statement.

It can show:
- The scan method, such as Seq Scan or Index Scan.
- Estimated startup and total costs.
- Estimated rows.
- Estimated average row width.
- Join strategies and sort operations for more complex queries.

EXPLAIN normally plans the query without executing the SELECT itself.

The cost values are planner estimates, not milliseconds. They are useful for comparing plan components within PostgreSQL's cost model, but should not be interpreted directly as wall-clock duration.

Read a Query Plan

Read a Query Plan
sql
EXPLAIN
SELECT customer_id, name
FROM customers
WHERE email = 'riya@example.com';
Output
An example plan might contain: Seq Scan on customers Filter: (email = 'riya@example.com') Or it may contain an index-based plan if the planner estimates that it is cheaper. The exact output depends on the PostgreSQL version, table size, statistics, and data distribution.

A sequential scan means PostgreSQL reads the table's pages and applies the filter. An index-based plan may use an index scan and then fetch matching table rows. Neither plan is automatically wrong: the best plan depends on how many rows match and how expensive the required page accesses are. For a tiny table, Seq Scan is often the sensible choice even when an index exists.

EXPLAIN ANALYZE: Measure Actual Execution

EXPLAIN ANALYZE executes the statement and reports actual execution measurements alongside the plan.

For a SELECT query, it reports information such as:
- Actual startup and total time.
- Actual row counts.
- Number of loops.
- Estimated versus actual rows.
- Planning and execution time.

Compare estimated rows with actual rows. Large differences can indicate stale statistics, skewed data, correlated columns, or other estimation difficulties.

Important safety note: EXPLAIN ANALYZE actually runs the statement. For an UPDATE, INSERT, or DELETE, it can modify data. Use a safe test environment or a carefully controlled transaction that you roll back when appropriate.

Even SELECT measurements vary with cache state, concurrency, system load, and data size. Run meaningful tests on representative data rather than treating one measurement as universal.

EXPLAIN ANALYZE Example

The following example measures a SELECT query.

The exact plan and timing are environment-dependent. The example output is illustrative, not a promised result.

Measure a Filtered Query

Measure a Filtered Query
sql
EXPLAIN ANALYZE
SELECT customer_id, name
FROM customers
WHERE email = 'riya@example.com';
Output
Illustrative output: Seq Scan on customers (cost=0.00..1.04 rows=1 width=36) (actual time=0.010..0.012 rows=1 loops=1) Filter: (email = 'riya@example.com') Rows Removed by Filter: 2 Planning Time: 0.120 ms Execution Time: 0.030 ms Actual costs, rows, and timing vary by environment.

This illustrative plan performs a sequential scan and removes two rows that do not match the filter. The example is intentionally small, so a sequential scan may be more efficient than an index scan. On a much larger table with selective filters, the plan may differ. Do not copy illustrative timings into performance claims or compare them across unrelated machines.

Index Scan, Index-Only Scan, and Sequential Scan

PostgreSQL can choose different ways to retrieve rows.

Sequential Scan:
Reads table pages sequentially and checks rows. It can be efficient when a large fraction of a table is needed.

Index Scan:
Uses an index to locate matching entries and then fetches the corresponding table rows. It is often useful for selective searches.

Index-Only Scan:
Retrieves the required values from the index without needing to fetch the corresponding table tuple for every result, when the index contains the needed columns and PostgreSQL's visibility information permits it.

A query that needs columns not present in the index may require heap access even if it uses an index.

Index-only scans are affected by PostgreSQL's visibility map and vacuum activity. Therefore, an index containing every selected column does not guarantee that all heap access can be avoided.

Covering Indexes with INCLUDE

PostgreSQL supports INCLUDE columns in B-Tree indexes.

The indexed columns determine the index's search ordering. Included columns store additional values in the index without becoming part of the search key.

This can help queries that filter on one column and return a small set of other columns. It also increases index size and write-maintenance cost.

Create a Covering Index

Create a Covering Index
sql
CREATE INDEX idx_customers_email_include
ON customers (email)
INCLUDE (customer_id, name);
 
EXPLAIN
SELECT customer_id, name
FROM customers
WHERE email = 'riya@example.com';
Output
PostgreSQL may choose an Index Only Scan if its cost estimates and visibility information make that plan appropriate. The actual plan depends on the table, its statistics, and the database's visibility map.

The index stores email as its search key and includes customer_id and name as additional values. The query may be answerable from the index, but an index-only scan is not guaranteed. Also, because the example table already has an email index, creating this second index may be redundant in a real system unless the covering behavior justifies its additional cost.

Index Trade-Offs: Reads Versus Writes

Indexes can improve read performance, but they are not free.

Benefits:
- Faster lookups for suitable predicates.
- Efficient support for some joins and ordering operations.
- Unique indexes can enforce uniqueness.
- Covering indexes can reduce table access for some queries.

Costs:
- Extra disk storage.
- Additional work during INSERT, UPDATE, and DELETE.
- More index pages to maintain and potentially cache.
- Longer maintenance operations in some workloads.
- Possible performance loss when too many redundant indexes exist.

An UPDATE to an indexed column may require index maintenance. PostgreSQL can sometimes use a HOT update when the necessary conditions are satisfied, avoiding updates to indexes for that row.

Avoid adding an index to every column automatically. Prioritize queries that matter, measure their performance, and assess the cost of maintaining each index.

Create and Remove Indexes Safely

Use DROP INDEX when an index is no longer required.

Use CREATE INDEX CONCURRENTLY for suitable production cases where avoiding the normal blocking behavior of index creation is important. It has operational constraints: it cannot run inside a transaction block, may take longer, and can leave an invalid index after certain failures.

Before removing an index, verify that it is not required for query performance, uniqueness enforcement, or another database requirement.

Manage Indexes

Manage Indexes
sql
-- Create a normal index.
CREATE INDEX idx_customers_city
ON customers (city);
 
-- Inspect index definitions.
SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'customers';
 
-- Remove an index when it is confirmed to be unnecessary.
DROP INDEX idx_customers_city;
Output
The CREATE INDEX statement creates idx_customers_city. The catalog query lists index definitions for the customers table. The DROP INDEX statement removes idx_customers_city if it exists and the operation succeeds.

The statements are intended to be run in order in the same session where the temporary customers table exists. In a production environment, use schema-qualified names where appropriate and check index dependencies and workload impact before dropping an index. For deployments, consider whether CREATE INDEX CONCURRENTLY is appropriate.

Real-World Query Optimization Workflow

Use a repeatable process instead of guessing which index will help.

1. Identify a slow query from application metrics or database monitoring.
2. Record the query, parameters, and expected result.
3. Run EXPLAIN to inspect the planned operations.
4. Run EXPLAIN ANALYZE in a safe environment using representative data.
5. Compare estimated and actual row counts.
6. Look for expensive scans, repeated loops, large sorts, or unnecessary row processing.
7. Consider an index that matches the query's filter, join, or ordering requirements.
8. Re-test after creating the index.
9. Check write performance and storage overhead.
10. Keep the index only if it provides a worthwhile benefit without unacceptable side effects.

An index should solve a measured workload problem. A query that returns half of a table may still be best served by a sequential scan, while a highly selective lookup may benefit substantially from an index.

Common Mistakes

Avoid treating indexes as automatic performance improvements.

The most important mistakes are:
- Creating indexes without measuring the queries they are intended to improve.
- Using the wrong column order in a composite index.
- Assuming EXPLAIN ANALYZE is a non-executing command.
- Interpreting planner cost values as elapsed milliseconds.
- Ignoring write overhead and index storage.
- Assuming the planner must use an index whenever one exists.

Practice Exercises

Exercise 1: Single-column index
Create an index on customers.email. Explain which query pattern it is designed to support.

Exercise 2: Composite index
Create an index on orders(customer_id, order_date). Write a query that filters by customer_id and sorts by order_date.

Exercise 3: Column order
Explain why an index on (customer_id, order_date) is not equivalent to an index on (order_date, customer_id).

Exercise 4: Query plans
Run EXPLAIN on a query that filters customers by email. Identify the scan type and estimated row count.

Exercise 5: Actual execution
Run EXPLAIN ANALYZE on a SELECT query in a safe test environment. Compare the estimated and actual row counts.

Exercise 6: Index trade-offs
Explain why creating an index on every column can harm a write-heavy application.

Exercise 7: Production deployment
Describe why CREATE INDEX CONCURRENTLY may be useful and name one operational constraint.

Exercise 8: Plan selection
Explain why PostgreSQL may choose a sequential scan even when a suitable index exists.

Knowledge Check

Question 1: What is the primary purpose of a database index?

A. To replace every table.
B. To help locate rows more efficiently for suitable queries.
C. To guarantee that all queries are fast.
D. To prevent every invalid business operation.

Answer: B. An index can reduce the work needed to locate rows, depending on the query and data.

Question 2: Which index method is PostgreSQL's default for CREATE INDEX when no method is specified?

A. Hash
B. GIN
C. B-Tree
D. BRIN

Answer: C. B-Tree is the default index method.

Question 3: What does EXPLAIN ANALYZE do?

A. Only displays an unexecuted plan.
B. Executes the statement and reports actual measurements.
C. Automatically creates all missing indexes.
D. Disables query planning.

Answer: B. EXPLAIN ANALYZE executes the statement. Be careful with data-changing commands.

Question 4: Which index best matches a query that filters by customer_id and orders by order_date?

A. An index on (order_date, customer_id) in every case.
B. An index on (customer_id, order_date).
C. An index on an unrelated column.
D. No index can support this pattern.

Answer: B. This column order aligns with the stated filter and ordering pattern, though the planner still evaluates costs.

Question 5: Why can excessive indexes reduce write performance?

A. Each index requires maintenance when relevant table changes occur.
B. Indexes disable transactions.
C. Indexes remove primary keys.
D. Every index forces a sequential scan.

Answer: A. Index maintenance adds work and consumes storage.

Question 6: Why might PostgreSQL choose a sequential scan when an index exists?

A. The planner is forbidden from using indexes.
B. A sequential scan may be cheaper for a small table or a query that reads many rows.
C. Indexes work only on text columns.
D. EXPLAIN disables all indexes permanently.

Answer: B. The planner chooses the plan it estimates will be cheaper for the query.

Question 7: What is the main difference between an index key column and an INCLUDE column in a PostgreSQL covering index?

A. INCLUDE columns determine the B-Tree's search ordering.
B. Key columns support index ordering and search; INCLUDE columns store additional values.
C. INCLUDE columns are never stored on disk.
D. Key columns cannot be filtered.

Answer: B. INCLUDE columns can help cover selected output columns without becoming part of the index search key.

Interview Questions and Answers

Q1. What is a database index?

Answer:
An index is a data structure that helps the database locate rows more efficiently for suitable queries. It consumes additional storage and must be maintained as data changes.

Q2. How does a B-Tree index help query performance?

Answer:
A B-Tree keeps entries organized so the database can navigate to matching values without scanning all indexed entries. Lookup work is often described as O(log N), although the total query cost also depends on row retrieval and other operations.

Q3. What is the difference between a single-column index and a composite index?

Answer:
A single-column index uses one key column. A composite index uses multiple key columns in a defined order. Composite indexes can support query patterns that constrain the leading columns and may also help with sorting.

Q4. Why does composite index column order matter?

Answer:
The index is organized according to its key-column order. An index on (customer_id, order_date) generally supports customer-based searches and ordering by date more directly than date-only searches.

Q5. What is EXPLAIN?

Answer:
EXPLAIN displays PostgreSQL's planned execution strategy, including scan types, estimated costs, and estimated rows. It normally does not execute the SELECT statement itself.

Q6. What is EXPLAIN ANALYZE?

Answer:
EXPLAIN ANALYZE executes the statement and reports actual execution details alongside the plan. Because it executes data-changing statements too, it must be used carefully.

Q7. What is the difference between Index Scan and Index Only Scan?

Answer:
An Index Scan uses index entries to locate rows and may fetch their table tuples. An Index Only Scan can return required values from the index when it contains those values and PostgreSQL's visibility information allows it.

Q8. Can an index make a query slower?

Answer:
The index can add write overhead and storage cost. A query can also perform worse if the index does not suit the access pattern or if the planner's chosen plan is less efficient than an alternative.

Q9. Why might PostgreSQL ignore an index?

Answer:
The table may be small, many rows may match, statistics may influence cost estimates, or a sequential scan may require less work than using the index and fetching many table rows.

Q10. What is a covering index?

Answer:
A covering index contains the columns needed by a query. PostgreSQL's INCLUDE syntax can store additional non-key columns in an index, potentially enabling an index-only scan when visibility conditions also permit it.

Q11. What does CREATE INDEX CONCURRENTLY do?

Answer:
It creates an index using a process designed to reduce blocking of concurrent writes compared with ordinary index creation. It has restrictions, takes additional work, and cannot run inside a transaction block.

Q12. How do you optimize a slow SQL query?

Answer:
Measure the query, inspect EXPLAIN and EXPLAIN ANALYZE, compare estimated and actual row counts, identify expensive operations, consider an index that matches the access pattern, and benchmark again on representative data. Also check the impact on writes and storage.

Lesson Summary

In this lesson, you learned how indexes can improve query performance and why PostgreSQL's query planner does not always choose an index.

Remember:
- B-Tree is PostgreSQL's default index method for CREATE INDEX.
- Single-column indexes support suitable searches on one column.
- Composite index column order should match important query patterns.
- EXPLAIN shows the planned execution strategy.
- EXPLAIN ANALYZE executes the statement and reports actual measurements.
- Sequential scans can be more efficient when a query needs many rows.
- Indexes improve some reads but add storage and write-maintenance costs.
- Performance decisions should be based on measured queries and realistic data.

The goal is not to create the maximum number of indexes. It is to create and maintain the indexes that provide meaningful benefits for the workload.

Common pitfalls to avoid

Real debugging lessons and exact syntax contrasts to avoid runtime bugs

#1 Assuming an index always makes a query faster

Why it happens: Developers focus on lookup complexity without considering table size, the percentage of matching rows, and the cost of fetching table pages.

Incorrect Approach
sql
sql
-- Creating an index does not guarantee that
-- PostgreSQL will use it or that the query will be faster.
CREATE INDEX idx_customers_city
ON customers (city);
Correct Solution
sql
sql
EXPLAIN ANALYZE
SELECT customer_id, name
FROM customers
WHERE city = 'Lucknow';

Key takeaway: Measure the real query using representative data. PostgreSQL may correctly choose a sequential scan when the table is small or many rows match.

#2 Ignoring the order of columns in a composite index

Why it happens: Developers assume that an index containing the required columns is equally useful regardless of their order.

Incorrect Approach
sql
sql
-- This index may not efficiently support
-- queries filtering only by customer_id.
CREATE INDEX idx_orders_date_customer
ON orders (order_date, customer_id);
Correct Solution
sql
sql
-- Better aligned with queries filtering by
-- customer_id and ordering by order_date.
CREATE INDEX idx_orders_customer_date
ON orders (customer_id, order_date);

Key takeaway: Choose column order based on actual filtering, ordering, selectivity, and workload requirements. Validate the design using execution plans.

#3 Treating EXPLAIN ANALYZE as a command that only plans a query

Why it happens: Developers confuse EXPLAIN with EXPLAIN ANALYZE.

Incorrect Approach
sql
sql
-- This executes the UPDATE.
EXPLAIN ANALYZE
UPDATE customers
SET city = 'Noida'
WHERE customer_id = 1;
Correct Solution
sql
sql
-- Inspect the plan without executing the UPDATE.
EXPLAIN
UPDATE customers
SET city = 'Noida'
WHERE customer_id = 1;

Key takeaway: EXPLAIN ANALYZE executes the statement and can change data. Use a safe environment or carefully controlled transaction when measuring data-changing statements.

#4 Creating redundant indexes without checking existing ones

Why it happens: Developers add indexes for each new query without considering indexes that already cover the same workload.

Incorrect Approach
sql
sql
CREATE INDEX idx_customers_email
ON customers (email);
 
-- A second index with the same key may add
-- maintenance cost without meaningful benefit.
CREATE INDEX idx_customers_email_again
ON customers (email);
Correct Solution
sql
sql
-- Inspect existing index definitions first.
SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'customers'
ORDER BY indexname;

Key takeaway: Review existing indexes and measure the workload before adding a new one. Similar indexes may serve different purposes in some designs, so assess their actual use rather than dropping them blindly.

Ready for hands-on challenges?

Test your skills with shuffled coding challenges in the Practice Arena.

Practice Arena →

Key takeaways

  • Indexes help the database find matching rows without necessarily scanning the entire table.
  • PostgreSQL uses B-Tree indexes by default when creating a standard index.
  • Composite index column order matters and should reflect query patterns.
  • EXPLAIN shows the planned execution strategy, while EXPLAIN ANALYZE executes the query and reports actual measurements.
  • An index is not always faster; sequential scans can be more efficient when a large portion of a table must be read.
  • Indexes consume storage and add work to inserts, updates, and deletes.
  • Use realistic data and measured execution plans rather than creating indexes based only on intuition.
Was this lesson helpful?
Found an error? Report an issue