What are the advantages and disadvantages of using a 'Partial Index'?

Question

PostgreSQL allows the creation of 'Partial Indexes' which are basically indexes with conditional predicates. http://www.postgresql.org/docs/8.2/static/indexes-partial.html

While testing, I found that they are performing very well for a case where the query is accessing only certain 12 rows in a table with 120k rows.

But before we deploy this, are there any disadvantages or caveats we should be aware of?

Answer 1

Pro:

This reduces the size of the index, which will speed up queries that do use the index. It will also speed up many table update operations because the index does not need to be updated in all cases

Con:

Since this no longer is a real index, if you do full join or filtering on values not covered by the index, the performance will degrade as your table size grows.

Answer 2

The main caveat is not to use them inappropriately. They are good for identifying rows that match one set of criteria where those rows are the minority in a large table.

Take a table Orders for example with an order_status column and distribution of your data over the order statuses as follows:

• New 5%
• Backordered 2%
• In Transit 5%
• Completed 85%
• Returned 3%

If you very often query where order_status = 'New' to get a list of new orders and then most the rest of the time just reference orders directly by ID, then a partial index would be beneficial. In your example of 12 rows out of 120k this is even more true.