pg.ddx.io pgsql-performance@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Self-join query and index usage 2+ messages / 2 participants [nested] [flat]
* Self-join query and index usage @ 2006-07-14 18:56 worky.workerson@gmail.com 0 siblings, 1 reply; 2+ messages in thread From: worky.workerson@gmail.com @ 2006-07-14 18:56 UTC (permalink / raw) To: 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. ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Self-join query and index usage @ 2006-07-14 19:19 Tom Lane <tgl@sss.pgh.pa.us> parent: worky.workerson@gmail.com 0 siblings, 0 replies; 2+ messages in thread From: Tom Lane @ 2006-07-14 19:19 UTC (permalink / raw) To: worky.workerson@gmail.com; +Cc: pgsql-performance worky.workerson@gmail.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 ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2006-07-14 19:19 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2006-07-14 18:56 Self-join query and index usage worky.workerson@gmail.com 2006-07-14 19:19 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox