Query on columns (x, z) for a multi-column index (x, y, z)

Question

I have a table that looks like:

CREATE TABLE t (
  x some_type_1,
  y some_type_2,
  z some_type_3,
  PRIMARY KEY (x, y, z)
);

Now I want to execute this query efficiently:

SELECT * FROM t WHERE x = ? AND z = ?;

Is the (primary-key) index (x, y, z) enough in this case, or should I create a separate index (x, z)?

Answer 1

That existing index on primary key should be enough in this case since with PRIMARY KEY (x, y, z) what you essentially have is a Covering Index. See What is a Covered Index?

Answer 2

The primary key index is sufficient for the query. However, it is not optimal.

In order for Postgres to use the index, it needs to scan all the entries in the index where x = ? in order to find the matching values for z. This might be good enough performance.

But, the optimal index for the query would be (x, z, y), because this covers the query and satisfies the where clause efficiently. The next best index is (x, z), because it covers the where clause. The existing primary key index will be used partially for the where clause (assuming statistics on the table say that an index should be used).