Sim Zacks <sim@xxxxxxxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
