Will the index be used as a covering index to help the JOIN in the above query? Should I change my index writing strategy to cover foreign key columns?

Not likely in the above query. This is a deceiving complex problem with the results based on the estimates and selectivity of the two conditions,

• table2.t1 = table1.id
• t2c1 = 42

Essentially, you want to throw both of the environments (row counts) to make it so both conditions have more or less selectivity. And if you get a nested-loop, you want to increase the raw amount until that's no longer the most viable join method.

CREATE TABLE table1(
   id INTEGER PRIMARY KEY,
   t1c1 INTEGER,
   t1c2 INTEGER
);
INSERT INTO table1(id, t1c1, t1c2)
  SELECT x,x,x FROM generate_series(1,1000)
  AS gs(x);

CREATE TABLE table2(
  id INTEGER PRIMARY KEY,
  t1 INTEGER REFERENCES table1(id),
  t2c1 INTEGER
);
INSERT INTO table2(id, t1, t2c1)
SELECT x,1+x%1000,x%50 FROM generate_series(1,1e6)
  AS gs(x);

EXPLAIN ANALYZE
  SELECT t1c1
  FROM table1
  JOIN table2 ON table2.t1 = table1.id
  WHERE t2c1 = 42;

Now check the plan.

Now create the compound index,

CREATE INDEX ON table2 (t1, t2c1);
VACUUM FULL ANALYZE table1;
VACUUM FULL ANALYZE table2;

And check the plan again,

EXPLAIN ANALYZE
  SELECT t1c1
  FROM table1
  JOIN table2 ON table2.t1 = table1.id
  WHERE t2c1 = 42;

You can drop the keys and such to find which form it prefers

CREATE INDEX ON table2 (t1, t2c1);

or

CREATE INDEX ON table2 (t2c1, t1);

Ultimately though this is a lot of work, I suggest starting off with

CREATE INDEX ON table2 (t1);
CREATE INDEX ON table2 (t2c1);

And optimizing only if that isn't sufficient.

You can also disable specific planner options to see if another plan really is faster or slower, and then look to fixing that but that can also be a lot of work.

Will the index be used as a covering index to help the JOIN in the above query? Should I change my index writing strategy to cover foreign key columns?

Not likely in the above query. This is a deceiving complex problem with the results based on the estimates and selectivity of the two conditions,

• table2.t1 = table1.id
• t2c1 = 42

Essentially, you want to throw both of the environments (row counts) to make it so both conditions have more or less selectivity. And if you get a nested-loop, you want to increase the raw amount until that's no longer the most viable join method.

CREATE TABLE table1(
   id INTEGER PRIMARY KEY,
   t1c1 INTEGER,
   t1c2 INTEGER
);
INSERT INTO table1(id, t1c1, t1c2)
  SELECT x,x,x FROM generate_series(1,1000)
  AS gs(x);

CREATE TABLE table2(
  id INTEGER PRIMARY KEY,
  t1 INTEGER REFERENCES table1(id),
  t2c1 INTEGER
);
INSERT INTO table2(id, t1, t2c1)
SELECT x,1+x%1000,x%50 FROM generate_series(1,1e6)
  AS gs(x);

EXPLAIN ANALYZE
  SELECT t1c1
  FROM table1
  JOIN table2 ON table2.t1 = table1.id
  WHERE t2c1 = 42;

Now check the plan.

Now create the compound index,

CREATE INDEX ON table2 (t2c1, t1);
VACUUM FULL ANALYZE table1;
VACUUM FULL ANALYZE table2;

And check the plan again,

EXPLAIN ANALYZE
  SELECT t1c1
  FROM table1
  JOIN table2 ON table2.t1 = table1.id
  WHERE t2c1 = 42;

You can drop the keys and such to find which form it prefers

CREATE INDEX ON table2 (t1, t2c1);

or

CREATE INDEX ON table2 (t2c1, t1);

Ultimately though this is a lot of work, I suggest starting off with

CREATE INDEX ON table2 (t1);
CREATE INDEX ON table2 (t2c1);

And optimizing only if that isn't sufficient.

You can also disable specific planner options to see if another plan really is faster or slower, and then look to fixing that but that can also be a lot of work.