Re: left join with OR optimization

Sim Zacks <sim@compulab.co.il> writes:
> I've seen written that a b-tree index can't be used on a join with an
> OR.

That's not the case ...

> Is there a way to optimize a join so that it can use an index for a
> query such as:

> select
> a.partid,a.duedate,coalesce(a.quantity,0)+sum(coalesce(b.quantity,0))
> from stat_allocated_components a
> left join stat_allocated_components b on a.partid=b.partid and
> b.quantity>0 and
> (a.duedate>b.duedate or (a.duedate=b.duedate and a.popartid>b.popartid))
> where a.quantity>0
> group by a.partid,a.duedate,a.quantity

... but in this example, it would be both more readable and more easily
optimizable if you expressed the duedate/popartid requirement as a row
comparison:

    row(a.duedate, a.popartid) > row(b.duedate, b.popartid)

            regards, tom lane