PostgreSQL: How to Create Index and Examples

An index in PostgreSQL speeds up data retrieval operations on a table by creating a data structure that allows quick lookup. Indexes are especially useful on columns that are frequently used in WHERE filters or JOIN conditions.

Create a Simple Index

CREATE INDEX idx_column
ON table_name (column_name);

This creates a basic index on column_name in table_name.

Example

CREATE INDEX idx_employee_name
ON employee (name);

With this index in place, queries filtering by name will run faster. You can also create compound indexes:

CREATE INDEX idx_emp_name_role
ON employee (name, role);

Indexes improve lookup performance but can slow down inserts or updates slightly because the index must also be maintained. Adjust based on your workload.