Skip site navigation (1) Skip section navigation (2)

Re: How best to index foreign key for inner join

From: Thom Brown <thombrown(at)gmail(dot)com>
To: Nathaniel Trellice <naptrel(at)yahoo(dot)co(dot)uk>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: How best to index foreign key for inner join
Date: 2009-11-27 11:12:09
Message-ID: bddc86150911270312t12dec151yadd656f9bc435bdc@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
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

In response to

pgsql-novice by date

Next:From: Jean-Yves F. BarbierDate: 2009-11-27 14:18:54
Subject: Re: bytea and text
Previous:From: Nathaniel TrelliceDate: 2009-11-27 11:04:21
Subject: How best to index foreign key for inner join

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group