Example
SQL Server and SQLite allow to create indexes that contain not only a subset of columns, but also a subset of rows.
Consider a constant growing amount of orders with order_state_id equal to finished (2), and a stable amount of orders with order_state_id equal to started (1).
If your business make use of queries like this:
SELECT id, comment
FROM orders
WHERE order_state_id = 1
AND product_id = @some_value;
Partial indexing allows you to limit the index, including only the unfinished orders:
CREATE INDEX Started_Orders
ON orders(product_id)
WHERE order_state_id = 1;
This index will be smaller than an unfiltered index, which saves space and reduces the cost of updating the index.
