From: worky.workerson@gmail.com
Subject: Self-join query and index usage
Date: 14 Jul 2006 11:56:53 -0700
Organization: http://groups.google.com
Lines: 31
Message-ID: <1152903413.210226.284600@75g2000cwc.googlegroups.com>
Mime-Version: 1.0
Content-Type: text/plain; charset="iso-8859-1"
User-Agent: G2/0.2
Complaints-To: groups-abuse@google.com
Injection-Info: 75g2000cwc.googlegroups.com; posting-host=63.239.69.1;
	posting-account=EhkmOg0AAAAu8-rAx4_fA4J_FdZnfg8I
To: pgsql-performance@postgresql.org

I'm doing a self join of some shipping data and wanted to get the best
query possible.  The interesting table is the event table, and it has
the following structure:

  startnode int,
  endnode int,
  weight int,
  starttime timestamp,
  endtime timestamp

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?

The "best" plan that I could see is a merge join between a sorted
sequential scan on e2.startnode and an index scan on e1.endnode, which
I figure takes care of the "e1.endnode = e2.startnode".  The join
filter is then "e1.starttime < e2.starttime AND e2.starttime <
e1.endtime" ... does this use an index?  Can the planner to use a
bitmap index scan to use the indexes on the start/endtimes in the join?

Table is about 3GB.