Multicolumn indexes

Suppose the application frequently searches for orders using both customer_id and status.

We can create a single index containing both columns:

This is a multicolumn index because it contains more than one indexed column.

We didn't specify an index type, so PostgreSQL creates a B-tree index, just as it did for the single-column index in the previous chapter.

Now run the query again:

Look for these lines in the execution plan:

Bitmap Index Scan on idx_orders_customer_status tells us that PostgreSQL used the multicolumn index we just created.

The Index Cond line shows that PostgreSQL used both conditions while searching the index.

PostgreSQL can therefore use the index to locate the matching rows instead of examining every row in the orders table.

The order of the columns in a multicolumn B-tree index is important.

We created the index as:

A simplified view of its entries might look like this:

The entries are ordered by customer_id first. Among entries with the same customer_id, they are then ordered by status.

For example, the entries for customer 4243 are grouped together. Within that group, the entries are ordered by status.

This organization allows PostgreSQL to efficiently find entries that match customer_id = 4243 and then narrow that part of the index further using status = 'delivered'.

Consider the index again:

customer_id is the first column in the index, and status is the second.

A multicolumn B-tree index is most effective when the query has conditions on the leftmost columns of the index.

For example, the same index can also be useful for a query that filters only by customer_id:

Even though the query doesn't use status, it has a condition on the first column of the index. PostgreSQL can use the index to locate the entries for customer 4243.

Now consider a query that filters only by status:

This query doesn't have a condition on customer_id, which is the first column of the index.

Because the index is ordered by customer_id first, rows with status = 'delivered' are spread across different customer_id groups rather than forming one continuous part of the index.

The index can therefore be much less useful for this query, and PostgreSQL may choose a sequential scan instead.

This means these two indexes are not equivalent:

The order of the columns should be chosen based on the queries the index needs to support.

The order of columns in the index definition matters.

The order in which you write the conditions in the WHERE clause doesn't.

For example, our index is defined as:

but this query can still use it:

PostgreSQL's planner determines which conditions can be used with the index. You don't need to arrange the conditions in the WHERE clause to match the order of the indexed columns.

A multicolumn index should be created to support the queries your application actually runs.

For example, an index on:

can be useful if the application frequently searches using:

or:

If the application's query patterns are different, a different column order, separate indexes, or another index may be more appropriate.

As with any index, creating a multicolumn index doesn't force PostgreSQL to use it. The planner compares the available execution plans and chooses the one with the lowest estimated cost.

Before continuing, remove the index: