What is partial index?

Partial indexes

+

A partial index is an index with a WHERE clause. It will only index rows that match the supplied predicate. You can use them to exclude values from an index that you hardly query against.

+

For example, you have an orders table with a completed flag. The sales people want to know what orders over $100,000.00 haven’t been completed because they want to collect their bonuses, so you build a view in your app to show them just that (and negotiate a cut on the bonus). You could create the following index:

+

CREATE INDEX orders_incomplete_amount_index
   on orders (amount) WHERE complete is not true;

+

Which will be used by queries of the form:

+

SELECT * FROM orders
  where amount > 100000 AND complete is not true;

Why is my query not using an index?

There are many reasons why the Postgres planner may choose to not use an index. Most of the time, the planner chooses correctly, even if it isn’t obvious why. It’s okay if the same query uses an index scan on some occasions but not others. The number of rows retrieved from the table may vary based on the particular constant values the query retrieves. So, for example, it might be correct for the query planner to use an index for the query select * from foo where bar = 1, and yet not use one for the query select * from foo where bar = 2, if there happened to be far more rows with “bar” values of 2. When this happens, a sequential scan is actually most likely much faster than an index scan, so the query planner has in fact correctly judged that the cost of performing the query that way is lower.