Re: or kills performance

My goal is a specific batchid, stock>0, ownerid=1 and either leadfree with a state of 1 or 3 OR not leadfree with a state of 2,3 or 4

The parenthesis are correct:
where a.batchid=382 and e.stock>0 and e.ownerid=1 and
(
(d.leadfree and leadstateid in (1,3) )
or
(not d.leadfree and leadstateid in (2,3,4) )
)
order by partid,leadstateid

I checked my indices and every field in both the where clause and the join is already indexed.

I tried unioning the 2 queries and it was much faster then with the OR statement. (Took 200 ms as opposed to 2000 ms). The union will work, but it seems like overkill for a simple or clause. Is this the recommended way to do it?

select c.partid,c.pnid,c.leadstateid,e.stock from
assemblies d join assembliesbatch a on d.assemblyid=a.assemblyid
join partsassembly b on b.assemblyid=d.assemblyid
join manufacturerpartpn c on c.partid=b.partid
join stock e on e.pnid=c.pnid
where a.batchid=382 and e.stock>0 and e.ownerid=1 and
not d.leadfree and leadstateid in (2,3,4)
union
select c.partid,c.pnid,c.leadstateid,e.stock from
assemblies d join assembliesbatch a on d.assemblyid=a.assemblyid
join partsassembly b on b.assemblyid=d.assemblyid
join manufacturerpartpn c on c.partid=b.partid
join stock e on e.pnid=c.pnid
where a.batchid=382 and e.stock>0 and e.ownerid=1 and
d.leadfree and leadstateid in (1,3)
order by partid,leadstateid

________________________________________________________________________________

"Sim Zacks" <sim(at)compulab(dot)co(dot)il> writes:
> Does it make sense for a simple or in a where clause to kill performance?

Did you get the parenthesization correct? Remember that AND binds more
tightly than OR.

regards, tom lane