Re: How best to index foreign key for inner join

2009/11/27 Nathaniel Trellice <naptrel(at)yahoo(dot)co(dot)uk>

> Hello,
>
> I'd appreciate advice anyone can offer on this simple, newbie
> question. It really is a straightforward question, so please bare with
> me if I don't explain it succinctly below.
>
> I want to search a table based on criteria on both a column within
> that table and a column within a joined table, and I'm trying to
> figure out how to index things for my particular form of query. The
> postgres manual is excellent, but it doesn't appear to answer this
> question explicitly through a matching example, and I'm not very good
> with indexing, so I'm seeking advice here (yet again).
>
> E.g. Suppose I have 2 tables defined:
>
> CREATE TABLE table1 (
> id SERIAL PRIMARY KEY,
> name text
> );
>
> CREATE TABLE table2 (
> x real,
> y real,
> t timestampz,
> table1_id integer REFERENCES table1 ON DELETE CASCADE
> );
>
> My queries upon these tables are, almost exclusively, asking for all
> the records with a particular 'name' between a range of times 't', and
> so are of the form:
>
> SELECT table2.x, table2.y, table2.t
> FROM table2, table1
> WHERE
> table2.table1_id = table1.id -- inner join
> AND table1.name = 'some_name' -- criterion on table1
> AND table2.t BETWEEN some_t AND some_other_t; --criterion on table2
>
>
> What columns should I index in table2, and how: multi-column on
> (table1_id, t), say, or multiple single column indexes, or only one
> on 't'?
>
> I guess the query might benefit from an index on table1's 'name'
> column, but since there are, typically, only tens or hundreds of
> records in table1, it might not be worth it. There are millions of
> records in table2.
>
> Thanks,
>
> Nathaniel
>
>
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;

Regards

Thom