X-Original-To: pgsql-performance-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id C7E989FAD5A for ; Fri, 14 Jul 2006 16:19:29 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 40362-09 for ; Fri, 14 Jul 2006 16:19:21 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id A6C879FAC76 for ; Fri, 14 Jul 2006 16:19:20 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id k6EJJIvV004261; Fri, 14 Jul 2006 15:19:18 -0400 (EDT) To: worky.workerson@gmail.com cc: pgsql-performance@postgresql.org Subject: Re: Self-join query and index usage In-reply-to: <1152903413.210226.284600@75g2000cwc.googlegroups.com> References: <1152903413.210226.284600@75g2000cwc.googlegroups.com> Comments: In-reply-to worky.workerson@gmail.com message dated "14 Jul 2006 11:56:53 -0700" Date: Fri, 14 Jul 2006 15:19:18 -0400 Message-ID: <4260.1152904758@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200607/146 X-Sequence-Number: 20006 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