Database Reference
In-Depth Information
The partial index
The partial index is an index that applies only on the rows that complete the
designated condition. Most of the time, the subset of the table is used in the queries.
In this case, creating an index on the whole table is a waste of space and time
consuming. We should consider creating an index on the subset of the table in this
case. The rudimental reason to have a partial index is better performance by using
the least amount of disk space as compared to creating an index on a whole table.
To create an index on the subset of the table, we use a partial index. The partial index
can be created by specifying the
WHERE
condition during index creation as follows:
CREATE INDEX index_name ON table_name (column) WHERE (condition);
Suppose most of the queries use the subset of the
item
table where
item_id
is less
than
106
, and then creating an index on the whole
item
table is a waste of space.
The optimal case is to create an index only on the irst 100 rows of the
warehouse_tbl
table. In this case, the partial index can be created for the rows less than 100. This can
be done in the following manner:
warehouse_db=# CREATE INDEX item_partial_index ON item (item_id)
WHERE (item_id < 106);
The result can be seen using the following statement:
warehouse_db=# \d item;
Table "item"
Column | Type | Modifiers
------------+--------------------+-----------
item_id | integer | not null
item_name | text |
item_price | numeric |
item_data | text |
Indexes:
"item_index" btree (item_id)
"item_multi_index" btree (item_id, item_price)
"item_partial_index" btree (item_id) WHERE item_id < 106
A B-tree index supports up to 32 columns to join in a single index.
