Hi All,
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? Do I build one on all three columns (id, time_from, time_to) or three separate indexes for each column or some other combo? It’s a small enough table where space isn’t a worry, but speed will be as “observations” is several terabytes in size.
This is PostgreSQL 10, so we can CREATE STATISTICS, but the id, is an md5 (its hashed data), so it is therefore completely orthogonal on its own.
Thanks,
Stephen
Stephen Froehlich
Sr. Strategist, CableLabs®
s.froehlich@cablelabs.com
Tel: +1 (303) 661-3708
