Re: Self-join query and index usage

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: worky(dot)workerson(at)gmail(dot)com
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Self-join query and index usage
Date: 2006-07-14 19:19:18
Message-ID: 4260.1152904758@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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

In response to

Browse pgsql-performance by date

  From Date Subject
Next Message Gabriele Turchi 2006-07-15 14:14:11 Big differences in plans between 8.0 and 8.1
Previous Message worky.workerson 2006-07-14 18:56:53 Self-join query and index usage