Partial index
Encyclopedia
In databases
, a partial index, also known as filtered index is an index
which has some condition applied to it so that it includes a subset of row
s in the table
.
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;
, this type of index is called a filtered index.
Partial indexes have been supported in PostgreSQL
since version 7.2, released in February 2002.
MySQL as of version 5.4 does not support partial indexes. In MySQL, the term "partial index" is sometimes used to refer to prefix indexes, where only a truncated prefix of each value is stored in the index. This is another technique for reducing index size.
Relational database management system
A relational database management system is a database management system that is based on the relational model as introduced by E. F. Codd. Most popular databases currently in use are based on the relational database model....
, a partial index, also known as filtered index is an index
Index (database)
A database index is a data structure that improves the speed of data retrieval operations on a database table at the cost of slower writes and increased storage space...
which has some condition applied to it so that it includes a subset of row
Row (database)
In the context of a relational database, a row—also called a record or tuple—represents a single, implicitly structured data item in a table. In simple terms, a database table can be thought of as consisting of rows and columns or fields...
s in the table
Table (database)
In relational databases and flat file databases, a table is a set of data elements that is organized using a model of vertical columns and horizontal rows. A table has a specified number of columns, but can have any number of rows...
.
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 ServerMicrosoft SQL Server
Microsoft SQL Server is a relational database server, developed by Microsoft: It is a software product whose primary function is to store and retrieve data as requested by other software applications, be it those on the same computer or those running on another computer across a network...
, this type of index is called a filtered index.
Partial indexes have been supported in PostgreSQL
PostgreSQL
PostgreSQL, often simply Postgres, is an object-relational database management system available for many platforms including Linux, FreeBSD, Solaris, MS Windows and Mac OS X. It is released under the PostgreSQL License, which is an MIT-style license, and is thus free and open source software...
since version 7.2, released in February 2002.
MySQL as of version 5.4 does not support partial indexes. In MySQL, the term "partial index" is sometimes used to refer to prefix indexes, where only a truncated prefix of each value is stored in the index. This is another technique for reducing index size.