Re: [GENERAL] Avoiding sequential scans with OR join condition

[Mike Mascari]

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
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings