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