From: | Nathaniel Trellice <naptrel(at)yahoo(dot)co(dot)uk> |
---|---|
To: | Thom Brown <thombrown(at)gmail(dot)com> |
Cc: | "pgsql-novice(at)postgresql(dot)org" <pgsql-novice(at)postgresql(dot)org> |
Subject: | Re: How best to index foreign key for inner join |
Date: | 2009-11-27 14:44:53 |
Message-ID: | 998892.58771.qm@web25004.mail.ukl.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Jean-Yves F. Barbier | 2009-11-27 15:04:44 | Re: index speed-up and automatic tables/procedures creation |
Previous Message | Jean-Yves F. Barbier | 2009-11-27 14:21:03 | Re: index speed-up and automatic tables/procedures creation |