Does indexing on a part of a composite primary key is needed in Postgresql?

Question

I have a (large) table with a composite primary key, composed of 5 columns (a, b, c, d, e).

I'd like to efficiently select all rows having two of those columns (a + e) to a given value.

In PostgreSQL, do I need an index for this? Or will the database use the primary key (even partly?)

I've seen the following post, which specify that MySQL can use the left-most part(s) of a multiple-column index for efficiently querying rows. But I did not find anything for PostgreSQL on composite primary keys.

Answer 1

For best performance, you would need an additional index on (a, e).

If that is an option, you can drop the primary key and replace it with one on (a, e, b, c, d). The primary key index would then support your query, and it is just as good as far as uniqueness is concerned.

You can also experiment with a single-column index on (e). PostgreSQL can combine scans on several indexes. That won't be as fast as a multi-column index, but the resulting index would be smaller.