Will PostgreSQL use parts of a composite index even if all parts aren't listed in the where clauses?

All we need is an easy explanation of the problem, so here it is.

I could obviously test this empirically but I’ve found that without loading fake data EXPLAIN shows that Postgres just loads the full page of memory containing the table and scans. I also haven’t found the answer in the docs.

My question is, if I have an index on a table such as (colA, colB) and a query containing where colA = 'something' will Postgres use this index even though colB is not in the query? Assume that no index containing just colA exists. It stands to reason that this index could be useful in the query execution, but I’ve had a hard time tracking the answer down definitively.

(Postgres v12.x)

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Yes, because that composite index in your example is also known as a covering index. Specifically mentioned in the previously linked documentation here:

…people sometimes made covering indexes by writing the payload columns as ordinary index columns, that is writing
CREATE INDEX tab_x_y ON tab(x, y);
even though they had no intention of ever using y as part of a WHERE clause. This works fine as long as the extra columns are trailing columns…

Basically, as long as the fields of your predicates are all sequentially part of the index definition (from left to right), then that index is covering and applicable to be used for that query.

The reverse of your example would not work though since the index does not cover that scenario. I.e. WHERE colB = 'someValue' is not covered by an index with the definition of (colA, colB) since the order of the field list in the index definition literally defines the structure of the underlying B-Tree used to sort the data. So first the data is sorted on colA and then colB, making it not possible to directly search on colB (aka directly seek on colB, rather an index scan could still be performed to indirectly search on colB) without a predicate to search on colA first.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply