2009/11/27 Nathaniel Trellice <naptrel(at)yahoo(dot)co(dot)uk>
> 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
> 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.
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
INNER JOIN table2 ON table1.id = table2.table1_id
AND table1.name = 'some_name'
WHERE table2.t BETWEEN some_t AND some_other_t;
In response to
pgsql-novice by date
|Next:||From: Jean-Yves F. Barbier||Date: 2009-11-27 14:18:54|
|Subject: Re: bytea and text|
|Previous:||From: Nathaniel Trellice||Date: 2009-11-27 11:04:21|
|Subject: How best to index foreign key for inner join|