Compound index with three keys, what happens if I query skipping the middle one?

Question

With PostgreSQL, I want to use a compound index on three columns A, B, C. B is the created_at datetime, and occasionally I might query without B.

What happens if I compound index on (A, B, C) but then query with conditions on A and C, but not B? (That is, A and C but want it over all time, not just some specific time range?)

Is Postgres smart enough to still use the (A, B, C) compound index but just skip B?

Answer 1

Postgres can use non-leading columns in a b-tree index, but in a far less efficient mode.

If the first column is very selective (only few rows per A) then you will hardly notice a difference in performance since either access method (even a sequential scan over the reduced set) is cheap. The performance hit grows with the number of rows per A.

For the case you describe I suggest to create the index on (A, C, B) or (C, A, B) (just make sure that B comes last) to optimize performance. This way you get best performance for queries on (A, B, C) and on (A, C) alike.

Unlike the sequence of columns in the index, the sequence of predicates in the query does not matter.

We have discussed this in great detail on dba.SE:

• Working of indexes in PostgreSQL

Note that it does not matter whether you lead with A, C or C, A for the case at hand:

• Multicolumn index and performance

There are also some other considerations, but your question does not have all the relevant details.

• Is a composite index also good for queries on the first field?

Answer 2

Yes it is.

I did a quick check by doing an EXPLAIN of a query that had a condition for the first and third column of an index. It did output that it would do a Bitmap Index Scan on that index and mentioned both the first and the third column in the index condition.

(Tested on 9.3.5)