Re: optimize self-join query - Mailing list pgsql-sql

From Harald Fuchs
Subject Re: optimize self-join query
Date
Msg-id 86sjmdlawh.fsf@protecting.net
Whole thread Raw
In response to optimize self-join query  (Ty Busby <tybusby@gmail.com>)
List pgsql-sql
In article <BFC71945-8821-4BC9-8430-A8CACF8F3794@gmail.com>,
Ty Busby <tybusby@gmail.com> writes:

> I have a table that stores a very large starting number called
> epc_start_numeric and a quantity.  I've apparently built the most
> inefficient query possible for doing the job I need: find out if any
> records overlap.  Imagine the epc_start_numeric + quantity
> representing a block of numbers.  I need to find out if any of these
> blocks overlap.

If I understand you correctly, you want to compare numeric intervals.
On PgFoundry you can find an interval type like that called bioseg.
This type is GiST-indexable and thus may speed up your query.

Example:
 CREATE TABLE test2 (   id serial NOT NULL,   seg bioseg NOT NULL,   PRIMARY KEY (id) );
 -- Fill test2 with a gazillion of rows
 CREATE INDEX test2_seg_ix ON test2 USING gist (seg);
 SELECT t1.id, t1.seg, t2.id, t2.seg FROM test2 t1 JOIN test2 t2 ON t2.id != t1.id AND t2.seg && t1.seg;

You'll still need a seqscan for t1, but t2 will use an index scan.

You can even define a table constraint to prevent overlaps:
 ALTER TABLE test2 ADD CONSTRAINT test2_seg_ex EXCLUDE USING gist (seg WITH &&);



pgsql-sql by date:

Previous
From: Pavel Stehule
Date:
Subject: Re: Different order by behaviour depending on where clause?
Next
From: Phil Couling
Date:
Subject: Re: Different order by behaviour depending on where clause?