From: Harald Fuchs <hari.fuchs@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: optimize self-join query
Date: Fri, 28 Oct 2011 11:31:58 +0200
Message-ID: <86sjmdlawh.fsf@protecting.net> (raw)
References: <BFC71945-8821-4BC9-8430-A8CACF8F3794@gmail.com>
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 &&);
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: hari.fuchs@gmail.com
Subject: Re: optimize self-join query
In-Reply-To: <86sjmdlawh.fsf@protecting.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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