Partial indexes with WHERE clause in PostgreSQL

Introduction

Partial indexes help speed up searches by indexing only the rows that meet a specific condition. This saves space and makes queries faster.

You want to index only active users, not all users.

You need to speed up queries on recent orders but not old ones.

You want to index rows where a status column equals 'pending'.

You want to save disk space by indexing only important rows.

You want to improve performance for queries with a common filter.

Syntax

PostgreSQL

CREATE INDEX index_name ON table_name(column_name) WHERE condition;

The WHERE clause defines which rows are included in the index.

Only rows matching the condition are indexed, others are ignored.

Examples

Indexes only users who are active.

PostgreSQL

CREATE INDEX idx_active_users ON users(last_login) WHERE active = true;

Indexes only orders placed after January 1, 2024.

PostgreSQL

CREATE INDEX idx_recent_orders ON orders(order_date) WHERE order_date > '2024-01-01';

Indexes only tasks that are pending.

PostgreSQL

CREATE INDEX idx_pending_tasks ON tasks(priority) WHERE status = 'pending';

Sample Program

This creates a table and inserts employees. Then it creates a partial index on active sales employees only. The EXPLAIN shows if the index is used for the query.

PostgreSQL

CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  name TEXT,
  department TEXT,
  active BOOLEAN
);

INSERT INTO employees (name, department, active) VALUES
('Alice', 'Sales', true),
('Bob', 'Sales', false),
('Carol', 'HR', true),
('Dave', 'HR', false);

CREATE INDEX idx_active_sales ON employees(name) WHERE department = 'Sales' AND active = true;

EXPLAIN SELECT * FROM employees WHERE department = 'Sales' AND active = true;

Important Notes

Partial indexes only help if your queries use the same condition as the WHERE clause.

They save space but add complexity to your database design.

Use EXPLAIN to check if your partial index is being used.

Summary

Partial indexes index only rows matching a condition.

They improve query speed and save space.

Use WHERE clause to define which rows to index.