PostgreSQL Partial Index

A partial index is an index built over a subset of table, the index contains entries for only those rows that satisfy the predicate defined by a conditional expression “where”.

When all the rows in the table are broken into two categories, and most of them are in the same category, a partial index will speed up the “select” query, especially on the category which contains less rows.

select * from pst_ref where status = 'N'

if most of rows have status = ‘N’, this query will take time. Now we create an partial index on rows which have status = ‘N’.


create index idx_status_n on pst_ref(status) where status = 'N';
analyze pst_ref;