How to Create a Btree Index in PostgreSQL: Syntax and Examples

In PostgreSQL, you create a Btree index using the CREATE INDEX statement with the default index type, which is Btree. The syntax is CREATE INDEX index_name ON table_name USING btree (column_name); to speed up queries on that column.

Syntax

The basic syntax to create a Btree index in PostgreSQL is:

• CREATE INDEX: Command to create an index.
• index_name: Name you give to the index.
• ON table_name: Specifies the table to index.
• USING btree: Specifies the Btree index type (default).
• (column_name): The column(s) to index.

sql

CREATE INDEX index_name ON table_name USING btree (column_name);

Example

This example creates a Btree index on the last_name column of the employees table. It helps speed up searches filtering by last name.

sql

CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  department VARCHAR(50)
);

CREATE INDEX idx_employees_last_name ON employees USING btree (last_name);

-- To check the index exists:
\d employees

Output

                                     Table "public.employees"
 Column    |         Type          | Collation | Nullable |              Default              
-----------+-----------------------+-----------+----------+----------------------------------
 id        | integer               |           | not null | nextval('employees_id_seq'::regclass)
 first_name| character varying(50) |           |          | 
 last_name | character varying(50) |           |          | 
 department| character varying(50) |           |          | 
Indexes:
    "employees_pkey" PRIMARY KEY, btree (id)
    "idx_employees_last_name" btree (last_name)

Common Pitfalls

Common mistakes when creating Btree indexes include:

• Not specifying the USING btree explicitly is fine since Btree is default, but omitting it can confuse beginners.
• Creating indexes on columns with low cardinality (few unique values) may not improve performance.
• For multi-column indexes, order matters; the first column is most important for query filtering.
• For text columns, beware of case sensitivity; Btree indexes are case-sensitive by default.

sql

/* Wrong: Creating index on low-cardinality boolean column might not help */
CREATE INDEX idx_active ON users (is_active);

/* Right: Create index on a column with many unique values */
CREATE INDEX idx_email ON users (email);

Quick Reference

Command Part	Description
CREATE INDEX	Starts the index creation command
index_name	Name you assign to the index
ON table_name	Table where the index is created
USING btree	Specifies Btree index type (default)
(column_name)	Column(s) to be indexed

Key Takeaways

Use CREATE INDEX with USING btree to create a Btree index in PostgreSQL.

Btree is the default index type, so USING btree is optional but explicit.

Choose columns with many unique values for indexing to improve query speed.

Order matters in multi-column indexes; put the most filtered column first.

Avoid indexing low-cardinality columns as it may not improve performance.