How best to index foreign key for inner join
От | Nathaniel Trellice |
---|---|
Тема | How best to index foreign key for inner join |
Дата | |
Msg-id | 205907.58847.qm@web25004.mail.ukl.yahoo.com обсуждение исходный текст |
Ответы |
Re: How best to index foreign key for inner join
|
Список | pgsql-novice |
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
В списке pgsql-novice по дате отправления: