In article <bfc71945-8821-4bc9-8430-a8cacf8f3...@gmail.com>, Ty Busby <tybu...@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 &&); -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql