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;
Thanks for the help Thom.
Do you think it's possible to phrase this query in such a way that the planner would be able to exploit any benefits from a mutli-column index on table2 on either (table1_id, t) or (t, table1_t)?
If you imagine setting up a view that encapsulates the inner join, creating a 'virtual' table with columns:
name text
x real
y real
t timestampz
Then the manual (11.3) suggests that ANDed queries on 'name' and 't' will be improved by using a multi-column index (name, t).
But the join confuses me. When the rule system breaks down both the query and the view's join, will the benefits of the multi-column index still be realised?
Nathaniel