What should I index when joining on a table with conditions?
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.