Re: Avoiding sequential scans with OR join condition

Mike,

You are probably correct, I was thinking in English, not SQL. That's
what happens when I bang code too early in the morning.

Thank You
Sim Zacks
IT Manager
CompuLab
04-829-0145 - Office
04-832-5251 - Fax

________________________________________________________________________________

Mike Mascari wrote:
> Sim Zacks wrote:
>
>> I would use 2 left joins and use the where condition to make sure one
>> of them is true, such as:
>>
>> select big_table.* from
>> big_table left join little_table as l1 on big_table.y1=l1.y and
>> l1.x=10
>> left join little_table as l2 on big_table.y2=l2.y and l1.x=10
>> where l1.p_key is not null and l2.p_key is not null
>>
>> I have never tried this in postgresql, but in my experience with
>> various other DB engines it is a lot faster then using an or in the
>> join and faster then a union.
>
> Wow! Thanks! That certainly did the trick.

I'm thinking that the WHERE clauses condition should read:

WHERE l1.p_pkey is not null OR l2.p_key is not null;

My condition for a given selection of a big_table tuple is that either
y1 or y2 exist as a valid x from little_table. So I think I need an OR
instead of an AND. And AND condition would require that both y1 and y2
for the sample tuple of big_table be a valid x from little_table. Correct?

Mike Mascari