In
databases
In computing, a database is an organized collection of data stored and accessed electronically. Small databases can be stored on a file system, while large databases are hosted on computer clusters or cloud storage. The design of databases spa ...
, a partial index, also known as filtered index is an
index
Index (or its plural form indices) may refer to:
Arts, entertainment, and media Fictional entities
* Index (''A Certain Magical Index''), a character in the light novel series ''A Certain Magical Index''
* The Index, an item on a Halo megastru ...
which has some condition applied to it so that it includes a subset of
row
Row or ROW may refer to:
Exercise
*Rowing, or a form of aquatic movement using oars
*Row (weight-lifting), a form of weight-lifting exercise
Math
*Row vector, a 1 × ''n'' matrix in linear algebra.
*Row (database), a single, implicitly structured ...
s in the
table
Table may refer to:
* Table (furniture), a piece of furniture with a flat surface and one or more legs
* Table (landform), a flat area of land
* Table (information), a data arrangement with rows and columns
* Table (database), how the table data ...
.
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:
create index partial_status on txn_table (status)
where status in ('A', 'P', 'W');
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.
create index partial_object_update on object_table (updated_on)
where updated_on is not null;
This index would allow the following query to read only the updated tuples:
select * from object_table
where updated_on is not null
order by updated_on;
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:
create index partial_salary on employee(age)
where salary > 2100;
Support
In
SQL Server, this type of index is called a ''filtered index''.
Partial indexes have been supported in
PostgreSQL
PostgreSQL (, ), also known as Postgres, is a free and open-source relational database management system (RDBMS) emphasizing extensibility and SQL compliance. It was originally named POSTGRES, referring to its origins as a successor to the In ...
since version 7.2, released in February 2002.
SQLite
SQLite (, ) is a database engine written in the C programming language. It is not a standalone app; rather, it is a library that software developers embed in their apps. As such, it belongs to the family of embedded databases. It is the most ...
supports partial indexes since version 3.8.0.
MongoDB
MongoDB is a source-available cross-platform document-oriented database program. Classified as a NoSQL database program, MongoDB uses JSON-like documents with optional schemas. MongoDB is developed by MongoDB Inc. and licensed under the Serve ...
supports partial indexes since version 3.2.
References
External links
The Case For Partial Indexes
{{DEFAULTSORT:Partial Index
Database management systems
PostgreSQL