From: | Mike Mascari <mascarm(at)mascari(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Avoiding sequential scans with OR join condition |
Date: | 2004-10-16 05:23:09 |
Message-ID: | 4170B03D.3060906@mascari.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hello. I have a query like:
SELECT big_table.*
FROM little_table, big_table
WHERE little_table.x = 10 AND
little_table.y IN (big_table.y1, big_table.y2);
I have indexes on both big_table.y1 and big_table.y2 and on
little_table.x and little_table.y. The result is a sequential scan of
big_table. In order to prevent this, I've rewritten the query as:
SELECT big_table.*
FROM little_table, big_table
WHERE little_table.x = 10 AND
little_table.y = big_table.y1
UNION
SELECT big_table.*
FROM little_table, big_table
WHERE little_table.x = 10 AND
little_table.y = big_table.y2
which does allow an index scan, but suffers from two separate queries
along with a unique sort, which, from the data, represents 90% of the
tuples returned by both queries.
Is there any way to write the first query such that indexes will be used?
Mike Mascari
From | Date | Subject | |
---|---|---|---|
Next Message | William Yu | 2004-10-16 05:57:43 | Re: creating audit tables |
Previous Message | Michael Glaesemann | 2004-10-16 04:13:08 | Re: Has anyone tried Date/Darwen/Lorentzos's model for temporal data? |