I have a whole lot of tables that look vaguely like this:
CREATE TABLE table1(id INTEGER PRIMARY KEY, t1c1 INTEGER, t1c2 INTEGER); CREATE TABLE table2(id INTEGER PRIMARY KEY, t1 INTEGER REFERENCES table1(id), t2c1 INTEGER);
And I do a whole lot of joins where I'm trying to filter on the joined-in table to get stuff from the first table, like this:
SELECT t1c1 FROM table1 JOIN table2 ON table2.t1 = table1.id WHERE t2c1 = 42;
When I go to write indexes for a table I'd look at the columns that get used in the WHERE clause and build out indexes to satisfy them. So for this query I'd wind up writing an index like this:
CREATE INDEX ON table2 (t2c1);
And this index is at least eligible for use in that query.
My question is that if I write an index like this:
CREATE INDEX ON table2 (t2c1, t1);
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?