PostgreSQL Expression Index

Key Expression Index Uses

-- Lowercase index
CREATE INDEX idx_name_lower 
ON users(LOWER(name));

-- Index by year
CREATE INDEX idx_year 
ON orders(EXTRACT(YEAR FROM created_at));

-- Index total price
CREATE INDEX idx_total 
ON orders((price * quantity));

-- Index substring
CREATE INDEX idx_domain 
ON emails(SUBSTRING(email FROM '@(.*)$'));

🔹 Case-Insensitive Search Index

The most common expression index uses LOWER() or UPPER() for case-insensitive searches. This allows users to search for 'john', 'John', or 'JOHN' and get the same results quickly.

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

-- Create case-insensitive index
CREATE INDEX idx_email_lower ON users(LOWER(email));

-- Query must use the same function to use the index
SELECT * FROM users WHERE LOWER(email) = LOWER('[email protected]');

-- This also works
SELECT * FROM users WHERE LOWER(email) = '[email protected]';

🔹 Date Part Expression Index

Index specific parts of dates like year, month, or day. This is useful for queries that filter by date components, like finding all orders from a specific year.

-- Create orders table
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER,
    amount DECIMAL(10,2),
    created_at TIMESTAMP DEFAULT NOW()
);

-- Index by year
CREATE INDEX idx_order_year 
ON orders(EXTRACT(YEAR FROM created_at));

-- Fast query for orders by year
SELECT * FROM orders 
WHERE EXTRACT(YEAR FROM created_at) = 2024;

-- Index by month and year
CREATE INDEX idx_order_month 
ON orders(EXTRACT(YEAR FROM created_at), EXTRACT(MONTH FROM created_at));

🔹 Calculation Expression Index

Index calculated values like totals, percentages, or other mathematical expressions. This speeds up queries that filter or sort by computed values without storing them separately.

-- Create order_items table
CREATE TABLE order_items (
    id SERIAL PRIMARY KEY,
    order_id INTEGER,
    price DECIMAL(10,2),
    quantity INTEGER,
    discount DECIMAL(5,2) DEFAULT 0
);

-- Index total price (price * quantity)
CREATE INDEX idx_total_price 
ON order_items((price * quantity));

-- Fast query for items by total
SELECT * FROM order_items 
WHERE (price * quantity) > 100;

-- Index with discount applied
CREATE INDEX idx_final_price 
ON order_items((price * quantity * (1 - discount/100)));

🔹 JSON Expression Index

Index specific fields within JSON columns for fast queries on JSON data. This allows you to query JSON properties as efficiently as regular columns.

-- Create products table with JSON
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200),
    attributes JSONB
);

-- Index a specific JSON field
CREATE INDEX idx_product_color 
ON products((attributes->>'color'));

-- Fast query on JSON field
SELECT * FROM products 
WHERE attributes->>'color' = 'red';

-- Index nested JSON field
CREATE INDEX idx_product_price 
ON products(((attributes->'pricing'->>'amount')::NUMERIC));

🔹 Text Pattern Expression Index

Create indexes for pattern matching and text extraction. This is useful for searching by domain names, area codes, or other text patterns within strings.

-- Create contacts table
CREATE TABLE contacts (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255),
    phone VARCHAR(20)
);

-- Index email domain
CREATE INDEX idx_email_domain 
ON contacts(SUBSTRING(email FROM '@(.*)$'));

-- Find all users from a domain
SELECT * FROM contacts 
WHERE SUBSTRING(email FROM '@(.*)$') = 'example.com';

-- Index area code from phone
CREATE INDEX idx_area_code 
ON contacts(SUBSTRING(phone FROM 1 FOR 3));

🔹 Important Rules

Expression indexes require exact matching between the index expression and query expression. The query must use the same function and parameters as the index to benefit from it.

-- Index created with LOWER()
CREATE INDEX idx_name ON users(LOWER(name));

-- ✅ This uses the index
SELECT * FROM users WHERE LOWER(name) = 'john';

-- ❌ This does NOT use the index
SELECT * FROM users WHERE name = 'john';