To: pgsql-sql@postgresql.org
From: Harald Fuchs <hari.fuchs@gmail.com>
Subject: Re: optimize self-join query
Date: Fri, 28 Oct 2011 11:31:58 +0200
Lines: 37
Message-ID: <86sjmdlawh.fsf@protecting.net>
References: <BFC71945-8821-4BC9-8430-A8CACF8F3794@gmail.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/23.1 (gnu/linux)
Cancel-Lock: sha1:675Mk4YIht8utLaqJ8iPely2HDA=

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 &&);