What should I index when joining on a table with conditions?

Question

I have a query like this:

SELECT "main_table".*
FROM "main_table"
INNER JOIN "other_table" ON "other_table"."deleted_at" IS NULL
AND "other_table"."id" = "main_table"."appointment_id"
WHERE "main_table"."user_id" = 1
  AND (other_table.date BETWEEN '2020-02-26 23:29:42.678693' AND '2020-02-27 01:29:42.678739')

You probably know everything you need to about the schema from the query, but let me know any questions.

What are the optimal indexes to make for this?

main_table is easy (right?)

CREATE INDEX ON main_table (user_id);

For other_table, I'm tempted to think "first postgres will 'do the join', then it will 'filter by date'". Which suggests this index:

CREATE INDEX ON other_table (id, date) where deleted_at IS NULL;

But, without creating the indexes and running EXPLAIN, is it unknowable how postgres will go about doing the query, and it might think that it's better to filter by date first, then 'do the join'?

• this select will typically match 1–10 rows
• for a given date range, there are thousands of rows in other_table
• for a given user_id there are thousands of rows in main_table
• the results of this query will never have two main_table rows correlating with one other_table row, or vice versa. It's always 1-1.
• other_table has about 2/3 of its rows with deleted_at not null

Answer 1

Given your information about the distribution of the data, the following execution plan will be the best:

• Use an index scan to get the 1 to 10 rows from other table.

• Perform a nested loop join with main_table as inner table, which will be fast if the join condition is indexed.

So the ideal index on other_table would be

CREATE INDEX ON other_table (date) WHERE deleted_at IS NULL;

If deleted_at is usually NULL, you can omit the WHERE clause without losing much.

If you want to get an index only scan on other_table (which probably isn't necessary, since you only fetch very few rows), you could instead

CREATE INDEX ON other_table (date) INCLUDING (id) WHERE deleted_at IS NULL;

---

The ideal index on main_table would be

CREATE INDEX ON main_table (user_id, appointment_id);

The order of the columns doesn't matter in this case, because you will scan for both columns with the = operator.

---

In the real world, you will try to pick indexes that can be useful for as many queries as possible, because having too many indexes hurts performance and wastes space.

Answer 2

there is a 1-1 relationship with main_table.

Do you mean that main_table.appointment_id and other_table.id are both primary keys of their respective tables? That is rarely a good design, and it would be easier to get efficient queries if the tables were merged to one table with more columns.

But, is it unknowable how postgres will go about doing the query, and it might think that it's better to filter by date first, then 'do the join'?

It is not unknowable, if you use EXPLAIN or EXPLAIN (ANALYZE).

If this is just testing, you could create a bunch of indexes and see which ones actually get used, and whether it seems like a good use of them, and keep the good ones in production.

Your existing indexes look pretty good for a nested loop from main_table to other_table. But it might like to instead pull out the thousands with the user_id, and the thousands with the right date, and then hash join them together. In which case, you would want

CREATE INDEX ON other_table (date,id) where deleted_at IS NULL;

Or it might want to do a nested loop with other_table driving, in which case you would want the above index plus:

CREATE INDEX ON main_table (appointment_id, user_id);

(although the other order of columns should also work)

If deleted_at is usually NULL, then specify "where deleted_at IS NULL;" might not be worthwhile, as it makes the index less flexible for other queries without making it that much faster (although if is enable index-only-scan, it might be quite a bit faster).