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);

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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