Re: Self-join query and index usage

worky(dot)workerson(at)gmail(dot)com writes:
> and the query that I would like to run is:

> SELECT e1.endnode, count(*), sum(e1.weight) AS weight1, sum(e2.weight)
> AS weight2
> FROM event e1, event e2
> WHERE e1.endnode = e2.startnode AND e1.starttime < e2.starttime AND
> e2.starttime < e1.endtime
> GROUP BY e1.endnode

> Assuming that I have indexes on all the columns, should this query be
> able to make use of the indexes on starttime and endtime?

This is just really poorly suited for btree indexes. What you're
looking for is an interval overlap test, which is something that can be
handled by rtree or gist indexes, but you'll need to change the form of
the query ...

regards, tom lane