PostgreSQL Indexes

What Indexes Are

Indexes are database objects that improve query performance by creating a fast lookup structure, similar to a book's index. They help the database find rows faster without scanning the entire table.

sql

-- Create a basic index
CREATE INDEX idx_employee_name ON employees (name);

-- View existing indexes
SELECT * FROM pg_indexes WHERE tablename = 'employees';

-- Check if index is used in a query
EXPLAIN ANALYZE SELECT * FROM employees WHERE name = 'John Doe';

How Indexes Work:

• Store sorted copies of column values with pointers to actual rows

• Reduce disk I/O by avoiding full table scans

• Trade-off: Additional storage + maintenance overhead on writes

B-tree Indexes (Default Index Type)

B-tree (Balanced Tree) is PostgreSQL's default index type, suitable for most scenarios.

sql

-- Default B-tree index
CREATE INDEX idx_employee_salary ON employees (salary);

-- B-tree supports these operators:
-- =, <, <=, >, >=, BETWEEN, IN, IS NULL, IS NOT NULL
-- Also pattern matching with LIKE 'pattern%' (prefix matches only)

When to use B-tree:

• Equality and range queries (=, >, <, BETWEEN)

• Sorting operations (ORDER BY)

• Columns with high cardinality (many unique values)

• Most common use cases (default choice)

Example queries that use B-tree efficiently:

sql

-- Equality
SELECT * FROM employees WHERE department = 'Engineering';

-- Range
SELECT * FROM employees WHERE salary BETWEEN 50000 AND 80000;

-- Sorting
SELECT * FROM employees ORDER BY hire_date LIMIT 10;

-- Prefix pattern matching
SELECT * FROM employees WHERE name LIKE 'Joh%';

Limitations:

• Cannot optimize suffix/contains pattern matching: LIKE '%son'

• Less efficient for columns with very low cardinality (few unique values)

Composite Indexes (Multi-column Indexes)

Indexes on multiple columns. Column order matters significantly!

sql

-- Create composite index
CREATE INDEX idx_employee_dept_salary ON employees (department, salary);

-- Also works with DESC
CREATE INDEX idx_employee_dept_salary_desc 
ON employees (department, salary DESC);

How Composite Indexes Work (Leftmost Prefix Principle):

• Index on (A, B, C) can be used for:

  • Queries on A

  • Queries on A, B

  • Queries on A, B, C

  • But NOT queries on just B or C alone

Effective examples:

sql

-- Uses composite index (both columns)
SELECT * FROM employees 
WHERE department = 'Engineering' AND salary > 70000;

-- Uses composite index (first column only)
SELECT * FROM employees 
WHERE department = 'Engineering';

-- May use index if department is selective
SELECT * FROM employees 
WHERE department = 'Engineering' 
ORDER BY salary;  -- Index provides pre-sorted data

Ineffective examples:

sql

-- WON'T use index (missing first column)
SELECT * FROM employees WHERE salary > 70000;

-- WON'T use index efficiently
SELECT * FROM employees 
WHERE department LIKE '%ing' AND salary > 70000;

Best Practices:

1. Order columns by selectivity (most selective first)

2. Include columns used in WHERE clauses first

3. Add columns used in ORDER BY/GROUP BY next

4. Consider INCLUDE columns for covering indexes:

   sql

   CREATE INDEX idx_covering ON employees (department) 
   INCLUDE (name, email);  -- Store extra columns in leaf nodes

Unique Indexes

Enforce uniqueness and provide performance benefits:

sql

-- Create unique index
CREATE UNIQUE INDEX idx_unique_email ON employees (email);

-- Alternative: Add unique constraint (creates unique index internally)
ALTER TABLE employees ADD CONSTRAINT uk_employee_email UNIQUE (email);

-- Composite unique index
CREATE UNIQUE INDEX idx_unique_dept_emp 
ON employees (department, employee_code);

Key Points:

• Prevent duplicate values in indexed columns

• Automatically created for PRIMARY KEY and UNIQUE constraints

• Can be composite (multiple columns)

• Allow NULL values (multiple NULLs are allowed unless filtered)

Checking uniqueness:

sql

-- See unique indexes
SELECT 
    t.relname as table_name,
    i.relname as index_name,
    array_to_string(array_agg(a.attname), ', ') as column_names
FROM pg_class t, pg_class i, pg_index ix, pg_attribute a
WHERE t.oid = ix.indrelid
    AND i.oid = ix.indexrelid
    AND a.attrelid = t.oid
    AND a.attnum = ANY(ix.indkey)
    AND t.relkind = 'r'
    AND ix.indisunique = true  -- Only unique indexes
    AND t.relname = 'employees'
GROUP BY t.relname, i.relname;

