Postgresql – Are indexes need for parts of primary key

indexpostgresqlprimary-key

In Postgres, is it beneficial to query performance to create an index on field2 in the following table if I intend to sometimes use queries with only field2 in the WHERE clause?

CREATE TABLE my_table (
    field1   INT NOT NULL,
    field2   INT NOT NULL,

    CONSTRAINT pk_my_table PRIMARY KEY (field1, field2)
);

example query ("get all the records where field2 = my_value"):

SELECT * FROM my_table WHERE field2 = my_value;

In other words should I also include:

CREATE INDEX ix_my_table_field2 ON my_table(field2);

Best Answer

A compound index can also support queries that compare columns that are at the beginning of the indexed values (but aren't all). So an index on (field1, field2) (which is implicitly created for a primary key) would also support queries that compare only field1.

But it cannot support queries that only compare columns of its end (but aren't all). So (field1, field2) won't support queries that only compare field2.

So yes, you need to create an extra index only on (field2) to have a chance it supports queries only comparing field2.

Related Question