Do covering indexes in PostgreSQL help JOIN columns?

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?

Will the index be used as a covering index to help the JOIN in the above query?

It depends. Postgres has "index-only" scans as index access method, there are no "covering indexes" per se - up to Postgres 10.

Starting with Postgres 11 true covering indexes with INCLUDE columns are available. Blog entry by Michael Paquier introducing the feature:

• https://paquier.xyz/postgresql-2/postgres-11-covering-indexes/

Related answer with code example:

• Does a query with a primary key and foreign keys run faster than a query with just primary keys?

That said, the index CREATE INDEX ON table2 (t2c1, t1); makes perfect sense for the query you demonstrate. It can be used for an index-only scan if additional preconditions are met, or it can be used in a bitmap index scan or a plain index scan. Related:

• Index usage on a temporary table

• Is a composite index also good for queries on the first field?

JOIN conditions and WHERE conditions are almost completely equivalent in Postgres. They certainly can use indexes in the same way. You can rewrite your query:

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

With this equivalent:

SELECT t1.t1c1
FROM   table1 t1 CROSS JOIN table2 t2
WHERE  t2.t1 = t1.id
AND    t2.t2c1 = 42;

The first form is obviously preferable, though. Easier to read.

Why "almost" equivalent? (Makes no difference for the simple query at hand.)

• Why does this implicit join get planned differently than an explicit join?

Related:

• Are implicit joins as efficient as explicit joins in Postgres?

• What does [FROM x, y] mean in Postgres?

StackExchange Database Administrators Q#190132, answer score: 23