When NOT to Use Indexes

1. Small Tables

sql

-- Table with < 1000 rows - sequential scan is faster
-- Index overhead > benefit
CREATE TABLE tiny_table (id INT, data TEXT);  -- ~100 rows
-- Avoid: CREATE INDEX idx_tiny ON tiny_table(data);

2. Tables with Frequent Write Operations

sql

-- Indexes slow down: INSERT, UPDATE, DELETE
-- Each write must update all related indexes
-- Evaluate: Read vs Write ratio

3. Columns with Low Cardinality

sql

-- Boolean columns, status flags, gender columns
-- Example: is_active BOOLEAN (only true/false)
-- Sequential scan might be faster unless combined with other columns

4. Columns Frequently Updated

sql

-- Index maintenance on every update
CREATE TABLE orders (
    status VARCHAR(20),  -- Frequently changes: 'new'→'processing'→'shipped'
    last_updated TIMESTAMP  -- Changes every update
);
-- Index on status may hurt performance

5. Very Large Text/BLOB Columns

sql

CREATE TABLE documents (
    content TEXT  -- Very large content (megabytes)
);
-- Avoid: CREATE INDEX idx_content ON documents(content);
-- Consider: Expression index on extracted metadata instead
CREATE INDEX idx_doc_title ON documents (LEFT(content, 100));

6. Tables Mostly Used for Full Table Scans

sql

-- When > 30-40% of rows are returned
-- Sequential scan is more efficient
SELECT * FROM employees WHERE salary > 30000;  -- Returns 80% of rows
-- Index scan + random I/O might be slower

7. Temporary Tables for One-time Use

sql

-- Unless reused multiple times in a session
CREATE TEMP TABLE temp_data AS SELECT * FROM large_table;
-- Usually not worth indexing

8. Columns Used Only in Non-SARGable Operations

sql

-- Non-SARGable = Search Argument Not Able
-- Index can't be used efficiently

-- Examples to avoid indexing for:
-- 1. Functions on indexed column
WHERE UPPER(name) = 'JOHN'  -- Won't use index on 'name'

-- 2. Calculations on indexed column
WHERE salary * 1.1 > 100000  -- Won't use index on 'salary'

-- 3. Pattern matching with leading wildcard
WHERE email LIKE '%@gmail.com'  -- Won't use index on 'email'

-- Instead, consider expression indexes:
CREATE INDEX idx_upper_name ON employees (UPPER(name));

Index Maintenance & Best Practices

sql

-- Monitor index usage
SELECT 
    schemaname,
    tablename,
    indexname,
    idx_scan as index_scans  -- Low scans = possibly unused index
FROM pg_stat_user_indexes;

-- Rebuild bloated indexes
REINDEX INDEX idx_employee_name;

-- Analyze table to update statistics
ANALYZE employees;

-- Get index size
SELECT 
    indexname,
    pg_size_pretty(pg_relation_size(indexname::regclass)) as size
FROM pg_indexes 
WHERE tablename = 'employees';

-- Drop unused indexes
DROP INDEX IF EXISTS idx_unused;

Decision Checklist

Create an index when:

• ✅ Column used frequently in WHERE, JOIN, ORDER BY

• ✅ High cardinality (> 1000 unique values)

• ✅ Read-heavy workload

• ✅ Table has > 10,000 rows

• ✅ Queries return < 5% of rows

Avoid or reconsider indexing when:

• ❌ Table has < 1000 rows

• ❌ Column has low cardinality (< 10 unique values)

• ❌ Write-heavy workload (INSERT/UPDATE/DELETE > 30%)

• ❌ Queries return > 40% of rows

• ❌ Column data type is TEXT/BLOB > 1KB

• ❌ Using functions/calculations on column in WHERE clause

Special Cases:

• Partial indexes for filtered queries:

  sql

  CREATE INDEX idx_active_employees ON employees (department) 
  WHERE is_active = true;  -- Only index active employees

• Expression indexes for computed values:

  sql

  CREATE INDEX idx_lower_email ON employees (LOWER(email));

• Covering indexes to avoid table access:

  sql

  CREATE INDEX idx_covering ON employees (department) 
  INCLUDE (name, salary);  -- Query can read from index only

Performance Impact Example

sql

-- Without index (slow for large tables)
EXPLAIN ANALYZE 
SELECT * FROM employees 
WHERE hire_date > '2023-01-01';  -- Sequential Scan

-- With index (fast)
CREATE INDEX idx_hire_date ON employees (hire_date);
EXPLAIN ANALYZE 
SELECT * FROM employees 
WHERE hire_date > '2023-01-01';  -- Index Scan

-- Monitor after creation
SELECT 
    indexrelname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes 
WHERE relname = 'employees';