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

Re: How best to index foreign key for inner join

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 (view raw or flat)
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


      

Responses

pgsql-novice by date

Next:From: Jean-Yves F. BarbierDate: 2009-11-27 15:04:44
Subject: Re: index speed-up and automatic tables/procedures creation
Previous:From: Jean-Yves F. BarbierDate: 2009-11-27 14:21:03
Subject: Re: index speed-up and automatic tables/procedures creation

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