PostgreSQL Index

Speed up your database queries with indexes

🚀 What is a PostgreSQL Index?

An index is a database object that improves query speed by creating a quick lookup structure. Think of it like a book's index that helps you find information faster without reading every page. 


-- Create a simple index
CREATE INDEX idx_users_email 
ON users(email);

Output:

CREATE INDEX

Key Index Concepts

⚡

Speed

Indexes make queries faster 

-- Fast lookup with index
SELECT * FROM users 
WHERE email = '[email protected]';
🔍

Search

Quick data retrieval 

-- Index helps find data
CREATE INDEX idx_name 
ON products(name);
📊

Structure

Organized data storage 

-- B-tree index (default)
CREATE INDEX idx_price 
ON products(price);
🎯

Columns

Index specific columns 

-- Multi-column index
CREATE INDEX idx_user_date 
ON orders(user_id, order_date);

🔹 Creating Your First Index

Indexes are created on table columns to speed up searches. Here's how to create a basic index on a users table to make email lookups faster. 

-- First, create a table
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255),
    name VARCHAR(100)
);

-- Create an index on email column
CREATE INDEX idx_users_email ON users(email);

-- Now queries on email will be faster
SELECT * FROM users WHERE email = '[email protected]';

Output:

CREATE TABLE
CREATE INDEX
Query executes faster with index!

🔹 Types of Indexes

PostgreSQL supports different index types for different use cases. B-tree is the default and most common type, suitable for equality and range queries. 

-- B-tree index (default) - for most queries
CREATE INDEX idx_btree ON products(price);

-- Hash index - for equality comparisons only
CREATE INDEX idx_hash ON products USING HASH(sku);

-- GiST index - for geometric data
CREATE INDEX idx_gist ON locations USING GIST(coordinates);

-- GIN index - for full-text search
CREATE INDEX idx_gin ON articles USING GIN(to_tsvector('english', content));

Output:

CREATE INDEX (for each command)

🔹 Multi-Column Indexes

You can create indexes on multiple columns when queries frequently filter by those columns together. The order of columns matters for query performance. 

-- Create a multi-column index
CREATE INDEX idx_orders_user_date 
ON orders(user_id, order_date);

-- This query benefits from the index
SELECT * FROM orders 
WHERE user_id = 123 
AND order_date = '2024-01-15';

-- This also benefits (uses first column)
SELECT * FROM orders WHERE user_id = 123;

Output:

Both queries run faster with the multi-column index

🔹 Viewing and Managing Indexes

You can list all indexes on a table and drop indexes you no longer need. Removing unused indexes saves storage space and improves write performance. 

-- View all indexes on a table
SELECT indexname, indexdef 
FROM pg_indexes 
WHERE tablename = 'users';

-- Drop an index
DROP INDEX idx_users_email;

-- Drop index if it exists
DROP INDEX IF EXISTS idx_users_email;

Output:

Lists all indexes or drops the specified index

🔹 When to Use Indexes

Indexes improve read performance but slow down writes. Use indexes on columns frequently used in WHERE, JOIN, and ORDER BY clauses for best results.

✅ Good for indexes:

  • Columns used in WHERE clauses
  • Foreign key columns
  • Columns used in JOIN operations
  • Columns used in ORDER BY

❌ Avoid indexes on:

  • Small tables (few rows)
  • Columns with many NULL values
  • Columns that change frequently
  • Columns with low cardinality (few unique values)

🧠 Test Your Knowledge

What is the main purpose of a database index?