B-tree index (default) behavior in PostgreSQL

Introduction

A B-tree index helps the database find data quickly, like an organized list that makes searching faster.

When you want to speed up searching for rows with specific values in a column.

When you need to sort data quickly by a column.

When you want to find data within a range, like dates between two days.

When you want to enforce uniqueness on a column to avoid duplicates.

Syntax

PostgreSQL

CREATE INDEX index_name ON table_name USING btree (column_name);

B-tree is the default index type in PostgreSQL, so you can omit 'USING btree'.

You can create indexes on one or more columns to speed up queries.

Examples

Creates a B-tree index on the 'name' column of the 'users' table.

PostgreSQL

CREATE INDEX idx_users_name ON users (name);

Explicitly creates a B-tree index on 'order_date' column in 'orders' table.

PostgreSQL

CREATE INDEX idx_orders_date ON orders USING btree (order_date);

Creates a unique B-tree index to ensure no duplicate emails in 'customers' table.

PostgreSQL

CREATE UNIQUE INDEX idx_email_unique ON customers (email);

Sample Program

This creates a 'products' table, inserts some rows, creates a B-tree index on 'price', and shows the query plan for a search using that index.

PostgreSQL

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name TEXT,
  price NUMERIC
);

INSERT INTO products (name, price) VALUES
('Apple', 1.20),
('Banana', 0.80),
('Carrot', 0.50);

CREATE INDEX idx_products_price ON products (price);

EXPLAIN SELECT * FROM products WHERE price > 0.60;

Important Notes

B-tree indexes work best for equality and range queries.

They keep data sorted internally, which helps with ORDER BY queries.

Too many indexes can slow down data inserts and updates.

Summary

B-tree indexes speed up searching and sorting in PostgreSQL.

They are the default and most common index type.

Use them for quick lookups, range queries, and enforcing uniqueness.