Partial Indexes

PostgreSQL supports an often overlooked feature called "Partial Indexes"; this allows you to have an index with a WHERE clause just like in a SELECT statement. With a partial index the querying for records with a frequently used key can be greatly accelerated; records without that key value can be immediately discarded.

From the PostgreSQL documentation: Since a query searching for a common value (one that accounts for more than a few percent of all the table rows) will not use the index anyway, there is no point in keeping those rows in the index at all. This reduces the size of the index, which will speed up queries that do use the index.

Take for instance the "Preferred Job Executant" in the OpenGroupware task application. This feature uses object links stored in a table called "obj_link" to connect x number of users to a task. The value of importance here is the "link_type" field. Every time a user looks at their assigned jobs a query like the following is executed:


SELECT t1.obj_link_id, t1.source_id, t1.source_type,
t1.target, t1.target_id, t1.target_type,
t1.link_type, t1.label
FROM obj_link t1
WHERE t1.link_type = 'Preferred Job Executant'
AND t1.target_id = 10160


The important thing to know here is that value of "link_type" being "Preferred Job Executant" is static, the "target_id" value will change depending upon the user but the "link_type" will always be "Preferred Job Executant".

Our problem is that the "obj_link" table can get quite large but there are usually relatively few distinct values of "link_type". This means PostgreSQL will almost certainly give up using a normal index at some point. Enter the partial index -


CREATE INDEX assigned_task_links
ON obj_link(target_id)
WHERE link_type = 'Preferred Job Executant'


The "WHERE" clause causes only those records where "link_type" is "Preferred Job Executant" to be included in the index. Now OpenGroupware can find assigned tasks for this user without having to consider non-assigned-task object links at all; we avoid the dreaded sequential scan.

The entire section on partial indexing is very much worth a read if you want the best possible performance from you database applications.