left join with OR optimization
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.
What version of PostgreSQL?
On Jan 24, 2012, at 9:28, Sim Zacks <sim@compulab.co.il> wrote:
Show quoted text
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.quantityWhere 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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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