5

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?

  • 1
    One question per question, please. Move "Scenario 2" to a separate question. You can always link to the other to provide more context. – Erwin Brandstetter Nov 5 '17 at 14:13
  • I don't think it will ever use the index you've suggested in the fashion you've mentioned. – Evan Carroll Nov 5 '17 at 21:39
9

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:

Related answer with code example:

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:

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 t1c1
FROM   table1
JOIN   table2 ON table2.t1 = table1.id
WHERE  t2c1 = 42;

With this equivalent:

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

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

Why "almost" equivalent?

2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.