# Partial index

> Mediated Wiki article. Canonical URL: https://mediated.wiki/source/Partial_index
> Markdown URL: https://mediated.wiki/source/Partial_index.md
> Source: https://en.wikipedia.org/wiki/Partial_index
> Source revision: 1292667684
> License: Creative Commons Attribution-ShareAlike 4.0 International (https://creativecommons.org/licenses/by-sa/4.0/)

In [databases](/source/Relational_database_management_system), a '''partial index''', also known as '''filtered index''' is an [index](/source/Index_(database)) which has some condition applied to it so that it includes a subset of [row](/source/Row_(database))s in the [table](/source/Table_(database)).

This allows the index to remain small, even though the table may be rather large, and have extreme selectivity.

Suppose you have a transaction table where entries start out with STATUS = 'A' (active), and then may pass through other statuses ('P' for pending, 'W' for "being worked on") before reaching a final status, 'F', at which point it is no longer likely to be processed again.

In PostgreSQL, a useful partial index might be defined as:
<syntaxhighlight lang="sql">
create index partial_status on txn_table (status) 
where status in ('A', 'P', 'W');
</syntaxhighlight>
This index would not bother storing any of the millions of rows that have reached "final" status, 'F', and would allow queries looking for transactions that still "need work" to efficiently search via this index.

Similarly, a partial index can be used to index only those rows where a column is not null, which will be of benefit when the column usually is null.
<syntaxhighlight lang="sql">
create index partial_object_update on object_table (updated_on) 
where updated_on is not null;
</syntaxhighlight>
This index would allow the following query to read only the updated tuples:
<syntaxhighlight lang="sql">
select * from object_table 
where updated_on is not null 
order by updated_on;
</syntaxhighlight>
It is not necessary that the condition be the same as the index criterion; Stonebraker's paper below presents a number of examples with indexes similar to the following:
<syntaxhighlight lang="sql">
create index partial_salary on employee(age) 
where salary > 2100;
</syntaxhighlight>
==Support==

In [SQL Server](/source/Microsoft_SQL_Server), this type of index is called a ''filtered index''.<ref>{{cite book |url=https://technet.microsoft.com/en-us/library/cc280372.aspx |title=SQL Server 2008 Documentation: Filtered Index Design Guidelines |publisher=[Microsoft TechNet](/source/Microsoft_TechNet) }}</ref>

Partial indexes have been supported in [PostgreSQL](/source/PostgreSQL) since version 7.2, released in February 2002.<ref>{{cite web
| url=https://www.postgresql.org/docs/current/static/release-7-2.html
| access-date=2009-10-09
| title=PostgreSQL Documentation: Release Notes: Release 7.2
| quote=Enable partial indexes (Martijn van Oosterhout)
| publisher=PostgreSQL
}}</ref>

[SQLite](/source/SQLite) supports partial indexes since version 3.8.0.<ref>{{cite web
| url=https://www.sqlite.org/partialindex.html
| access-date=2014-02-04
| title=Partial Indexes
}}</ref>

[MongoDB](/source/MongoDB) supports partial indexes since version 3.2.<ref>{{cite book | url=https://docs.mongodb.org/manual/release-notes/3.2/ |title=MongoDB V302 Release Notes}}</ref>

==References==

{{reflist}}

==External links==
*[https://db.cs.berkeley.edu/papers/ERL-M89-17.pdf The Case For Partial Indexes]

{{DEFAULTSORT:Partial Index}}
Category:Database management systems
Category:PostgreSQL

---
Adapted from the Wikipedia article [Partial index](https://en.wikipedia.org/wiki/Partial_index) by Wikipedia contributors ([contributor history](https://en.wikipedia.org/wiki/Partial_index?action=history)). Available under [Creative Commons Attribution-ShareAlike 4.0 International](https://creativecommons.org/licenses/by-sa/4.0/). Changes may have been made.