Re: How best to index foreign key for inner join

Since for each row of table1 you'll be looking for multiple records on table2, I imagine you'd want to index table2.table1_id, and table2.t since that's what you're filtering on.  If table1 is particularly big, you might benefit from also indexing table1.name.

I personally prefer to use INNER JOIN syntax as it's clearer, although the query planner will probably be identical as it's clever that way:

SELECT table2.x, table2.y, table2.t
FROM table1
INNER JOIN table2 ON table1.id = table2.table1_id
AND table1.name = 'some_name'
WHERE table2.t BETWEEN some_t AND some_other_t;