Advanced Index Types

Beyond basic B-tree indexes, PostgreSQL offers several advanced indexing strategies that can dramatically improve performance for specific query patterns. Understanding when and how to use each type is key to building efficient databases.

Partial Indexes

A partial index only indexes a subset of rows that match a condition. This makes the index smaller, faster to search, and cheaper to maintain.

Syntax

CREATE INDEX index_name ON table_name (column_name)
WHERE condition;

Example: Indexing Only Active Records

Imagine you frequently query for only a specific payment method:

-- Only index credit card sales
CREATE INDEX idx_sales_credit_card
ON sales (sale_date, amount)
WHERE payment_method = 'credit_card';

This index will be used when your query includes the matching condition:

-- This query can use the partial index
SELECT * FROM sales
WHERE payment_method = 'credit_card'
  AND sale_date > '2025-06-01';

-- This query CANNOT use the partial index
SELECT * FROM sales
WHERE payment_method = 'cash'
  AND sale_date > '2025-06-01';

When to Use Partial Indexes

• When queries frequently filter for a specific subset of data
• When one value dominates (e.g., 90% of orders have status = 'completed' -- index only the 10% that are 'pending')
• To enforce uniqueness on a subset: CREATE UNIQUE INDEX ... WHERE is_active = true

Partial Unique Index Example

A powerful pattern is enforcing uniqueness only for certain rows:

-- Each region can have only one sale per date (hypothetical)
CREATE UNIQUE INDEX idx_unique_region_date
ON sales (region, sale_date)
WHERE amount > 500;

Expression Indexes

An expression index (also called a functional index) indexes the result of an expression or function rather than the raw column value.

Syntax

CREATE INDEX index_name ON table_name (expression);

Case-Insensitive Lookups

CREATE INDEX idx_sales_region_lower ON sales (LOWER(region));

Now case-insensitive queries can use the index:

-- Uses the expression index
SELECT * FROM sales WHERE LOWER(region) = 'north';

Important: the query must use the exact same expression as the index. WHERE region = 'North' will not use an index on LOWER(region).

Date Part Extraction

CREATE INDEX idx_sales_month
ON sales (EXTRACT(MONTH FROM sale_date));

This speeds up queries like:

SELECT * FROM sales
WHERE EXTRACT(MONTH FROM sale_date) = 6;

Computed Values

-- Index on a calculated column
CREATE INDEX idx_sales_year_amount
ON sales (EXTRACT(YEAR FROM sale_date), amount * 1.1);

Multi-Column Indexes

A multi-column index (also called a composite index) indexes two or more columns together.

Column Order Matters

The leftmost prefix rule determines which queries can use the index. For an index on (A, B, C):

Example

CREATE INDEX idx_sales_region_date_amount
ON sales (region, sale_date, amount);

This single index effectively supports:

-- Uses full index
SELECT * FROM sales
WHERE region = 'North'
  AND sale_date > '2025-01-01'
  AND amount > 100;

-- Uses first two columns of the index
SELECT * FROM sales
WHERE region = 'East'
  AND sale_date BETWEEN '2025-03-01' AND '2025-06-30';

-- Uses only the first column
SELECT * FROM sales WHERE region = 'South';

Choosing Column Order

Put the column order based on:

1. Equality conditions first: Columns filtered with = should come before range filters (>, <, BETWEEN)
2. Most selective first: Columns that narrow down the most rows should lead
3. Consider ORDER BY: If you often sort by certain columns, include them in order

Covering Indexes (INCLUDE)

A covering index includes additional columns that are not part of the search key. This enables index-only scans where PostgreSQL can answer a query entirely from the index without visiting the table.

Syntax

CREATE INDEX index_name ON table_name (search_columns)
INCLUDE (extra_columns);

Example

If you frequently run this query:

SELECT region, sale_date, amount
FROM sales
WHERE region = 'North'
ORDER BY sale_date;

You can create a covering index:

CREATE INDEX idx_sales_covering
ON sales (region, sale_date)
INCLUDE (amount);

Now PostgreSQL can answer the query entirely from the index:

• region and sale_date are the search/sort keys
• amount is stored in the index leaf nodes but is not part of the key

INCLUDE vs Adding to the Key

Why not just use (region, sale_date, amount) as the key? Two reasons:

1. Index size: INCLUDE columns are only in leaf nodes, not internal nodes, making the index smaller
2. Uniqueness: For unique indexes, INCLUDE columns are not part of the uniqueness check

-- Unique on (region, sale_date), but amount is available for index-only scans
CREATE UNIQUE INDEX idx_unique_covering
ON sales (region, sale_date)
INCLUDE (amount);

Other Index Types in PostgreSQL

While B-tree is the most common, PostgreSQL supports several other index types:

Creating Non-B-tree Indexes

-- Hash index (equality lookups only)
CREATE INDEX idx_sales_region_hash ON sales USING hash (region);

-- GIN index for JSONB (covered in the JSON chapter)
CREATE INDEX idx_settings_prefs ON user_settings USING gin (preferences);

Index Maintenance Tips

1. Monitor unused indexes: They cost write performance for no benefit

SELECT indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0;

2. Check index size:

SELECT indexname,
       pg_size_pretty(pg_relation_size(indexname::regclass)) AS index_size
FROM pg_indexes
WHERE tablename = 'sales';

3. Reindex if bloated:

REINDEX INDEX idx_sales_region;

Try It Yourself

1. Think about the queries you run most often on the sales table. Which columns would you index?

2. Consider a partial index on the sales table that only indexes high-value sales (amount > 200). Why would this be smaller and faster than a full index?

3. If you have a query WHERE EXTRACT(YEAR FROM sale_date) = 2025, what kind of index would you create to speed it up?