From: | Martijn van Oosterhout <kleptog(at)svana(dot)org> |
---|---|
To: | Mike Mascari <mascarm(at)mascari(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Avoiding sequential scans with OR join condition |
Date: | 2004-10-16 14:19:21 |
Message-ID: | 20041016141918.GA6767@svana.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
If the problem is the sort, use UNION ALL.
As for the query restructuring, I don't know if there is a way of
restructuring the query to do it in a single query. You would be able
to contruct a query plan that would do it, something like:
-> Nested Loop
-> Append
-> Index Scan on big_table.y1
-> Index Scan on big_table.y2
-> Index Scan on little_table
But I have no idea how to get PostgreSQL to produce this...
On Sat, Oct 16, 2004 at 01:23:09AM -0400, Mike Mascari wrote:
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.
From | Date | Subject | |
---|---|---|---|
Next Message | Josh Close | 2004-10-16 14:30:32 | Re: plpgsql loop not returning value |
Previous Message | John DeSoi | 2004-10-16 14:08:45 | Re: OS X Install |