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