Re: left join with OR optimization

What version of PostgreSQL?

On Jan 24, 2012, at 9:28, Sim Zacks <sim(at)compulab(dot)co(dot)il> wrote:

> I've seen written that a b-tree index can't be used on a join with an
> OR. 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
>
> Where I am doing a self join to get a running sum, but some rows have
> the same due date so I am saying if the due date is the same then the
> first one entered should be considered earlier.
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general