PostgreSQL Indexes Explained: Improve SQL Query Performance

-

 

Indexes in PostgreSQL help the database find rows more efficiently without scanning an entire table in many cases. They are especially useful when working with large datasets and frequently queried columns.

What Is an Index?

An index is a database structure that helps PostgreSQL locate rows based on indexed column values.

Without a suitable index, PostgreSQL may need to scan many rows to find matching records. With an appropriate index, it may be able to locate matching rows more efficiently.

Indexes are not always beneficial. They consume storage and can slow down inserts, updates, and deletes because the index may also need to be maintained.

1. Create a Table

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100),
    department VARCHAR(50),
    salary NUMERIC(10, 2)
);

A primary key automatically creates a unique index to enforce uniqueness.

2. Create an Index

Suppose queries frequently search employees by department.

CREATE INDEX idx_employees_department
ON employees (department);

This creates a B-tree index by default.

A query that may benefit from this index:

SELECT *
FROM employees
WHERE department = 'IT';

PostgreSQL decides whether to use the index based on the query, table size, data distribution, and estimated costs.

3. Create a Unique Index

A unique index prevents duplicate values in the indexed column or combination of columns.

CREATE UNIQUE INDEX idx_employees_email
ON employees (email);

This example assumes the table contains an email column. The index prevents duplicate non-NULL email values. PostgreSQL permits multiple NULL values by default in a unique index.

4. Create a Composite Index

A composite index includes multiple columns.

CREATE INDEX idx_employees_department_salary
ON employees (department, salary);

This index may help queries such as:

SELECT employee_name, salary
FROM employees
WHERE department = 'IT'
  AND salary > 50000;

Column order matters. A B-tree index on (department, salary) is generally most useful for queries filtering on department, or on both department and salary. It is not equivalent to a separate index on salary.

5. Check Query Performance with EXPLAIN

Use EXPLAIN to inspect the query plan PostgreSQL expects to use.

EXPLAIN
SELECT *
FROM employees
WHERE department = 'IT';

To execute the query and inspect actual timing and row counts:

EXPLAIN ANALYZE
SELECT *
FROM employees
WHERE department = 'IT';

EXPLAIN ANALYZE actually executes the statement. For data-changing statements, take care because the operation is performed unless it is safely rolled back within a transaction.

Look for plan details such as:

  • Sequential Scan
  • Index Scan
  • Bitmap Index Scan
  • Estimated and actual row counts
  • Execution time

A sequential scan is not automatically bad. For a small table or a query returning a large percentage of rows, it may be faster than using an index.

6. List Indexes

You can inspect indexes for a table using PostgreSQL's psql command:

\d employees

Alternatively, query the system catalog:

SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'employees';

7. Drop an Index

If an index is no longer required:

DROP INDEX idx_employees_department;

Only drop an index after checking its purpose and dependencies. An index may support important queries or enforce a uniqueness requirement.

8. When Should You Create an Index?

Indexes are often useful for:

  • Columns frequently used in selective WHERE filters.
  • Columns used in join conditions.
  • Columns used for certain ORDER BY operations.
  • Columns requiring uniqueness.

Avoid creating indexes blindly on every column. Measure query performance before and after making changes.

Common Mistakes to Avoid

  1. Creating too many indexes.
  2. Assuming every query will use an index.
  3. Ignoring composite index column order.
  4. Forgetting that indexes increase storage requirements.
  5. Comparing performance on tiny datasets and assuming the same result for production-sized tables.

Practice Questions

  1. Create an index on department.
  2. Create a composite index on department and salary.
  3. Create a unique index on an email column.
  4. Use EXPLAIN to inspect a filtered query.
  5. Explain why PostgreSQL might choose a sequential scan.
  6. Identify the indexes defined on a table.
  7. Drop an index that is no longer needed.

Interview Questions

1. What is an index in PostgreSQL?

An index is a database structure that can help locate rows efficiently.

2. Does an index always improve query performance?

No. Indexes have storage and maintenance costs, and the query planner may decide that a sequential scan is faster.

3. What is a composite index?

It is an index built on two or more columns.

4. What is the purpose of EXPLAIN ANALYZE?

It executes a query and reports the actual execution plan, row counts, and timing information.

5. What is the difference between a primary key and an index?

A primary key is a constraint that uniquely identifies rows and creates a supporting unique index. An index is a data structure that can support searching, sorting, and constraints.

Conclusion

Indexes are important tools for PostgreSQL performance tuning. Learn to create appropriate indexes, understand execution plans, and measure query performance instead of assuming an index is always faster.

Comments

Post a Comment

Popular posts from this blog

PostgreSQL Tutorial for Beginners: Learn SQL with Practical Examples

-
Introduction PostgreSQL is a powerful, open-source relational database management system used to store, organize, and retrieve data. It is widely used in web applications, business software, reporting systems, and data analytics projects. In this tutorial, you will learn the fundamentals of PostgreSQL through practical examples. You will understand how to create tables, insert records, retrieve data, filter results, and summarize information using SQL queries. This guide is designed for beginners who want to develop practical database skills for software development, data analytics, and other technology-related careers. What You Will Learn By the end of this tutorial, you will understand: What PostgreSQL is and how it works. How to create a database table. How to insert and retrieve records. How to filter and sort data. How to summarize data using aggregate functions. How to practice SQL using a business-related example. What Is PostgreSQL? PostgreSQL is an open-source relational datab...
Read more

PostgreSQL Constraints: PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK and NOT NULL – Practical SQL Guide

-
Constraints are rules applied to table columns to maintain data accuracy and consistency in a PostgreSQL database. They prevent invalid data from being inserted or updated and help maintain relationships between tables. In this tutorial, you will learn the most commonly used PostgreSQL constraints with practical SQL examples. What Are Constraints in PostgreSQL? Constraints define rules that table data must follow. For example: - An employee ID should be unique. - An employee name should not be NULL. - A salary should not be negative. - An order should reference an existing customer. PostgreSQL provides several constraints to enforce these rules. 1. NOT NULL Constraint The NOT NULL constraint ensures that a column cannot contain NULL values. Suppose you have an employees table. CREATE TABLE employees (     employee_id INT,     employee_name VARCHAR(100) NOT NULL,     department VARCHAR(50) ); In this example, employee_name cannot contain NULL values. Insert ...
Read more