Should I create an index for non key columns?

I have a table in a PostGreSql database defined as following:

CREATE TABLE public."MATCH"(
    "ITEM_A_ID" bigint DEFAULT 0,
    "ITEM_B_ID" bigint DEFAULT 0,
    "OWNER_A_ID" bigint DEFAULT 0,
    "OWNER_B_ID" bigint DEFAULT 0,
    "OTHER_DATA" varchar(100) NOT NULL DEFAULT ''
    CONSTRAINT "MATCH_PK" PRIMARY KEY ("ITEM_A_ID","ITEM_B_ID")
);

It will contain a lot of rows. There will be a lot of queries like the following performed on this table:

SELECT * FROM "MATCH" WHERE "OWNER_A_ID" = owner_a_id;
SELECT * FROM "MATCH" WHERE "OWNER_B_ID" = owner_b_id;

I was thinking about creating indexes on OWNER_A_ID and OWNER_B_ID, since these columns are not keys. Is this a good idea, and if yes, how should I create these? Should I create one index with both columns? Should I create two indexes? Should I include other columns?

Please be more specific: "It will contain a lot of rows". How many? Millions, thousands or billions. "Is it a good idea?" It depends.

If your queries are like the ones you mention, you should create two b-tree indexes, one for each field. Instructions are here: http://www.postgresql.org/docs/9.1/static/sql-createindex.html

You should only create one index for both fields, ONLY IF all your queries are like:

SELECT * FROM "MATCH" WHERE "OWNER_A_ID" = owner_a_id AND "OWNER_B_ID"=owner_b_id;

This index will also work for queries like:

SELECT * FROM "MATCH" WHERE "OWNER_A_ID" = owner_a_id;

but NOT for

SELECT * FROM "MATCH" WHERE "OWNER_B_ID" = owner_b_id;

StackExchange Database Administrators Q#60480, answer score: 6