Re: How best to index foreign key for inner join

On 27 Nov 2009, at 11:12, Thom Brown <thombrown(at)gmail(dot)com> wrote:

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