| From: | Sim Zacks <sim(at)compulab(dot)co(dot)il> |
|---|---|
| To: | PostgreSQL general <pgsql-general(at)postgresql(dot)org> |
| Subject: | left join with OR optimization |
| Date: | 2012-01-24 14:28:54 |
| Message-ID: | 4F1EC026.9080004@compulab.co.il |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
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.
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Andreas Kretschmer | 2012-01-24 14:35:33 | Re: I cant create excluding constaint |
| Previous Message | pasman pasmański | 2012-01-24 11:41:28 | I cant create excluding constaint |