Stephen Froehlich <s.froehlich@cablelabs.com> writes:
> I am creating an intersect table where I have a relationship that is true for a period of time and then a series of
observations,so we're looking at something like:
> SELECT * FROM
> observations
> INNER JOIN
> relationships
> ON (observations.id = relationships.id AND observations.time >= relationships.time_from AND observations.time <
relationships.time_to)
> How do I best build indexes on "relationships", which is a few hundred
> thousand lines in length for a fast join?
You won't get terribly great results from standard btree indexes on that
sort of range test. If there are not too many relationships entries per
"id" then it might not matter, but if there are a lot then you need decent
index selectivity for the time aspect too. You might do better by
representing the time_from/time_to pair as a range and then using a GIST
index on the range, along the lines of
SELECT * FROM
observations
INNER JOIN
relationships
ON (observations.id = relationships.id AND
observations.time <@ tstzrange(relationships.time_from, relationships.time_to))
I think you'd need the btree_gist extension as well, so that the index
can be like
create index on relationships using gist (id, tstzrange(time_from,time_to));
You could do it just like this and leave the table storage alone, but it
might be better to materialize the range value as an actual column in
the table.
regards, tom lane