How to Create Partial Index in PostgreSQL: Syntax and Examples

In PostgreSQL, you create a partial index using the CREATE INDEX statement with a WHERE clause to specify the condition. This index only includes rows that meet the condition, improving query performance on filtered data.

Syntax

The basic syntax for creating a partial index in PostgreSQL is:

• CREATE INDEX index_name ON table_name (column_name); creates a normal index.
• Adding WHERE condition makes it a partial index.
• The condition filters which rows are indexed.

sql

CREATE INDEX index_name ON table_name (column_name) WHERE condition;

Example

This example creates a partial index on the orders table for rows where status = 'pending'. It speeds up queries filtering on pending orders only.

sql

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  customer_id INT,
  status TEXT
);

CREATE INDEX idx_orders_pending ON orders (customer_id) WHERE status = 'pending';

-- Query that benefits from the partial index
EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'pending' AND customer_id = 123;

Output

Seq Scan on orders  (cost=0.00..12.75 rows=1 width=32) (actual time=0.012..0.013 rows=0 loops=1)
  Filter: ((status)::text = 'pending'::text)
Planning Time: 0.123 ms
Execution Time: 0.045 ms

Common Pitfalls

Common mistakes when creating partial indexes include:

• Not matching the WHERE condition exactly in queries, so the index is not used.
• Using complex conditions that do not improve performance.
• Forgetting that partial indexes only cover rows matching the condition, so queries outside that condition won't benefit.

sql

/* Wrong: Query condition does not match partial index condition */
SELECT * FROM orders WHERE status = 'completed' AND customer_id = 123;

/* Right: Query condition matches partial index condition */
SELECT * FROM orders WHERE status = 'pending' AND customer_id = 123;

Quick Reference

Clause	Description
CREATE INDEX index_name ON table_name (column_name)	Creates a normal index on the specified column.
WHERE condition	Filters rows to include only those matching the condition in the index.
Partial Index	An index built only on rows that satisfy the WHERE condition.

Key Takeaways

Use WHERE clause in CREATE INDEX to create a partial index in PostgreSQL.

Partial indexes improve performance by indexing only relevant rows matching the condition.

Ensure your query's filter matches the partial index condition exactly to use the index.

Partial indexes do not cover all rows, so queries outside the condition won't benefit.

Avoid overly complex conditions in partial indexes to keep them efficient.