| From: | worky(dot)workerson(at)gmail(dot)com |
|---|---|
| To: | pgsql-performance(at)postgresql(dot)org |
| Subject: | Self-join query and index usage |
| Date: | 2006-07-14 18:56:53 |
| Message-ID: | 1152903413.210226.284600@75g2000cwc.googlegroups.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-performance |
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.
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Tom Lane | 2006-07-14 19:19:18 | Re: Self-join query and index usage |
| Previous Message | Tom Lane | 2006-07-14 16:25:13 | Re: Kill a session |