3

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)

Improve this question
2
  • 1
    Loading fake data is trivially easy to do, and is invaluable as you won't be able to find every detail in the docs. create table j as select floor(random()*10000)::int as a, floor(random()*10000)::int as b, md5(random()::text) as t from generate_series(1,1e6);
    – jjanes
    Commented Apr 1, 2021 at 15:23
  • Thanks @jjanes that's very helpful
    – AlexMayle
    Commented Apr 1, 2021 at 18:34

1 Answer 1

Reset to default
6

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.

Improve this answer
2
  • 2
    It can be used for colB='somevalue'. It won't be very efficient as it will need to scan the entire index not just one part of it, but that might still be more efficient than scanning the table if the table rows are much wider than the index entries.
    – jjanes
    Commented Apr 1, 2021 at 15:26
  • 1
    @jjanes Upvoted because that's an important point when you said "it will need to scan the entire index not just one part of it, but that might still be more efficient than scanning the table". Just wanted to note myself that my answer doesn't say the index can't be used, rather my answer means it just doesn't cover it so it can't directly be used for just colB when I said "not possible to directly search on colB". But I think you articulated it better by communicating the operation would be a scan if it were to be used. Hopefully my updated wording communicates that better.
    – J.D.
    Commented Apr 1, 2021 at 15:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.